Ao realizar import me deparei com o erro:
Erro
ORA-39083: Object type TYPE failed to create with error:
ORA-02304: invalid object identifier literal
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
Connected to: Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/********@DB directory=BKPDIR transform=disable_archive_logging:Y dumpfile=DB_FULL_09042020.DMP logfile=IMP_MIGRACAO_DB_09042020.log schemas=DB EXCLUDE=STATISTICS Processing object type DATABASE_EXPORT/SCHEMA/USER ORA-31684: Object type USER:"SANKHYA" already exists Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC ORA-39083: Object type TYPE:"DB"."T_EXTRATO" failed to create with error: ORA-02304: invalid object identifier literal Failing sql is: CREATE TYPE "DB"."T_EXTRATO" OID '117BA7FA64CE4D8D802965580CAA506B' AS OBJECT ( DTMOV DATE, COTACAO FLOAT, VLRANT FLOAT, VLRAPLICADO FLOAT ) |
Causa:
Retorna o erro pois o Oracle não permite importar dois objetos com o mesmo OID. Cada objeto deve ter OID único no banco de dados
TRANSFORM=OID:N parameter on the import. The transform OID:N causes a new OID to be created for the new object, allowing the import to succeed.
Checando se existe OID “117BA7FA64CE4D8D802965580CAA506B″ exists or
|
1 2 3 4 5 |
SQL> select owner,TYPE_NAME,TYPE_OID from dba_types where TYPE_OID='<strong>117BA7FA64CE4D8D802965580CAA506B</strong>'; OWNER TYPE_NAME TYPE_OID ------------------------------ ------------------------------ -------------------------------- PRODUCT T_EXTRATO BE4C0AFD318A07CBE0443C4A923B4794 |
Solução:
Adionar no impdp TRANSFORM=oid:n
system/********@DB directory=BKPDIR TRANSFORM=oid:n dumpfile=bd.dmp logfile=imp.db.log schemas=schema exclude=statistics
|
1 2 3 4 5 6 7 8 |
Import: Release 12.2.0.1.0 - Production on Thu Apr 9 13:14:31 2020 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. ;;; Connected to: Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/********@DB directory=BKPDIR TRANSFORM=oid:n dumpfile=DB_FULL_09042020.DMP logfile=IMP_MIGRACAO_DB_09042020_3.log schemas=SCHEMA EXCLUDE=STATISTICS Processing object type DATABASE_EXPORT/SCHEMA/USER |