合 ORA-31684 Object type SEQUENCE already exists序列已存在不能忽略
👉 本文共约260个字，系统预计阅读时间或需1分钟。
现象
若是导入已存在的表，那么可以添加参数“table_exists_action=REPLACE”
但是，若导入已经存在的序列，那么就应该删除已经存在的序列或删除整个用户，然后重新导入，否则可能会导致业务报错。例如，表上有主键的情况，而主键是通过序列来插入的。
示例
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 | [oracle@rac1 data]$ sqlplus lhr/lhr SQL*Plus: Release 11.2.0.4.0 Production on Mon Aug 8 09:26:47 2022 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> create sequence s3 nocache; Sequence created. SQL> select s3.nextval from dual; NEXTVAL ---------- 1 SQL> select s3.nextval from dual; NEXTVAL ---------- 2 SQL> select s3.nextval from dual; NEXTVAL ---------- 3 SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options [oracle@rac1 data]$ [oracle@rac1 data]$ expdp lhr/lhr schemas=lhr directory=D1 dumpfile=lhr.dmp Export: Release 11.2.0.4.0 - Production on Mon Aug 8 09:27:43 2022 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Starting "LHR"."SYS_EXPORT_SCHEMA_01": lhr/lhr schemas=lhr directory=D1 dumpfile=lhr.dmp Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 64 KB Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS . . exported "LHR"."TEST" 5.007 KB 1 rows Master table "LHR"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for LHR.SYS_EXPORT_SCHEMA_01 is: /data/lhr.dmp Job "LHR"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Aug 8 09:27:46 2022 elapsed 0 00:00:02 [oracle@rac1 data]$ sqlplus lhr/lhr SQL*Plus: Release 11.2.0.4.0 Production on Mon Aug 8 09:27:55 2022 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> select s3.nextval from dual; NEXTVAL ---------- 4 ................. SQL> select s3.nextval from dual; NEXTVAL ---------- 10 SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options [oracle@rac1 data]$ impdp lhr/lhr schemas=lhr directory=D1 dumpfile=lhr.dmp table_exists_action=REPLACE Import: Release 11.2.0.4.0 - Production on Mon Aug 8 09:28:26 2022 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Master table "LHR"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded Starting "LHR"."SYS_IMPORT_SCHEMA_01": lhr/lhr schemas=lhr directory=D1 dumpfile=lhr.dmp table_exists_action=REPLACE Processing object type SCHEMA_EXPORT/USER ORA-31684: Object type USER:"LHR" already exists Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE ORA-31684: Object type SEQUENCE:"LHR"."S3" already exists Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA . . imported "LHR"."TEST" 5.007 KB 1 rows Job "LHR"."SYS_IMPORT_SCHEMA_01" completed with 2 error(s) at Mon Aug 8 09:28:27 2022 elapsed 0 00:00:01 [oracle@rac1 data]$ impdp lhr/lhr schemas=lhr directory=D1 dumpfile=lhr.dmp sqlfile=ddl.sql Import: Release 11.2.0.4.0 - Production on Mon Aug 8 09:33:35 2022 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Master table "LHR"."SYS_SQL_FILE_SCHEMA_01" successfully loaded/unloaded Starting "LHR"."SYS_SQL_FILE_SCHEMA_01": lhr/lhr schemas=lhr directory=D1 dumpfile=lhr.dmp sqlfile=ddl.sql Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE Processing object type SCHEMA_EXPORT/TABLE/TABLE Job "LHR"."SYS_SQL_FILE_SCHEMA_01" successfully completed at Mon Aug 8 09:33:36 2022 elapsed 0 00:00:01 [oracle@rac1 data]$ more ddl.sql -- CONNECT LHR ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 '; -- new object type path: SCHEMA_EXPORT/USER -- CONNECT SYSTEM CREATE USER "LHR" IDENTIFIED BY VALUES 'S:F7DB81EA15B377E5C0C355683322E7050FC8476672738EB4736044764687;157AE4BCFD41976D' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP"; -- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT GRANT UNLIMITED TABLESPACE TO "LHR"; -- new object type path: SCHEMA_EXPORT/ROLE_GRANT GRANT "DBA" TO "LHR"; -- new object type path: SCHEMA_EXPORT/DEFAULT_ROLE ALTER USER "LHR" DEFAULT ROLE ALL; -- new object type path: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA -- CONNECT LHR BEGIN sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'CWORCL', inst_scn=>'3059403'); COMMIT; END; / -- new object type path: SCHEMA_EXPORT/SEQUENCE/SEQUENCE CREATE SEQUENCE "LHR"."S3" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 4 NOCACHE NOORDER NOCYCLE ; -- new object type path: SCHEMA_EXPORT/TABLE/TABLE CREATE TABLE "LHR"."TEST" ( "DUMMY" VARCHAR2(1 BYTE) ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ; [oracle@rac1 data]$ sqlplus lhr/lhr SQL*Plus: Release 11.2.0.4.0 Production on Mon Aug 8 09:28:45 2022 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> select s3.nextval from dual; NEXTVAL ---------- 11 SQL> |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
