Hi,
How to get a list of all non system synonym that have no objects associated with it ?
Thanks
Hi,
How to get a list of all non system synonym that have no objects associated with it ?
Thanks
outer join with dba_synonyms and dba_objects and look for null values from dba_objects.
Can I get a query to arrive at this list ?
yes by doing the query suggested
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');
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?Quote:
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.
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';
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;