When use default sequence on a column, when perform impdp with remap schema, the remap didn't happen for the sequence. This is a problem when destination doesn't have the sequence per-defined. In the following example, I am trying to expdp schema POJEN123 and remap to schema POJEN456. (I drop POJEN123 before impdp to simulate I impdp to a different database.)
The error want to address is: ORA-02289: sequence does not exist
(note, using identity column won't have this problem, but I don't have a easy way to convert millions of tables (across multiple databases) use trigger based PK to identity column.)
SQL> connect c##pojen/oracle_4U@freepdb1
Connected.
SQL> drop user pojen456 cascade;
User dropped.
SQL> grant dba to pojen123 identified by oracle_4U;
Grant succeeded.
SQL> connect pojen123/oracle_4U@freepdb1
Connected.
SQL> create sequence seq_po;
Sequence created.
SQL> create table pojen(eid number default seq_po.nextval,ename varchar2(8));
Table created.
SQL> connect c##pojen/oracle_4U@freepdb1
Connected.
SQL> drop directory tmp_dir ;
Directory dropped.
SQL> create directory tmp_dir as '/tmp';
Directory created.
SQL> grant read,write on directory tmp_dir to pojen123;
Grant succeeded.
SQL> !
[oracle@oel2 ~]$ expdp pojen123/oracle_4U@freepdb1 dumpfile=pojen.dmp directory= tmp_dir
Export: Release 23.0.0.0.0 - Production on Wed Mar 20 01:19:37 2024
Version 23.3.0.23.09
Copyright (c) 1982, 2023, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Starting "POJEN123"."SYS_EXPORT_SCHEMA_01": pojen123/********@freepdb1 dumpfile =pojen.dmp directory=tmp_dir
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
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/LOGREP
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
. . exported "POJEN123"."POJEN" 0 KB 0 rows
Master table "POJEN123"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for POJEN123.SYS_EXPORT_SCHEMA_01 is:
/tmp/pojen.dmp
Job "POJEN123"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Mar 20 01:20 :07 2024 elapsed 0 00:00:28
[oracle@oel2 ~]$ exit
exit
SQL> connect c##pojen/oracle_4U@freepdb1
Connected.
SQL> drop user pojen123 cascade;
User dropped.
SQL> grant dba to pojen456 identified by oracle_4U;
Grant succeeded.
SQL> SQL> impdp pojen456/oracle_4U@freepdb1 dumpfile=pojen.dmp directory=tmp_dir remap_schema=POJEN123:POJEN456
SP2-0734: unknown command beginning "impdp poje..." - rest of line ignored.
Help: https://docs.oracle.com/error-help/db/sp2-0734/
SQL> !
[oracle@oel2 ~]$ impdp pojen456/oracle_4U@freepdb1 dumpfile=pojen.dmp directory= tmp_dir remap_schema=POJEN123:POJEN456
Import: Release 23.0.0.0.0 - Production on Wed Mar 20 01:20:42 2024
Version 23.3.0.23.09
Copyright (c) 1982, 2023, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Master table "POJEN456"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "POJEN456"."SYS_IMPORT_FULL_01": pojen456/********@freepdb1 dumpfile=p ojen.dmp directory=tmp_dir remap_schema=POJEN123:POJEN456
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"POJEN456" 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/LOGREP
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"POJEN456"."POJEN" failed to create with error:
ORA-02289: sequence does not exist
Failing sql is:
CREATE TABLE "POJEN456"."POJEN" ("EID" NUMBER DEFAULT "POJEN123"."SEQ_PO"."NEXTV AL", "ENAME" VARCHAR2(8 BYTE)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NO COMPRESS LOGGING TABLESPACE "USERS"
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "POJEN456"."SYS_IMPORT_FULL_01" completed with 2 error(s) at Wed Mar 20 01:2 0:45 2024 elapsed 0 00:00:02
[oracle@oel2 ~]$