Invalid Database Objects Analysis (dobjecti.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 provides Oracle database administration functionality via the dobjecti.sql script.

The Script

rem  dobjecti.sql
rem
set linesize 132
rem
ttitle 'Invalid Database Objects'
rem
col owner format a12 heading 'OWNER'
col object_name format a30 heading 'OBJECT NAME'
rem col object_id format 99999 heading 'ID'
col object_type format a13 heading 'OBJECT TYPE'
col created format a9 heading 'CREATED'
col modified format a14 heading 'MODIFIED'
col status format a1 heading 'S'
rem
break on owner skip 1
rem
select owner, object_name, object_type, created,
       decode( last_ddl_time, to_char(created,'mm/dd hh24:mi:ss'), null, to_char(last_ddl_time,'mm/dd hh24:mi:ss')) modified,
       decode( status, 'VALID', null, 'INVALID', 'I', '?' ) status
  from sys.dba_objects
 where owner like upper('&owner')
   and object_type like upper('&type')
   and object_name like upper('&object')
   and status != 'VALID'
 order by owner, object_name;

What This Script Does

This focused script identifies all invalid database objects across schemas, showing their creation and modification dates. It’s essential for database health monitoring, deployment validation, and troubleshooting compilation issues. Invalid objects can indicate dependency problems, missing privileges, or failed deployments.

Key Features

• Invalid Objects Only: Filters specifically for objects with status != ‘VALID’
• Flexible Filtering: Filter by owner, object type, and object name patterns
• Timestamp Information: Shows when objects were created and last modified
• Organized Output: Grouped by owner for easy review
• Status Indication: Clear invalid status marking

Usage

Run the script and provide filter criteria when prompted:

@dobjecti.sql

Input Parameters:

• Owner: Schema owner name or pattern (use % for all owners)
• Type: Object type or pattern (e.g., PROCEDURE, FUNCTION, PACKAGE)
• Object: Object name or pattern (use % for all objects)

Required Privileges

SELECT on SYS.DBA_OBJECTS

Sample Output

                                        Invalid Database Objects

OWNER        OBJECT NAME                    OBJECT TYPE   CREATED   MODIFIED       S
------------ ------------------------------ ------------- --------- -------------- -
HR           CALCULATE_BONUS               FUNCTION      03/15     03/20 14:30    I
             EMPLOYEE_REPORTS              PACKAGE       02/28     03/18 16:45    I
             EMPLOYEE_REPORTS              PACKAGE BODY  02/28     03/18 16:45    I
             GET_DEPARTMENT_PROC           PROCEDURE     03/10     03/22 11:20    I
             UPDATE_SALARY_HISTORY         PROCEDURE     01/15                    I

FINANCE      BUDGET_CALCULATIONS           PACKAGE       03/01     03/25 09:15    I
             BUDGET_CALCULATIONS           PACKAGE BODY  03/01     03/25 09:15    I
             MONTHLY_REPORT_VIEW           VIEW          02/20     03/19 13:30    I
             TAX_CALCULATION_FUNC          FUNCTION      03/05                    I

SALES        COMMISSION_TRIGGER            TRIGGER       03/12     03/23 10:45    I
             CUSTOMER_ANALYSIS_PKG         PACKAGE       02/15     03/21 15:20    I
             SALES_REPORT_VIEW             VIEW          03/08                    I

Key Output Columns

• OWNER: Schema that owns the invalid object
• OBJECT NAME: Name of the invalid object
• OBJECT TYPE: Type of object (PROCEDURE, FUNCTION, PACKAGE, etc.)
• CREATED: Date when object was first created
• MODIFIED: Date when object was last modified (blank if never modified)
• S (Status): Status indicator (I = INVALID)

Understanding Invalid Objects

Common Object Types That Become Invalid

PL/SQL Objects

• PROCEDURE: Stored procedures with compilation errors
• FUNCTION: Functions with syntax or dependency issues
• PACKAGE: Package specifications with problems
• PACKAGE BODY: Package bodies with implementation errors
• TRIGGER: Database triggers with compilation failures

Database Objects

• VIEW: Views with invalid underlying tables or columns
• MATERIALIZED VIEW: MVs with dependency or privilege issues
• SYNONYM: Synonyms pointing to non-existent objects

Common Causes of Invalid Objects

1. Dependency Changes

   • Underlying tables or columns dropped/modified
   • Referenced objects becoming invalid
   • Privilege changes on dependent objects

2. Compilation Errors

   • Syntax errors in PL/SQL code
   • Invalid SQL statements
   • Missing or incorrect references

3. Schema Changes

   • Table structure modifications
   • Column datatype changes
   • Index or constraint modifications

4. Security Issues

   • Missing privileges on referenced objects
   • Revoked access to dependent schemas
   • Invalid definer rights

Analysis and Resolution

Prioritizing Invalid Objects

1. Recently Modified Objects

   • Objects with recent MODIFIED dates likely need immediate attention
   • May be related to recent deployments or changes

2. Core Application Objects

   • Critical business logic procedures and functions
   • Frequently used packages and triggers

3. Dependency Chains

   • Objects that other valid objects depend on
   • Starting point for cascade compilation

Resolution Strategies

Automatic Compilation

-- Compile specific object
ALTER PROCEDURE hr.calculate_bonus COMPILE;
ALTER PACKAGE finance.budget_calculations COMPILE;
ALTER PACKAGE BODY finance.budget_calculations COMPILE;
ALTER VIEW sales.sales_report_view COMPILE;

Bulk Compilation

-- Use utlrp.sql for system-wide recompilation
@?/rdbms/admin/utlrp.sql

-- Or use DBMS_UTILITY
EXEC DBMS_UTILITY.COMPILE_SCHEMA('HR');

Common Use Cases

1. Post-Deployment Validation

   • Verify all objects compiled successfully after deployment
   • Identify objects that need attention after schema changes

2. Database Health Monitoring

   • Regular checks for invalid objects
   • Proactive identification of compilation issues

3. Upgrade Preparation

   • Clean up invalid objects before database upgrades
   • Ensure all objects are valid for migration

4. Troubleshooting

   • Identify root cause of application errors
   • Find objects affected by schema changes

5. Change Impact Analysis

   • Assess impact of proposed schema changes
   • Identify dependent objects that may become invalid

Advanced Analysis

Find Compilation Errors

-- After identifying invalid objects, check errors:
SELECT owner, name, type, line, position, text
FROM dba_errors
WHERE owner = 'HR'
AND name = 'CALCULATE_BONUS'
ORDER BY line, position;

Dependency Analysis

-- Find what the invalid object depends on:
SELECT owner, name, type, referenced_owner, referenced_name, referenced_type
FROM dba_dependencies
WHERE owner = 'HR'
AND name = 'CALCULATE_BONUS';

Cascade Impact

-- Find what depends on the invalid object:
SELECT owner, name, type
FROM dba_dependencies
WHERE referenced_owner = 'HR'
AND referenced_name = 'CALCULATE_BONUS';

Filter Examples

All Invalid PL/SQL Objects

Owner: %
Type: PROCEDURE,FUNCTION,PACKAGE%
Object: %

Invalid Objects in Specific Schema

Owner: HR
Type: %
Object: %

Recently Modified Invalid Objects

Look for objects with MODIFIED dates in recent deployment windows.

Troubleshooting

No Results

• Good news - no invalid objects match the criteria
• All objects in the filtered scope are valid

Many Results After Deployment

• Common after schema changes or application deployments
• Prioritize by object importance and dependency order
• Consider bulk recompilation

Persistent Invalid Objects

• Check DBA_ERRORS for compilation error details
• Verify dependencies and privileges
• May require code fixes or schema adjustments

Integration with Deployment Process

Pre-Deployment Check

-- Document current invalid objects before deployment
-- Compare after deployment to identify new issues

Post-Deployment Validation

-- Run dobjecti.sql after each deployment
-- Ensure no new invalid objects were introduced
-- Compile or fix any issues found

Related Scripts

• dobj.sql - All database objects with status
• dobjectf.sql - Objects with full timestamps
• compile_invalid.gen - Generate compilation commands
• derror.sql - Compilation error analysis