--

-- Find the Cause of Invalid Objects (Detailed)

--

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 (Quick)

--

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