The Problem Link to heading

This one had me scratching my head for a while.

declare
  tc_out clob;
begin
  dbms_sqldiag.export_sql_testcase(directory=>'EXP_TC', INCIDENT_ID=>722551, testcase => tc_out);
  dbms_output.put(tc_out);
end;
/

2 3 4 5 6 7 8 9 10
declare
*
ERROR at line 1:
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 1137
ORA-06512: at "SYS.DBMS_DATAPUMP", line 5658
ORA-06512: at line 154
ORA-06512: at "SYS.DBMS_SQL", line 1825
ORA-06512: at "SYS.DBMS_SQLTCB_INTERNAL", line 391
ORA-06512: at "SYS.DBMS_SQLTCB_INTERNAL", line 1713
ORA-06512: at "SYS.DBMS_SQLTCB_INTERNAL", line 2950
ORA-06512: at "SYS.DBMS_SQLDIAG", line 235
ORA-06512: at line 4

Well, it turns out that expdp wasn’t working either - it simply reported:

ORA-39001: invalid argument value
ORA-01775: looping chain of synonyms

Root Cause Link to heading

Explanation to be found in Oracle Support note 459151.1.

Effectively, it means that there’s a public synonym or two, which points to non-existent objects, stopping data pump from working:

select synonym_name, TABLE_OWNER, TABLE_NAME
from dba_synonyms
where owner='PUBLIC'
and regexp_like(synonym_name, '^(SYS_EXPORT|SYS_IMPORT)')
/

SYNONYM_NAME                   TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------ ------------------------------
SYS_EXPORT_SCHEMA_01           TESTUS                         SYS_EXPORT_TABLE_01

Solution Link to heading 

drop public synonym SYS_EXPORT_SCHEMA_01;

Finally, I should add that this happened in an Oracle 11.2.0.2.3 database, which had previously been upgraded on a number of occasions. The actual synonym was a remnant from a prior release.

Originally published at https://jensenmo.blogspot.com/2011/11/what-could-be-wrong-when-dbmssqldiag.html