Find Invalid Objects (dinvalid.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script helps database administrators quickly identify invalid objects in the database. Invalid objects can cause runtime errors and should be recompiled or investigated.

The Script

rem  dinvalid.sql
rem
ttitle 'Invalid Database Objects'
rem
set lines 160
set pagesize 50
rem
clear col
col owner format a20 heading 'OWNER'
col object_type format a20 heading 'OBJECT_TYPE'
col object_name format a30 heading 'OBJECT_NAME'
col object_cnt format 99999 heading 'OBJ|CNT'
col invalid_cnt format 99999 heading 'INVALID|CNT'
rem

select owner
     , object_type
     , object_name
--     , count(*) object_cnt
--     , sum(decode( status, 'VALID', null, 1 )) invalid_cnt
  from sys.dba_objects
 where owner like nvl(upper('&owner'),'%')
   and object_type like nvl(upper('&type'),'%')
   and object_name like nvl(upper('&object'),'%')
   and owner not in ('SYS','SYSTEM','WMSYS','OUTLN','SCOTT','TSMSYS','PUBLIC')
   and status <> 'VALID'
-- group by owner, object_type
 order by owner, object_type
/

Usage

-- Basic usage
@dinvalid.sql

-- When prompted, enter:
-- owner: ** - Schema owner to filter (use % for all)
-- type: ** - Object type to filter (use % for all)
-- object: ** - Object name pattern to filter (use % for all)

Parameters

The script prompts for:

• &owner - ** - Schema owner to filter (use % for all)
• &type - ** - Object type to filter (use % for all)
• &object - ** - Object name pattern to filter (use % for all)

Required Privileges

SELECT ANY DICTIONARY
-- OR --
SELECT ON DBA_OBJECTS

Sample Output

OWNER                OBJECT_TYPE          OBJECT_NAME
-------------------- -------------------- ------------------------------
HR                   PROCEDURE            UPDATE_JOB_HISTORY
HR                   TRIGGER              SECURE_EMPLOYEES
SCOTT                VIEW                 DEPT_SUMMARY

Key Output Columns

• OWNER - Schema that owns the object
• OBJECT_TYPE - Type of database object (PROCEDURE, FUNCTION, VIEW, etc.)
• OBJECT_NAME - Name of the invalid object

Common Use Cases

Find All Invalid Objects

-- Check all invalid objects across all schemas
@dinvalid.sql
-- Enter: % for all parameters

Check Specific Schema

-- Find invalid objects in HR schema
@dinvalid.sql
-- Enter: HR for owner, % for type and object

Related Scripts

• Object Analysis - Detailed object information
• Jobs Status - Check scheduled job status