Thanks for the question, Kishan.
Asked: March 07, 2017 - 10:58 am UTC
Last updated: March 07, 2017 - 2:56 pm UTC
Version: 12C
Viewed 10K+ times! This question is
You Asked
Hey TOM,
Consider the following test scenario :
1. Create a sequence
Connected to:
Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production
SQL> create sequence test_seq start with 100 increment by 10;
2. Use the sequence as default value in a table
create table test_tbl
(
col1 number default test_seq.nextval,
col2 varchar2(30)
);
All is good till here, the question comes when I do an export and import of the schema.
3. Export the schema
expdp system/manager dumpfile=test.dmp logfile=exp_test.log schemas=test_km
4. Import the schema
impdp system/manager dumpfile=test.dmp logfile=imp_test.log remap_schema=test_km:test
Now if I am importing in the same SID, I see that the table that get created(through import) has the DDL as follows :
CREATE TABLE TEST.TEST_TBL
(
COL1 NUMBER DEFAULT "TEST_KM"."TEST_SEQ"."NEXTVAL",
COL2 VARCHAR2(30 CHAR)
);
Why TEST_KM.TEST_SEQ ??
it should be TEST.TEST_SEQ, as you have noticed in the import I remapped the schema from test_km to test.
If I perform the import on a different sid, the table creation will fail with error
ORA-39083: Object type TABLE:"TEST"."TEST_TBL" failed to create with error:
ORA-02289: sequence does not exist
Failing sql is:
CREATE TABLE "TEST"."TEST_TBL"(...)
Can you please let me know how can we perform an import of the tables having default sequence_name.nextval, and have the tables default sequences pointing to the sequence created in that very schema.
I am exporting and importing in two different sid's and getting the above ORA- error.
Thanks in advance.
Kishan
and Chris said...
This is a known issue unfortunately, as discussed in MOS note 2060135.1. It offers the following workarounds:
1. excluding the affected objects from the import
2. using the sqlfile option to import the affected objects
3. amending the sqlfile script output to point to the correct objects before running it
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment