1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

List of all synonyms which indicate on nonexistent objects

Discussion in 'SQL PL/SQL' started by NikoTrend1, Apr 1, 2014.

  1. NikoTrend1

    NikoTrend1 Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    This is for db_links, but what about objects?
    Code (Text):

    SELECT * FROM Dba_synonyms
    WHERE db_link is null;
     
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Code (SQL):

    SELECT s.owner, s.synonym_name, s.table_owner, s.TABLE_NAME
    FROM dba_synonyms s LEFT JOIN dba_tables t ON s.table_owner = t.owner AND s.TABLE_NAME = t.TABLE_NAME
    WHERE t.TABLE_NAME IS NULL
    AND instr(s.TABLE_NAME, '/') = 0
    AND s.TABLE_NAME IN (SELECT object_name FROM dba_objects WHERE object_type = 'TABLE')
    /
     

    The above query should work for you; the issue with DBA_SYNONYMS is that TABLE_NAME doesn't always refer to a table. You CAN make it easier on yourself by querying DBA_INVALID_OBJECTS:


    Code (SQL):
    SELECT owner, object_name
    FROM dba_invalid_objects
    WHERE object_type = 'SYNONYM'
    /
     
     
    NikoTrend1 likes this.