Find Database Objects by Object ID (dobjectid.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 dobjectid.sql script.

The Script

rem  dobjectid.sql
rem
set  linesize 132
rem
ttitle 'Database Objects'
rem
col owner format a12 heading 'OWNER'
col object_name format a30 heading 'OBJECT NAME'
col subobject_name format a15 heading 'SUBOBJECT NAME'
rem col object_id format 99999 heading 'ID'
col data_object_id format 99999 heading 'DATA|OBJECT ID'
col object_type format a13 heading 'OBJECT TYPE'
col created format a9 heading 'CREATED'
col modified format a9 heading 'MODIFIED'
col status format a1 heading 'S'
rem
break on owner skip 1
rem
select owner, object_name, subobject_name, object_type,
       data_object_id,
       created,
       decode( last_ddl_time, created, null, last_ddl_time ) modified,
       decode( status, 'VALID', null, 'INVALID', 'I', '?' ) status
  from sys.dba_objects
 where data_object_id = &&data_object_id
    or object_id = &data_object_id
 order by owner, object_name;
undefine data_object_id

What This Script Does

This script identifies database objects by their object_id or data_object_id, which is essential for troubleshooting when you encounter object IDs in trace files, error messages, or database internals. It provides comprehensive object information including ownership, type, creation date, modification history, and validity status. The script searches both object_id and data_object_id columns to handle different Oracle internal references.

Key Features

• Dual ID Search: Searches both object_id and data_object_id in single query
• Complete Object Details: Shows owner, name, type, and subobject information
• Partition Support: Displays subobject names for partitioned objects
• Status Indication: Shows validity status with compact notation
• Modification Tracking: Displays creation and last modification dates
• Variable Cleanup: Automatically undefines the input variable

Usage

@dobjectid.sql

Input Parameters:

• data_object_id: The object ID or data object ID to search for

Required Privileges

SELECT on SYS.DBA_OBJECTS

Sample Output

                                            Database Objects

OWNER        OBJECT NAME                    SUBOBJECT NAME  OBJECT TYPE   DATA   CREATED   MODIFIED  S
                                                                        OBJECT ID
------------ ------------------------------ --------------- ------------- ------ --------- --------- -
HR           EMPLOYEES                                      TABLE          12345 15-JAN-20 01-FEB-20
             EMPLOYEES                      EMP_PART_2020   TABLE PARTITION 12346 01-FEB-20
             EMPLOYEES                      EMP_PART_2021   TABLE PARTITION 12347 01-JAN-21
             EMP_SALARY_IDX                                 INDEX          12348 15-JAN-20

Key Output Columns

• OWNER: Schema that owns the object
• OBJECT NAME: Name of the database object
• SUBOBJECT NAME: Name of subobject (partitions, subpartitions)
• OBJECT TYPE: Type of database object (TABLE, INDEX, PROCEDURE, etc.)
• DATA OBJECT ID: Data object ID (for segments)
• CREATED: Date when object was created
• MODIFIED: Date when object was last modified (blank if never modified)
• S: Status indicator (blank=VALID, I=INVALID, ?=other)

Understanding Object IDs

Object ID vs Data Object ID

Object ID

-- OBJECT_ID characteristics:
-- Unique identifier for all database objects
-- Never changes for the object
-- Used in data dictionary references
-- Present for all object types

Data Object ID

-- DATA_OBJECT_ID characteristics:
-- Identifier for objects that store data (segments)
-- Can change during certain operations (e.g., table moves)
-- NULL for objects without storage (views, synonyms)
-- Used in database file headers and blocks

Common Scenarios for Object ID Lookup

Trace Files

-- Common trace file references:
-- "Table/Index access: object_id=12345"
-- "Block corruption in object 12345"
-- "Wait event: obj#=12345"
-- Use this script to identify the object

Error Messages

-- Oracle error messages often include:
-- ORA-00600 with object ID references
-- Block corruption messages
-- Space management errors
-- Performance monitoring output

Common Use Cases

1. Trace File Analysis

   • Identify objects referenced in trace files
   • Correlate object IDs with performance issues
   • Analyze wait events and timing data
   • Troubleshoot database internal operations

2. Error Investigation

   • Resolve object IDs in error messages
   • Investigate corruption reports
   • Analyze space management issues
   • Troubleshoot backup/recovery problems

3. Performance Troubleshooting

   • Identify hot objects in performance reports
   • Correlate object access with wait events
   • Analyze I/O patterns by object
   • Investigate locking and contention issues

4. Database Internals Analysis

   • Understand object storage characteristics
   • Analyze partition and subpartition structure
   • Investigate object relationships
   • Support advanced troubleshooting

Advanced Analysis

Partition and Subpartition Mapping

-- For partitioned objects, the script shows:
-- Main table with its object_id
-- Each partition as a subobject
-- Different data_object_id for each partition
-- Helps understand partition storage layout

Object Status Analysis

-- Status indicators help identify:
-- I = INVALID objects needing recompilation
-- Blank = VALID objects functioning normally
-- ? = Other status (UNUSABLE indexes, etc.)
-- Critical for troubleshooting object issues

Modification History

-- Modified column shows:
-- Blank = Object never modified since creation
-- Date = Last DDL operation performed
-- Helps correlate issues with recent changes
-- Useful for change management analysis

Troubleshooting Applications

Block Corruption Investigation

Corruption Reports

-- When Oracle reports block corruption:
-- "Block corruption in object 12345"
-- Use script to identify affected table/index
-- Plan recovery or repair actions
-- Assess impact on applications

Recovery Planning

-- Once object is identified:
-- Determine object importance
-- Plan recovery strategy
-- Identify dependent objects
-- Coordinate with application teams

Performance Problem Resolution

Hot Object Identification

-- Performance monitoring shows object IDs:
-- High I/O operations on object 12345
-- Locking contention on object 12346
-- Buffer busy waits for object 12347
-- Use script to identify specific objects

Tuning Strategy

-- After identifying objects:
-- Analyze object structure and usage
-- Consider partitioning strategies
-- Review indexing approaches
-- Plan optimization efforts

Space Management Issues

Space Alerts

-- Space management alerts reference object IDs:
-- "Cannot extend object 12345"
-- "Tablespace full for object 12346"
-- Use script to identify affected objects
-- Plan space allocation accordingly

Capacity Planning

-- Space analysis by object:
-- Identify large growing objects
-- Plan tablespace expansion
-- Consider storage optimization
-- Coordinate with capacity planning

Integration with Other Analysis

Cross-Reference with Performance Data

-- Combine with performance scripts:
-- V$SEGMENT_STATISTICS by object_id
-- V$SQL with object references
-- AWR reports with object activity
-- Session wait events by object

Dependency Analysis

-- After identifying objects:
-- Check DBA_DEPENDENCIES for relationships
-- Identify dependent views and procedures
-- Analyze impact of changes
-- Plan coordinated maintenance

Historical Analysis

-- Track object changes over time:
-- Compare creation dates with issues
-- Correlate modifications with problems
-- Analyze object lifecycle patterns
-- Support change management

Advanced Use Cases

Database Forensics

Security Investigation

-- When investigating security issues:
-- Trace unauthorized access to objects
-- Identify objects accessed during incidents
-- Correlate with audit trail data
-- Support security analysis

Change Detection

-- Unauthorized change investigation:
-- Compare modification dates
-- Identify unexpected object changes
-- Correlate with known change windows
-- Support compliance auditing

Migration and Upgrades

Object Inventory

-- During migration planning:
-- Document object IDs and types
-- Identify partitioned objects
-- Plan migration strategies
-- Verify post-migration integrity

Validation

-- Post-migration validation:
-- Verify object IDs are consistent
-- Check partition mapping
-- Validate object status
-- Confirm structural integrity

Best Practices

Documentation

-- When using this script:
-- Document the context (trace file, error)
-- Record the investigation findings
-- Note any corrective actions taken
-- Update incident documentation

Automation

-- Consider automation for:
-- Regular object health checks
-- Performance monitoring correlation
-- Alert notification systems
-- Automated troubleshooting workflows

Integration

-- Use with other diagnostic tools:
-- Database health check scripts
-- Performance monitoring systems
-- Error log analysis tools
-- Capacity planning utilities

Related Scripts

• dobject.sql - General object listing and analysis
• dobjectd.sql - Objects by creation date
• dinvalid.sql - Invalid objects analysis
• dxref.sql - Object dependency analysis

Troubleshooting Tips

Object Not Found

-- If no results returned:
-- Verify the object ID is correct
-- Check if object was dropped
-- Consider case sensitivity
-- Try related object IDs

Multiple Objects

-- If multiple objects found:
-- Review subobject relationships
-- Check partition hierarchy
-- Analyze object types
-- Consider object dependencies

Invalid Objects

-- For invalid objects (Status = I):
-- Check compilation errors
-- Review dependencies
-- Plan recompilation
-- Consider cascade effects

Summary

This script is essential for:

1. Trace File Analysis - Identifying objects referenced in traces
2. Error Investigation - Resolving object IDs in error messages
3. Performance Troubleshooting - Correlating object access with performance
4. Database Internals - Understanding object storage and relationships
5. Incident Response - Quick object identification during problems