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 18.104.22.168 to 22.214.171.124 [Release 12.1]
Information in this document applies to any platform.
Impdp fails with ORA-39083 if there is a table which has an identity column, e.g.:
ORA-39083: Object type IDENTITY_COLUMN:"TEST" failed to create
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)
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.
(On Windows using a 12c non-CDB database)
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