Impdp Fails with ORA-39083 if there is a Table with an Identity Column
(Doc ID 2116552.1)
Last updated on MAY 17, 2021
Applies to:
Oracle Database - Enterprise Edition - Version 12.1.0.1 to 12.1.0.2 [Release 12.1]Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
NOTE: In the images and/or the document content below, the user information and data used in the testcase represents fictitious data. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.
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:"<COLUMN_NAME>" failed to create
with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "<SCHEMA_NAME>"."<TABLE_NAME>" MODIFY ("<COLUMN_NAME>" 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:"<COLUMN_NAME>" failed to create
with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "<SCHEMA_NAME>"."<TABLE_NAME>" MODIFY ("<COLUMN_NAME>" 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
Changes
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Changes |
| Cause |
| Solution |
| References |