list of all non system synonym that have no objects associated with it
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: list of all non system synonym that have no objects associated with it

  1. #1
    Join Date
    Dec 2001
    Posts
    96

    list of all non system synonym that have no objects associated with it

    Hi,

    How to get a list of all non system synonym that have no objects associated with it ?

    Thanks

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    outer join with dba_synonyms and dba_objects and look for null values from dba_objects.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Dec 2001
    Posts
    96
    Can I get a query to arrive at this list ?

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    yes by doing the query suggested

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,003
    Try this.

    Code:
    SELECT ds.owner, ds.synonym_name
      FROM dba_synonyms ds
      LEFT OUTER JOIN all_objects do
        ON ds.owner        = do.owner         AND
           ds.synonym_name = do.object_name
     WHERE do.object_name IS NULL             AND
           do.owner       IN ('SYSTEM', 'SYS');
    this space intentionally left blank

  6. #6
    Join Date
    Dec 2001
    Posts
    96
    If I do this
    SELECT ds.owner, ds.synonym_name
    FROM dba_synonyms ds,all_objects do
    WHERE ds.owner(+) = do.owner AND
    do.object_name IS NULL AND
    ds.synonym_name = do.object_name and
    do.owner IN ('SYSTEM', 'SYS');
    -- no rows selected.

  7. #7
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,003
    Originally posted by aj_usa
    If I do this
    SELECT ds.owner, ds.synonym_name
    FROM dba_synonyms ds,all_objects do
    WHERE ds.owner(+) = do.owner AND
    do.object_name IS NULL AND
    ds.synonym_name = do.object_name and
    do.owner IN ('SYSTEM', 'SYS');
    -- no rows selected.
    Are you using 8i?

    You should also be able to do this.

    Code:
    SELECT owner, object_name, last_ddl_time, status, 
                  temporary,   generated,     secondary
      FROM all_objects
     WHERE owner NOT IN( 'SYS', 'SYSTEM' ) AND
           object_type = 'SYNONYM'         AND
           status      = 'INVALID';
    this space intentionally left blank

  8. #8
    Join Date
    Apr 2003
    Posts
    353
    Tables of synonyms if renamed
    the synonyms will not be invalidated and will not be
    displayed with the above query.

    The synonyms on selection will give
    ORA-00980: synonym translation is no longer valid

    They need to be identified with
    http://www.oracle.com/technology/ora...03/113003.html

    --syn not displayed
    SQL> SELECT owner, object_name, last_ddl_time, status,
    2 temporary, generated, secondary
    3 FROM all_objects
    4 WHERE owner NOT IN( 'SYS', 'SYSTEM' ) AND
    5 object_type = 'SYNONYM' AND
    6 status = 'INVALID';

    no rows selected

    --Synonyms displayed here with the below query that are no longer
    valid(for testing purpose I created
    the last synonym at scott user and renamed the table.)

    SQL> select 'drop '||decode (s.owner,'PUBLIC','PUBLIC SYNONYM ',
    2 'SYNONYM'||s.owner||'.')||s.synonym_name||';'
    3 from dba_synonyms s
    4 where table_owner not in('SYSTEM','SYS')
    5 and db_link is null
    6 and not exists
    7 (select 1
    8 from dba_objects o
    9 where s.table_owner=o.owner
    10 and s.table_name=o.object_name)
    11 /

    'DROP'||DECODE(S.OWNER,'PUBLIC','PUBLICSYNONYM','SYNONYM'||S.OWNER||'.')||
    --------------------------------------------------------------------------
    drop PUBLIC SYNONYM /4e6818ad_OrdSourceSvr;
    drop PUBLIC SYNONYM ALL_OLAP_OS_CHILD_INSTANCES;
    drop PUBLIC SYNONYM DBA_OLAP_OS_CHILD_INSTANCES;
    drop PUBLIC SYNONYM ALL_OLAP_OS_OSA_PRIVILEGES;
    drop PUBLIC SYNONYM DBA_OLAP_OS_OSA_PRIVILEGES;
    drop PUBLIC SYNONYM CWM_OLAP_SERVER_LIST;
    drop PUBLIC SYNONYM ALL_OLAP2_AW_MAP_LEVEL_USE;
    drop SYNONYMSCOTT.TEST23SYN;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width