Modified Objects Tracking (dobjectd.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 tracks database object modifications by:

• Filtering objects modified within a specific date range
• Distinguishing between object creation and subsequent modifications
• Supporting pattern matching for owner, object type, and object name
• Showing object status to identify invalid objects
• Organizing output by owner for easy review
• Providing precise timestamps for change tracking

Script

rem  dobjectd.sql
rem
rem
set lines 1332
rem
ttitle 'Database Objects - Modified'
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,
       to_char(decode( last_ddl_time, created, to_date(null), 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')
   and last_ddl_time between to_date('&ddl_start')
                         and to_date('&ddl_end') + 1
 order by owner, object_name;

Usage

SQL> @dobjectd.sql
Enter value for owner: SCOTT
Enter value for type: %
Enter value for object: %
Enter value for ddl_start: 01-JAN-2025
Enter value for ddl_end: 31-JAN-2025

Parameters

• owner: Object owner pattern (% for all, specific name for single owner)
• type: Object type pattern (TABLE, INDEX, PROCEDURE, etc., % for all)
• object: Object name pattern (% for all, specific pattern for filtering)
• ddl_start: Start date for modification period (DD-MON-YYYY format)
• ddl_end: End date for modification period (DD-MON-YYYY format)

Required Privileges

• SELECT on SYS.DBA_OBJECTS

Sample Output

Database Objects - Modified

OWNER        OBJECT NAME                    OBJECT TYPE   CREATED           MODIFIED          S
------------ ------------------------------ ------------- ----------------- ----------------- -
SCOTT        BONUS                          TABLE         15/MAR/25 09:15:23
             CUSTOMERS                      TABLE         10/JAN/25 14:30:45 25/JAN/25 16:45:12
             CUSTOMER_IDX                   INDEX         10/JAN/25 14:31:02 25/JAN/25 16:45:15
             UPDATE_SALARY                  PROCEDURE     20/JAN/25 11:20:30 28/JAN/25 09:10:45

HR           EMPLOYEES                      TABLE         05/JAN/25 08:00:00 15/JAN/25 13:22:10
             EMP_TRIGGER                    TRIGGER       05/JAN/25 08:05:30 15/JAN/25 13:22:45
             PAYROLL_PKG                    PACKAGE       12/JAN/25 16:45:00 20/JAN/25 14:30:15
             PAYROLL_PKG                    PACKAGE BODY  12/JAN/25 16:45:15 20/JAN/25 14:30:20  I

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: When the object was first created
• MODIFIED: When the object was last modified (blank if never modified)
• S: Status indicator (I = Invalid, blank = Valid)

Understanding the Results

Modification vs Creation

• CREATED only: Object created within the date range but never modified
• CREATED + MODIFIED: Object modified after initial creation
• MODIFIED date later: Shows when changes were made

Object Status Indicators

• Blank: Object is valid and ready for use
• I: Object is invalid (compilation errors, dependencies broken)
• ?: Unknown status (rare)

Common Use Cases

1. Change Management

   • Track development changes
   • Audit production modifications
   • Document release deployments

2. Compliance Auditing

   • Monitor unauthorized changes
   • Track privileged operations
   • Support regulatory requirements

3. Impact Analysis

   • Identify recently changed objects
   • Correlate changes with issues
   • Plan rollback strategies

Filtering Strategies

Recent Changes

-- Objects modified in last 7 days
Enter value for ddl_start: 24-JAN-2025
Enter value for ddl_end: 31-JAN-2025

Specific Object Types

-- Only track table changes
Enter value for type: TABLE

-- Monitor procedural code changes
Enter value for type: %PROCEDURE%

Application-Specific

-- Monitor specific application schema
Enter value for owner: ORDERAPP
Enter value for type: %
Enter value for object: %

Change Analysis Techniques

Daily Change Summary

-- Summarize changes by day
SELECT TO_CHAR(last_ddl_time, 'YYYY-MM-DD') change_date,
       object_type,
       COUNT(*) object_count
FROM dba_objects
WHERE last_ddl_time >= TRUNC(SYSDATE) - 7
GROUP BY TO_CHAR(last_ddl_time, 'YYYY-MM-DD'), object_type
ORDER BY change_date DESC, object_count DESC;

Owner Activity Analysis

-- Find most active schemas
SELECT owner,
       COUNT(*) total_changes,
       COUNT(DISTINCT object_type) object_types,
       MIN(last_ddl_time) first_change,
       MAX(last_ddl_time) last_change
FROM dba_objects
WHERE last_ddl_time >= SYSDATE - 30
GROUP BY owner
ORDER BY total_changes DESC;

Object Type Distribution

-- Analyze change patterns by object type
SELECT object_type,
       COUNT(*) change_count,
       COUNT(DISTINCT owner) affected_schemas,
       ROUND(AVG(SYSDATE - last_ddl_time), 1) avg_days_ago
FROM dba_objects
WHERE last_ddl_time >= SYSDATE - 30
GROUP BY object_type
ORDER BY change_count DESC;

Invalid Object Analysis

Find Invalid Objects

-- Focus on invalid objects from changes
SELECT owner, object_name, object_type,
       last_ddl_time, status
FROM dba_objects
WHERE status = 'INVALID'
AND last_ddl_time >= SYSDATE - 7
ORDER BY last_ddl_time DESC;

Dependency Impact

-- Find objects invalidated by changes
SELECT o1.owner, o1.object_name, o1.object_type,
       o2.owner referenced_owner,
       o2.object_name referenced_object,
       o2.object_type referenced_type
FROM dba_dependencies d,
     dba_objects o1,
     dba_objects o2
WHERE d.owner = o1.owner
AND d.name = o1.object_name
AND d.type = o1.object_type
AND d.referenced_owner = o2.owner
AND d.referenced_name = o2.object_name
AND d.referenced_type = o2.object_type
AND o1.status = 'INVALID'
AND o2.last_ddl_time >= SYSDATE - 7;

Change Tracking Integration

Create Change Log

-- Create table to track changes
CREATE TABLE change_audit_log (
    log_date DATE DEFAULT SYSDATE,
    owner VARCHAR2(128),
    object_name VARCHAR2(128),
    object_type VARCHAR2(23),
    change_type VARCHAR2(10), -- CREATE, MODIFY
    ddl_time DATE,
    status VARCHAR2(7)
);

-- Populate with current snapshot
INSERT INTO change_audit_log
    (owner, object_name, object_type, change_type, ddl_time, status)
SELECT owner, object_name, object_type,
       CASE WHEN created = last_ddl_time THEN 'CREATE' ELSE 'MODIFY' END,
       last_ddl_time, status
FROM dba_objects
WHERE last_ddl_time >= SYSDATE - 1;

Automated Monitoring

-- Find new changes since last check
SELECT o.owner, o.object_name, o.object_type,
       o.last_ddl_time, o.status
FROM dba_objects o
WHERE o.last_ddl_time > (
    SELECT NVL(MAX(ddl_time), SYSDATE - 365)
    FROM change_audit_log
)
ORDER BY o.last_ddl_time DESC;

Development Workflow Tracking

Release Deployment Verification

-- Verify expected objects were deployed
WITH expected_changes AS (
    SELECT 'SCOTT' owner, 'CUSTOMERS' object_name, 'TABLE' object_type FROM dual UNION ALL
    SELECT 'SCOTT', 'UPDATE_SALARY', 'PROCEDURE' FROM dual UNION ALL
    SELECT 'HR', 'PAYROLL_PKG', 'PACKAGE' FROM dual
)
SELECT e.owner, e.object_name, e.object_type,
       CASE WHEN o.object_name IS NOT NULL THEN 'DEPLOYED' ELSE 'MISSING' END status,
       o.last_ddl_time
FROM expected_changes e
LEFT JOIN dba_objects o ON e.owner = o.owner
                        AND e.object_name = o.object_name
                        AND e.object_type = o.object_type
                        AND o.last_ddl_time >= DATE '2025-01-28';

Rollback Planning

-- Identify objects for potential rollback
SELECT owner, object_name, object_type,
       created, last_ddl_time,
       CASE
         WHEN created = last_ddl_time THEN 'NEW OBJECT - DROP'
         ELSE 'MODIFIED OBJECT - RESTORE'
       END rollback_action
FROM dba_objects
WHERE last_ddl_time BETWEEN DATE '2025-01-28' AND DATE '2025-01-29'
ORDER BY rollback_action, owner, object_name;

Security and Compliance

Privileged User Activity

-- Track changes by privileged users
SELECT o.owner, COUNT(*) change_count,
       LISTAGG(DISTINCT o.object_type, ', ') object_types
FROM dba_objects o
WHERE o.last_ddl_time >= SYSDATE - 30
AND o.owner IN ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
GROUP BY o.owner
ORDER BY change_count DESC;

Off-Hours Changes

-- Identify changes made outside business hours
SELECT owner, object_name, object_type,
       TO_CHAR(last_ddl_time, 'DY HH24:MI:SS') change_time,
       last_ddl_time
FROM dba_objects
WHERE last_ddl_time >= SYSDATE - 7
AND (TO_NUMBER(TO_CHAR(last_ddl_time, 'HH24')) < 8
     OR TO_NUMBER(TO_CHAR(last_ddl_time, 'HH24')) > 18
     OR TO_CHAR(last_ddl_time, 'DY') IN ('SAT', 'SUN'))
ORDER BY last_ddl_time DESC;

Performance Considerations

1. Date Range Limitations

   • Use specific date ranges for better performance
   • Avoid very large time spans
   • Consider indexing on LAST_DDL_TIME

2. Pattern Matching

   • Use specific object names when possible
   • Limit owner patterns to necessary schemas
   • Consider object type filtering

Best Practices

1. Regular Monitoring

   • Run daily reports for recent changes
   • Establish baseline change patterns
   • Alert on unexpected modifications

2. Change Documentation

   • Correlate changes with change tickets
   • Document deployment windows
   • Track rollback procedures

3. Security Monitoring

   • Monitor privileged schema changes
   • Track off-hours modifications
   • Review unexpected object creations

Troubleshooting

Missing Expected Changes

• Verify date range includes deployment time
• Check if objects were created in different schema
• Confirm DDL operations completed successfully

Invalid Objects After Changes

• Identify dependency chains
• Plan compilation order
• Check for missing privileges or objects

Related Scripts

• Database Objects Analysis
• Object Creation Summary
• Program Compilation Errors
• Object Dependencies