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

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