Thursday, July 26, 2012

Looping synonyms and transportable

Whilst doing an export as part of TTS

> expdp directory=tmp_dir dumpfile=test_meta.dmp transport_tablespaces=tts_conv

Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 31 May, 2012 11:03:50

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": /******** AS SYSDBA directory=tmp_dir dumpfile=test_meta.dmp transport_tablespaces=tts_conv
ORA-39123: Data Pump transportable tablespace job aborted
ORA-01001: invalid cursor
ORA-06512: at "SYS.DBMS_SYS_SQL", line 902
ORA-06512: at "SYS.DBMS_SQL", line 19
ORA-06512: at "SYS.DBMS_TTS", line 838
ORA-01775: looping chain of synonyms

Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" stopped due to fatal error at 11:03:59


SYS > alter system set events '1775 trace name errorstack level 3';

System altered.

From the trace file generated

the failing SQL statement is

*** 2012-06-04 10:17:14.026
ksedmp: internal or fatal error
ORA-01775: looping chain of synonyms
Current SQL statement for this session:
SELECT DISTINCT p.name, x.xmlschema, u.name FROM dba_xml_tables x, obj$ o, tab$ t, ts$ p, user$ u WHERE x.table_name = o.name AND o.obj# = t.obj# AND t.ts# =
p.ts# AND u.user# = o.owner# AND u.name = x.owner
----- Call Stack Trace -----

So if you run the command manually you receive the same error.

SYS> SELECT DISTINCT p.name, x.xmlschema, u.name FROM dba_xml_tables x, obj$ o, tab$ t, ts$ p, user$ u WHERE x.table_name = o.name AND o.obj# = t.obj# AND t.ts# =
p.ts# AND u.user# = o.owner# AND u.name = x.owner 2
3 /
SELECT DISTINCT p.name, x.xmlschema, u.name FROM dba_xml_tables x, obj$ o, tab$ t, ts$ p, user$ u WHERE x.table_name = o.name AND o.obj# = t.obj# AND t.ts# =
*
ERROR at line 1:
ORA-01775: looping chain of synonyms



> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Thu May 31 11:04:03 2012

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SYS> select object_name,owner,object_type from dba_objects where object_name='DBA_XML_TABLES';

OBJECT_NAME
--------------------------------------------------------------------------------
OWNER OBJECT_TYPE
------------------------------ -------------------
DBA_XML_TABLES
PUBLIC SYNONYM

So lets drop the public synonym

SYS > drop public SYNONYM DBA_XML_TABLES;

Synonym dropped.

SYS@>
and Real Application Testing options
> expdp directory=tmp_dir dumpfile=test_meta.dmp transport_tablespaces=tts_conv

Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 31 May, 2012 11:05:09

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": /******** AS SYSDBA directory=tmp_dir dumpfile=test_meta.dmp transport_tablespaces=tts_conv
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
/tmp/test_meta.dmp
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 11:05:35

As you can see the export completes successfully

You can re-create the public synonym by running the below

catxdbv.sql:86:create or replace public synonym dba_xml_tables for dba_xml_tables;

If you do a search on MOS for the string "ORA-01775 dbms_tts" you find


This note indicates the synonym can be dropped