Search code examples
oracle-databasesequenceoracle12cdefault-value

Oracle 12c, export table with sequence as default value, schema is attached


I'm use oracle 12c with a user u1. With user u1 I create table T1 like this,

create SEQUENCE  T1_SEQ START WITH  1;
CREATE  table T1(
  id number(11) DEFAULT T1_SEQ.nextval PRIMARY KEY ,
  name varchar2(255)
);

Now I want to export the schema to others, and use below command to export

expdp u1/password dumpfile=u1.dmp  schemas=u1

, but when others use u1.dmp to import the schema with a new user named u2,

impdp u2/password   remap_schema=u1:u2  DUMPFILE=u1.DMP TABLE_EXISTS_ACTION=REPLACE

error happend, beacause default value of table T1 column id add U1 as prefix.

U1.T1_SEQ.nextval

and here is the new create table statement

CREATE  table T1(
      id number(11) DEFAULT U1.T1_SEQ.nextval PRIMARY KEY ,
      name varchar2(255)
    );

How can I remove the U1's influence while I want to move one schema to another schema. Could you give me some suggestions? Thanks in advance!.


Solution

  • this is a known and ongoing issue according to Ask Tom.

    So You can,

    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

    Please See relevant topic