Oracle Synonyms to Missing Objects Checker
Lists all synonyms that reference missing or non-existing objects. Helps DBAs identify broken synonyms that may cause runtime errors or invalid object references, with optional filtering by object schema.
oraclesqlmaintenance-cleanupv1.0.0
0 stars0 downloads16 views0 comments
By OracleDba • Created
Code
(26 lines)1234567891011121314151617181920212223242526
-- -----------------------------------------------------------------------------------
-- File Name : https://oracle-base.com/dba/monitoring/synonyms_to_missing_objects.sql
-- Author : Tim Hall
-- Description : Lists all synonyms that point to missing objects.
-- Call Syntax : @synonyms_to_missing_objects(object-schema-name or all)
-- Requirements : Access to the DBA views.
-- Last Modified: 07/10/2013
-- -----------------------------------------------------------------------------------
SET LINESIZE 1000 VERIFY OFF
SELECT s.owner,
s.synonym_name,
s.table_owner,
s.table_name
FROM dba_synonyms s
WHERE s.db_link IS NULL
AND s.table_owner NOT IN ('SYS','SYSTEM')
AND NOT EXISTS (SELECT 1
FROM dba_objects o
WHERE o.owner = s.table_owner
AND o.object_name = s.table_name
AND o.object_type != 'SYNONYM')
AND s.table_owner = DECODE(UPPER('&1'), 'ALL', s.table_owner, UPPER('&1'))
ORDER BY s.owner, s.synonym_name;
SET LINESIZE 80 VERIFY ON