Database Objects with Full Timestamp Details (dobjectf.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 dobjectf.sql script.

The Script

rem  dobjectf.sql
rem
rem  linesize = 95
rem
set linesize 132
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 created format a17 heading 'CREATED'
col modified format a17 heading 'MODIFIED'
col status format a1 heading 'S'
rem
break on owner skip 1
rem
select owner, object_name, object_type,
       to_char( created, 'mm/dd/yy hh24:mi:ss' ) created,
       decode( last_ddl_time, created, null,
               to_char( last_ddl_time, 'mm/dd/yy 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')
 order by owner, object_name;
rem
set linesize 80

What This Script Does

This enhanced version of the database objects script provides comprehensive object information including creation and modification timestamps. It’s essential for change tracking, deployment verification, and understanding object lifecycle management across database schemas.

Key Features

• Full Timestamp Details: Shows both creation and modification dates with time
• Change Detection: Highlights objects that have been modified since creation
• Status Monitoring: Displays object validity status
• Flexible Filtering: Filter by owner, object type, and object name patterns
• Audit Trail: Helps track when objects were created or last modified

Usage

Run the script and provide filter criteria when prompted:

@dobjectf.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

SELECT on SYS.DBA_OBJECTS

Sample Output

                                            Database Objects

OWNER        OBJECT NAME                    OBJECT TYPE   CREATED           MODIFIED          S
------------ ------------------------------ ------------- ----------------- ----------------- -
SCOTT        DEPT                           TABLE         03/15/24 09:30:15
             EMP                            TABLE         03/15/24 09:30:15 03/20/24 14:22:33
             EMP_NAME_IDX                   INDEX         03/15/24 10:15:22
             GET_EMP_PROC                   PROCEDURE     03/18/24 11:45:30 03/25/24 16:10:45 I
             PK_DEPT                        INDEX         03/15/24 09:30:16
             PK_EMP                         INDEX         03/15/24 09:30:16

HR           COUNTRIES                      TABLE         02/01/24 08:00:00
             DEPARTMENTS                    TABLE         02/01/24 08:00:01
             EMPLOYEES                      TABLE         02/01/24 08:00:02 03/22/24 10:30:15
             GET_EMPLOYEE_PROC              PROCEDURE     02/15/24 14:20:30 03/26/24 09:15:20 I
             JOBS                           TABLE         02/01/24 08:00:03
             SALARY_HISTORY                 TABLE         03/01/24 16:45:00
             UPDATE_SALARY_FUNC             FUNCTION      02/20/24 11:30:45 03/24/24 13:45:22

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.)
• CREATED: Date and time when object was first created
• MODIFIED: Date and time when object was last modified (blank if never modified)
• S (Status): Object status indicator
  • Blank: Object is VALID
  • I: Object is INVALID

Understanding Timestamps

Creation Date (CREATED)

• When Object Was First Created: Initial creation timestamp
• Format: MM/DD/YY HH24:MI:SS
• Always Populated: Every object has a creation date

Modification Date (MODIFIED)

• When Object Was Last Changed: Last DDL operation timestamp
• Blank if Unchanged: Only shows if object was modified after creation
• DDL Operations Include: ALTER, COMPILE, GRANT, etc.

Time Analysis

• Same Create/Modify: Object hasn’t been changed since creation
• Different Timestamps: Object has been modified
• Recent Modifications: Recent changes for deployment tracking

Common Use Cases

1. Deployment Verification

   • Verify objects were created during deployment windows
   • Confirm modification timestamps match expected changes

2. Change Tracking

   • Identify recently modified objects
   • Track object changes over time

3. Audit and Compliance

   • Document when objects were created/modified
   • Support change management processes

4. Troubleshooting

   • Correlate object changes with performance issues
   • Identify when objects became invalid

5. Migration Planning

   • Understand object modification history
   • Plan migration sequences based on dependencies

Analysis Examples

Find Recently Modified Objects

Look for objects with MODIFIED column populated and recent dates:

-- Objects modified in last 7 days
Owner: %
Type: %
Object: %

Then filter results by MODIFIED column

Identify Unchanged Objects

Objects where MODIFIED column is blank have never been altered since creation.

Track Invalid Objects

Objects with ‘I’ status that have recent modification dates may indicate compilation issues.

Deployment Analysis

Before/After Comparison

1. Run Before Deployment

   @dobjectf.sql
   Owner: MYAPP
   Type: %
   Object: %

2. Run After Deployment

   • Compare MODIFIED timestamps
   • Verify expected objects were changed
   • Identify unexpected modifications

Change Windows

• Planned Changes: Should see MODIFIED timestamps during maintenance windows
• Unplanned Changes: MODIFIED timestamps outside maintenance windows need investigation

Troubleshooting Object Issues

Invalid Objects After Changes

-- Focus on invalid objects with recent modifications
Owner: %
Type: PROCEDURE,FUNCTION,PACKAGE
Object: %

Look for ‘I’ status with recent MODIFIED dates

Missing Expected Changes

• No MODIFIED Date: Object wasn’t actually changed
• Wrong MODIFIED Date: Change may have occurred at unexpected time
• Missing Objects: May not have been created yet

Performance Optimization

Filtering Strategies

1. Start Specific: Use specific owner first
2. Object Type Filter: Filter by object type to reduce result set
3. Name Patterns: Use specific name patterns when possible

Large Database Considerations

• Consider time-based filtering for large databases
• Use specific schema names rather than wildcards
• Focus on specific object types for targeted analysis

Related Scripts

• dobj.sql - Basic object listing without timestamps
• dobjecti.sql - Invalid objects only
• dinvalid.sql - Detailed invalid object analysis
• compile_invalid.gen - Generate recompilation scripts