What could be wrong when DBMS_SQLDIAG fails when exporting a test case?

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

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:


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

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

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.