DBA_OBJECTS - Find Oracle Objects by Type, Status & Date

DBA_OBJECTS

Overview

DBA_OBJECTS is the master catalog of all named database objects in Oracle: tables, indexes, views, packages, procedures, functions, triggers, sequences, synonyms, types, materialized views, database links, and more. It is the single source of truth for object status (VALID vs INVALID), creation and modification timestamps, and the internal OBJECT_ID that appears in performance views such as V$SESSION and V$BH. DBAs query it constantly after deployments, imports, and patch applications.

View Type: Data Dictionary View (static) Available Since: Oracle 7 Required Privileges: SELECT on DBA_OBJECTS or SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY

The scoped variants are USER_OBJECTS (current schema only, no OWNER column) and ALL_OBJECTS (objects accessible to the current user).

Key Columns

Column	Datatype	Description
OWNER	VARCHAR2(128)	Schema that owns the object
OBJECT_NAME	VARCHAR2(128)	Name of the object
SUBOBJECT_NAME	VARCHAR2(128)	Partition or subpartition name for partitioned objects; NULL for non-partitioned objects
OBJECT_ID	NUMBER	Unique internal identifier assigned when the object was created; used in DBA_EXTENTS, V$BH cache analysis
DATA_OBJECT_ID	NUMBER	Internal ID of the physical segment; can differ from OBJECT_ID after table moves or partition exchanges; matches V$SESSION.ROW_WAIT_OBJ#
OBJECT_TYPE	VARCHAR2(23)	Type string: TABLE, INDEX, VIEW, PACKAGE, PACKAGE BODY, PROCEDURE, FUNCTION, TRIGGER, SEQUENCE, SYNONYM, TYPE, TYPE BODY, DATABASE LINK, MATERIALIZED VIEW, TABLE PARTITION, INDEX PARTITION, etc.
CREATED	DATE	Timestamp when the object was originally created
LAST_DDL_TIME	DATE	Timestamp of the last DDL operation on the object, including GRANT, REVOKE, TRUNCATE, ALTER, and recompilation
TIMESTAMP	VARCHAR2(19)	Last compilation timestamp as a string (YYYY-MM-DD:HH24:MI:SS) for PL/SQL units and views; used to detect recompilation events
STATUS	VARCHAR2(7)	VALID, INVALID (failed compilation or broken dependency), or N/A (objects that do not compile, such as tables and sequences)
TEMPORARY	VARCHAR2(1)	Y for session-duration temporary objects, N for persistent objects
GENERATED	VARCHAR2(1)	Y if the name was system-generated (system-named constraints, LOB segment names, internal index names)
SECONDARY	VARCHAR2(1)	Y for secondary objects created implicitly as part of another object (e.g., domain index sub-objects)
EDITION_NAME	VARCHAR2(128)	Edition name for objects in an edition-based redefinition (EBR) context; NULL for non-edition-enabled objects

Essential Queries

Invalid Objects

The most-used query in post-deployment validation — find every PL/SQL unit, view, or other compilable object that failed its last compilation. Run this after any DDL-intensive operation:

SELECT
    owner,
    object_type,
    object_name,
    status,
    last_ddl_time,
    timestamp                                   AS last_compile_attempt
FROM
    dba_objects
WHERE
    status = 'INVALID'
    AND owner NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'SYSMAN', 'OUTLN',
                      'MDSYS', 'ORDSYS', 'EXFSYS', 'CTXSYS', 'WMSYS',
                      'XDB', 'ORDPLUGINS', 'DMSYS', 'APEX_040200')
ORDER BY
    owner,
    object_type,
    object_name;

After identifying invalid objects, recompile with:

-- Recompile all invalid objects in one schema
EXEC DBMS_UTILITY.COMPILE_SCHEMA(schema => 'MY_SCHEMA', compile_all => FALSE);

-- Recompile the entire database serially (safe on active systems)
EXEC UTL_RECOMP.RECOMP_SERIAL();

-- Check specific errors for a named object
SELECT line, position, text
FROM   dba_errors
WHERE  owner       = 'MY_SCHEMA'
AND    name        = 'MY_PACKAGE'
AND    type        = 'PACKAGE BODY'
ORDER  BY sequence;

Objects Created or Modified in the Last 7 Days

Track recent schema changes after a deployment window or to investigate unexpected modifications. LAST_DDL_TIME is updated by GRANT, REVOKE, and TRUNCATE as well as structural DDL:

SELECT
    owner,
    object_type,
    object_name,
    created,
    last_ddl_time,
    status,
    CASE
        WHEN created      > SYSDATE - 7 THEN 'NEW'
        WHEN last_ddl_time > SYSDATE - 7 THEN 'MODIFIED'
    END                                         AS change_type
FROM
    dba_objects
WHERE
    owner NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'SYSMAN', 'OUTLN',
                  'MDSYS', 'ORDSYS', 'EXFSYS', 'CTXSYS', 'WMSYS',
                  'XDB', 'ORDPLUGINS', 'DMSYS', 'APEX_040200')
    AND (
        created       > SYSDATE - 7
        OR last_ddl_time > SYSDATE - 7
    )
    AND generated = 'N'
ORDER BY
    last_ddl_time DESC,
    created DESC;

Object Count by Type and Schema

Produce an inventory of all application schema objects, grouped by type. Essential for migration scoping, environment comparison, and auditing object sprawl:

SELECT
    owner,
    object_type,
    COUNT(*)                                    AS object_count,
    SUM(CASE WHEN status = 'INVALID' THEN 1 ELSE 0 END) AS invalid_count,
    MIN(created)                                AS oldest,
    MAX(last_ddl_time)                          AS most_recently_changed
FROM
    dba_objects
WHERE
    owner NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'SYSMAN', 'OUTLN',
                  'MDSYS', 'ORDSYS', 'EXFSYS', 'CTXSYS', 'WMSYS',
                  'XDB', 'ORDPLUGINS', 'DMSYS', 'APEX_040200')
    AND generated = 'N'
GROUP BY
    owner,
    object_type
ORDER BY
    owner,
    object_count DESC;

Identify Object from V$SESSION ROW_WAIT_OBJ#

When a session is waiting on a row lock, V$SESSION.ROW_WAIT_OBJ# contains the DATA_OBJECT_ID of the contended object. Use this query to resolve it to an owner and object name for immediate diagnosis:

SELECT
    s.sid,
    s.serial#,
    s.username,
    s.event,
    s.seconds_in_wait,
    s.row_wait_obj#,
    o.owner           AS locked_object_owner,
    o.object_name     AS locked_object_name,
    o.object_type,
    o.subobject_name  AS partition_name
FROM
    v$session  s
    LEFT JOIN dba_objects o
        ON o.data_object_id = s.row_wait_obj#
WHERE
    s.row_wait_obj# <> -1
    AND s.username IS NOT NULL
ORDER BY
    s.seconds_in_wait DESC;

Find All Objects in a Schema with Creation Timeline

Generate a complete chronological object timeline for a schema — useful for onboarding, schema documentation, and forensic investigation after an incident:

SELECT
    object_type,
    object_name,
    subobject_name,
    created,
    last_ddl_time,
    status,
    temporary
FROM
    dba_objects
WHERE
    owner = :schema_name
    AND generated = 'N'
ORDER BY
    created,
    object_type,
    object_name;

Common Use Cases

• Post-deployment validation — Run the invalid objects query immediately after releasing code changes; even a clean Data Pump import can introduce invalid objects if the target environment is missing grants or synonym targets
• Change audit without audit trail — When no formal change management log exists, sort by LAST_DDL_TIME DESC to reconstruct a DDL timeline; remember that grants and revokes also update this timestamp
• Migration and compare scoping — Count objects by type and schema in both source and target environments and diff the results to verify a schema migration was complete
• Row-level lock investigation — Combine V$SESSION.ROW_WAIT_OBJ# with DBA_OBJECTS.DATA_OBJECT_ID to immediately name the table or partition a session is trying to lock, shortcutting the investigation from “unknown object” to actionable information
• Orphan and clutter detection — Filter on GENERATED = ‘Y’ and OBJECT_TYPE IN (‘INDEX’, ‘LOB’) to find system-named objects left behind by incomplete schema migrations or failed DDL operations
• Edition-based redefinition inventory — Filter on EDITION_NAME IS NOT NULL to enumerate all edition-visible objects and confirm that the correct edition is current in each session via the EDITION parameter

Related Views

• DBA_TABLES — Extends DBA_OBJECTS for TABLE-type objects with storage statistics, row counts, and compression status
• DBA_INDEXES — Extends DBA_OBJECTS for INDEX-type objects with clustering factor, leaf blocks, and status
• DBA_SOURCE — PL/SQL source code; join on OWNER, NAME (= OBJECT_NAME), TYPE (= OBJECT_TYPE) to retrieve the body of any compilable unit
• DBA_ERRORS — Compilation error messages and line numbers for invalid objects; join on OWNER, NAME, TYPE to diagnose the root cause of STATUS = ‘INVALID’
• DBA_DEPENDENCIES — Object dependency graph; reveals which objects a package references and which objects reference it, helping predict the impact of DDL changes
• V$SESSION — ROW_WAIT_OBJ# in V$SESSION joins to DATA_OBJECT_ID in DBA_OBJECTS to resolve row-level waits to a specific table or partition

Version Notes

• Oracle 10g: SECONDARY column added; GENERATED reliably populated for system-named objects; object recyclebin entries visible in DBA_OBJECTS with names beginning BIN$
• Oracle 11g: EDITION_NAME column added to support edition-based redefinition; RECYCLEBIN objects now have a separate RECYCLEBIN view and are less prominent in DBA_OBJECTS
• Oracle 12c: APPLICATION column added in 12.2 to flag objects belonging to an Application Container; OBJECT_ID values in CDB environments are unique only within their container
• Oracle 12c (Multitenant): CDB_OBJECTS view available from the CDB root includes a CON_ID column; each PDB maintains a completely independent object namespace
• Oracle 23ai: SHARING column available for Application Common Objects, distinguishing METADATA LINK, OBJECT LINK, EXTENDED DATA LINK, and NONE sharing modes; ORIGIN_CON_ID tracks which container originally defined a shared object