Skip to content
Oracle DBA Scripts & Database Utilities

Database Objects Listing with Status (dobj.sql)

What This Script Does

Section titled “What This Script Does”

This script provides Oracle database administration functionality via the dobj.sql script.

The Script

Section titled “The Script”
rem  dobj.sql
rem
rem  linesize = 59
rem
ttitle '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 status format a1 heading 'S'
rem
break on owner skip 1
rem
select owner, object_name, object_type,
       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')
 order by owner, object_name;

What This Script Does

Section titled “What This Script Does”

This script provides a clean, simple listing of database objects with their status information. It allows filtering by owner, object type, and object name using pattern matching, making it useful for quick object inventory and status checking across schemas.

Key Features

Section titled “Key Features”
  • Flexible Filtering: Filter by owner, object type, and object name patterns
  • Status Indication: Shows object validity status with simple indicators
  • Clean Output: Organized by owner with clear formatting
  • Pattern Matching: Supports SQL wildcards (% and _) for flexible searches
  • Quick Reference: Fast way to locate and verify objects

Usage

Section titled “Usage”

Run the script and provide filter criteria when prompted:

@dobj.sql

Input Parameters:

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

Required Privileges

Section titled “Required Privileges”
SELECT on SYS.DBA_OBJECTS

Sample Output

Section titled “Sample Output”
                                Database Objects


OWNER        OBJECT NAME                    OBJECT TYPE   S
------------ ------------------------------ ------------- -
SCOTT        DEPT                           TABLE
             EMP                            TABLE
             EMP_NAME_IDX                   INDEX
             PK_DEPT                        INDEX
             PK_EMP                         INDEX


HR           COUNTRIES                      TABLE
             DEPARTMENTS                    TABLE
             EMPLOYEES                      TABLE
             GET_EMPLOYEE_PROC              PROCEDURE     I
             JOBS                           TABLE
             LOCATIONS                      TABLE
             UPDATE_SALARY_FUNC             FUNCTION


FINANCE      ACCOUNTS                       TABLE
             BUDGET_VIEW                    VIEW          I
             CALC_TAX_FUNC                  FUNCTION
             FINANCIAL_REPORTS              PACKAGE
             MONTHLY_SUMMARY                VIEW

Key Output Columns

Section titled “Key Output Columns”
  • OWNER: Schema that owns the object
  • OBJECT NAME: Name of the database object
  • OBJECT TYPE: Type of object (TABLE, INDEX, PROCEDURE, etc.)
  • S (Status): Object status indicator
    • Blank: Object is VALID
    • I: Object is INVALID
    • ?: Unknown status

Understanding Object Status

Section titled “Understanding Object Status”

Valid Objects

Section titled “Valid Objects”
  • No status indicator shown (blank)
  • Object is compiled and ready for use
  • No compilation errors

Invalid Objects

Section titled “Invalid Objects”
  • Marked with “I” in status column
  • Object has compilation errors or dependencies issues
  • Needs recompilation or dependency resolution

Common Object Types

Section titled “Common Object Types”
  • TABLE: Data tables
  • INDEX: Database indexes
  • VIEW: Database views
  • PROCEDURE: Stored procedures
  • FUNCTION: Database functions
  • PACKAGE: PL/SQL packages
  • TRIGGER: Database triggers
  • SEQUENCE: Sequence generators
  • SYNONYM: Object synonyms

Common Use Cases

Section titled “Common Use Cases”

  1. Object Inventory

    • List all objects in a specific schema
    • Inventory objects by type across all schemas

  2. Status Checking

    • Find invalid objects needing attention
    • Verify object compilation status after changes

  3. Object Location

    • Find objects by name pattern across schemas
    • Locate specific object types

  4. Schema Analysis

    • Compare object counts between environments
    • Analyze schema composition

Filter Examples

Section titled “Filter Examples”

List All Tables in SCOTT Schema

Section titled “List All Tables in SCOTT Schema”
Owner: SCOTT
Type: TABLE
Object: %

Find All Invalid Procedures

Section titled “Find All Invalid Procedures”
Owner: %
Type: PROCEDURE
Object: %

Then look for ‘I’ in status column

Find Objects with Specific Name Pattern

Section titled “Find Objects with Specific Name Pattern”
Owner: %
Type: %
Object: EMP%

List All Objects in Specific Schema

Section titled “List All Objects in Specific Schema”
Owner: HR
Type: %
Object: %

Advanced Usage Tips

Section titled “Advanced Usage Tips”

Finding Specific Object Categories

Section titled “Finding Specific Object Categories”
-- Find all constraints
Type: %CONSTRAINT


-- Find all database links
Type: DATABASE LINK


-- Find all materialized views
Type: MATERIALIZED VIEW

Pattern Matching Examples

Section titled “Pattern Matching Examples”
-- Objects starting with 'TMP'
Object: TMP%


-- Objects ending with '_BAK'
Object: %_BAK


-- Objects containing 'AUDIT'
Object: %AUDIT%

Troubleshooting

Section titled “Troubleshooting”

No Results Returned

  • Check if the filter patterns match existing objects
  • Verify the schema names are spelled correctly
  • Ensure you have privileges to see the objects

Too Many Results

  • Use more specific patterns to narrow results
  • Filter by object type first, then by name
  • Use specific schema names instead of wildcards

Missing Objects

  • Some objects may be in different schemas than expected
  • Check synonyms that might point to objects in other schemas
  • Verify object names are spelled correctly

Performance Considerations

Section titled “Performance Considerations”
  • Using % wildcards for all parameters may return large result sets
  • DBA_OBJECTS is a frequently accessed view, generally performs well
  • Consider adding WHERE clauses for very large databases
Section titled “Related Scripts”