1

I am trying to query the Oracle ALL_SYNONYMS view, but filtering by OWNER=PUBLIC returns both synonyms I have created and Oracle created ones. Is there a way to distinguish between the two?

1 Answer 1

1

Well, you could go by object creation date:

SELECT AO.*
  FROM ALL_OBJECTS AO
  INNER JOIN ALL_SYNONYMS ASY ON AO.OWNER = ASY.OWNER and AO.OBJECT_NAME = ASY.SYNONYM_NAME
  AND ASY.OWNER = 'PUBLIC'
  ORDER by AO.CREATED DESC

The system created synonyms should all have the same created date, equal to the creation time of the database and the earliest of this list.

You can also filter out the system owned objects being referenced by the synonym using this :

SELECT AO.*
  FROM ALL_OBJECTS AO
  INNER JOIN ALL_SYNONYMS ASY ON AO.OWNER = ASY.OWNER and AO.OBJECT_NAME = ASY.SYNONYM_NAME
  AND ASY.OWNER = 'PUBLIC'
  AND ASY.TABLE_OWNER NOT IN ('CTXSYS','EXFSYS','APPQOSSYS','XDB','SYS', 'DVSYS', 'FLOWS_FILES', 'LBACSYS', 'MDSYS', 'OLAPSYS', 'ORDSYS', 'ORDDATA','WMSYS','SYSTEM', 'DBMS_PRIVILEGE_CAPTURE') 
  and ASY.TABLE_OWNER not like 'APEX_%'
  ORDER by AO.CREATED DESC
3
  • That doesn't seem to work. The creation time seems to widely vary, from Aug 2, 2010 to Feb 4 2014 on my particular instance. Too much to be able to reliably know which were generated and which were created by me Jun 3, 2014 at 20:51
  • 2
    include that table_owner = SYS. The system generated public synonyms won't be on the application's objects.
    – user953
    Jun 4, 2014 at 7:39
  • 1
    That is what I ended up settling on, although I needed to also filter out table_owners in ('CTXSYS','EXFSYS','APPQOSSYS','XDB','SYS', 'DVSYS', 'FLOWS_FILES', 'LBACSYS', 'MDSYS', 'OLAPSYS', 'ORDSYS', 'ORDDATA','WMSYS','SYSTEM', 'DBMS_PRIVILEGE_CAPTURE') and not like 'APEX_%'. If you add that as an answer I'll accept it Jun 4, 2014 at 13:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.