Find the Cause of Invalid Objects

--
-- Find the Cause of Invalid Objects
--
 
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
SET VERIFY OFF
 
SELECT owner, TRIM (missing_table) as Missing_Table, COUNT (*)
FROM (SELECT owner,
procedure_name,
line,
text,
table_string,
REGEXP_SUBSTR (table_string,
'( [^ ]+ | [^;]+;| [^ ]+$)',
REGEXP_INSTR (table_string,
'(join|into|from|update)',
1,
1,
1,
'i')) "MISSING_TABLE"
FROM (SELECT a.name AS procedure_name,
a.owner,
a.line,
a.text,
b.text AS table_string
FROM dba_errors a, dba_source b
WHERE REGEXP_LIKE (a.text, 'ORA-00942')
AND NOT REGEXP_LIKE (b.text, '( *merge$| *insert$| *select)','i')
AND a.TYPE in ('PROCEDURE')
AND a.OWNER = b.OWNER
AND a.NAME = b.NAME
AND a.TYPE = b.TYPE
AND a.line = b.line
-- AND rownum <1500
)
)
GROUP BY owner, TRIM (missing_table)
ORDER BY 3 DESC

--
-- Find the Cause of Invalid Objects
--
 
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
SET VERIFY OFF
 
SELECT a.name,
a.owner,
a.line,
a.text,
b.text
FROM dba_errors a, dba_source b
WHERE a.text LIKE '%ORA-00942%'
AND a.TYPE = 'PROCEDURE'
AND a.OWNER = b.OWNER
AND a.NAME = b.NAME
AND a.TYPE = b.TYPE
AND a.line = b.line
ORDER BY 1, 2, 3

---

Published 1st October 2021