Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Data Pump (impdp) enhancement wanted

P.HuangMar 26 2024

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 ~]$
Comments
Post Details
Added on Mar 26 2024
2 comments
110 views