Impdp Fails with ORA-39083 if there is a Table with an Identity Column
Last updated on OCTOBER 11, 2016
Applies to:
Oracle Database - Enterprise Edition - Version 12.1.0.1 to 12.1.0.2 [Release 12.1]Information in this document applies to any platform.
Symptoms
Impdp fails with ORA-39083 if there is a table which has an identity column, e.g.:
Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN
ORA-39083: Object type IDENTITY_COLUMN:"TEST" failed to create
with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "TEST"."T_SEQ" MODIFY ("ID" GENERATED ALWAYS AS
IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999
INCREMENT BY 1 START WITH LIMIT VALUE CACHE 20 NOORDER NOCYCLE)
ORA-39083: Object type IDENTITY_COLUMN:"TEST" failed to create
with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "TEST"."T_SEQ" MODIFY ("ID" GENERATED ALWAYS AS
IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999
INCREMENT BY 1 START WITH LIMIT VALUE CACHE 20 NOORDER NOCYCLE)
NOTE:
The situation occurs under next circumstances:
- User test has following 2 tables of which table t_seq has an identity column:
- t_test(id number, col number)
- t_seq(id number generated always as identity, col number)
These 2 tables are independent and have no reference to each other.
- Export table t_test ONLY.
- Drop test user.
- Import the table using the generated expdp.dmp.
- The ORA-39083 will be raised even though table t_seq is not included in this expdp.dmp.
TESTCASE
(On Windows using a 12c non-CDB database)
Cause
Sign In with your My Oracle Support account |
|
Don't have a My Oracle Support account? Click to get started |
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms