July 14, 2017

ORA-39001: ORA-00980: Errors when taking expdp


ORA-39001: invalid argument value
ORA-00980: synonym translation is no longer valid
Errors when taking expdp. 

Refered: https://shrikantrao.wordpress.com/2012/11/23/ora-39001-ora-01775-looping-chain-of-synonyms-2/

expdp system/password@tnsname directory=directory_nm dumpfile=test.dmp logfile=test.log schemas=TESTUSER

Export: Release 12.1.0.2.0 - Production on Fri Jul 14 06:59:12 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORA-39001: invalid argument value
ORA-00980: synonym translation is no longer valid

Check for the synonym for expdp master tables.

col owner for a30
col OBJECT_NAME for a30
col OBJECT_TYPE for a30
col STATUS for a20
select owner, object_name, object_type, status from dba_objects where object_name like '%SYS_EXPORT%';

OWNER                          OBJECT_NAME                    OBJECT_TYPE                    STATUS
------------------------------ ------------------------------ ------------------------------ --------------------
SYS                            SYS_EXPORT_SCHEMA_04           TABLE                          VALID
SYS                            SYS_EXPORT_SCHEMA_05           TABLE                          VALID
PUBLIC                         SYS_EXPORT_SCHEMA_03           SYNONYM                        VALID
PUBLIC                         SYS_EXPORT_SCHEMA_02           SYNONYM                        VALID
PUBLIC                         SYS_EXPORT_SCHEMA_01           SYNONYM                        VALID
SYSTEM                         SYS_EXPORT_FULL_01             TABLE                          VALID


select * from SYS_EXPORT_SCHEMA_01;

select * from SYS_EXPORT_SCHEMA_01
              *
ERROR at line 1:
ORA-00980: synonym translation is no longer valid


SQL> drop public synonym SYS_EXPORT_SCHEMA_03 ;
SQL> drop public synonym SYS_EXPORT_SCHEMA_02 ;
SQL> drop public synonym SYS_EXPORT_SCHEMA_01 ;

Then export will be successful.

expdp system/password@tnsname directory=directory_nm dumpfile=test.dmp logfile=test.log schemas=TESTUSER

Export: Release 12.1.0.2.0 - Production on Fri Jul 14 11:56:45 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@tnsname directory=directory_nm dumpfile=test.dmp logfile=test.log schemas=TESTUSER
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 13.5 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
.
.


No comments: