Monday, November 19, 2012

Weird phantom object... Can't create, but can't drop neither

After doing a full database export/import, it left me a "phantom" object for the public synonym in sys.obj$ in the target database, but nothing shows in dba_objects for that synonym. As a result, I can neither create such synonym (got ORA-00995: missing or invalid synonym identifier), nor dropping it (got ORA-01432: public synonym to be dropped does not exist). Eventually compiling the base object removed the row from sys.obj$.

But it's still kind of a mystery. According to this document (Public Synonym ST_GEOMETRY For SDE.ST_GEOMETRY Removed After Upgrade To 10.2.0.4 (Doc ID 577529.1)), these public synonyms shouldn't be exist even in 10g databases. But my source database is 11.1 and target database is 11.2.0.3.


SQL> CREATE PUBLIC SYNONYM ST_MULTISURFACE for sde.ST_MULTISURFACE
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00995: missing or invalid synonym identifier
ORA-06512: at line 48

SQL> drop public synonym ST_MULTISURFACE;
drop public synonym ST_MULTISURFACE
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> select obj#, owner#, name, type# from sys.obj$ where
name='ST_MULTISURFACE';

OBJ# OWNER# NAME TYPE#
---------- ---------- ------------------------------ ----------
92147 0 ST_MULTISURFACE 10
63690 57 ST_MULTISURFACE 13
64338 57 ST_MULTISURFACE 14
79560 87 ST_MULTISURFACE 13
89355 87 ST_MULTISURFACE 14

Note that dba_objects only returuns 4 rows, instead of 5.

SQL> select owner,object_name from dba_objects where object_name='ST_MULTISURFACE'

OWNER OBJECT_NAME
------------------------------ ------------------------------
MDSYS ST_MULTISURFACE
MDSYS ST_MULTISURFACE
SDE ST_MULTISURFACE
SDE ST_MULTISURFACE


1 comment:

  1. Lazy Dba Workshop: Weird Phantom Object... Can'T Create, But Can'T Drop Neither >>>>> Download Now

    >>>>> Download Full

    Lazy Dba Workshop: Weird Phantom Object... Can'T Create, But Can'T Drop Neither >>>>> Download LINK

    >>>>> Download Now

    Lazy Dba Workshop: Weird Phantom Object... Can'T Create, But Can'T Drop Neither >>>>> Download Full

    >>>>> Download LINK 6S

    ReplyDelete