Object Summary by Modification Date (dobjmod.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 dobjmod.sql script.

The Script

rem  dobjmod.sql
rem
ttitle 'Object Summary by Modification Date'
rem
col owner format a12 heading 'OWNER'
col last_ddl_time format a9 heading 'MODIFIED|DATE'
col object_type format a13 heading 'OBJECT TYPE'
col object_count format 99999 heading 'OBJECT|TYPE'
rem
break on owner skip 1
rem
select owner,
       trunc(last_ddl_time) last_ddl_time,
       object_type,
       count(*) object_count
  from sys.dba_objects
 where owner like upper('&owner')
 group by owner, trunc(last_ddl_time), object_type;

What This Script Does

This script provides a chronological summary of database object modifications, grouping objects by owner, last DDL modification date, and object type. Unlike creation date tracking, this script shows when objects were last changed through any DDL operation (ALTER, GRANT, COMPILE, etc.). It’s invaluable for monitoring recent schema changes, tracking development activity, and ensuring change control compliance.

Key Features

• Modification Tracking: Shows when objects were last modified via DDL
• Activity Monitoring: Identifies recent development and maintenance activity
• Type Classification: Groups modifications by object type
• Change Patterns: Reveals deployment and maintenance patterns
• Compliance Support: Provides modification audit trail

Usage

@dobjmod.sql

Input Parameters:

• Owner: Schema owner name or pattern (use % for all owners)

Required Privileges

SELECT on SYS.DBA_OBJECTS

Sample Output

                                 Object Summary by Modification Date

OWNER        MODIFIED  OBJECT TYPE   OBJECT
             DATE                     TYPE
------------ --------- ------------- ------
HR           01-NOV-24 TABLE            3
             01-NOV-24 INDEX            5
             01-NOV-24 TRIGGER          2

             15-NOV-24 PROCEDURE        8
             15-NOV-24 FUNCTION         4
             15-NOV-24 PACKAGE BODY    12

             20-NOV-24 VIEW             5
             20-NOV-24 SYNONYM          3

             25-NOV-24 PACKAGE          2
             25-NOV-24 PACKAGE BODY     2
             25-NOV-24 TYPE             1

SALES        05-NOV-24 TABLE            2
             05-NOV-24 INDEX            4

             18-NOV-24 PROCEDURE       15
             18-NOV-24 FUNCTION         7
             18-NOV-24 PACKAGE BODY     9

             22-NOV-24 MATERIALIZED VIEW 3
             22-NOV-24 INDEX            6

Key Output Columns

• OWNER: Schema that owns the modified objects
• MODIFIED DATE: Date of last DDL operation (time truncated)
• OBJECT TYPE: Type of database object modified
• OBJECT TYPE: Count of objects of that type modified on that date

Understanding Modification Patterns

Types of Modifications

DDL Operations That Update LAST_DDL_TIME

-- Operations that change modification date:
-- ALTER TABLE/INDEX/VIEW operations
-- GRANT/REVOKE on objects
-- COMPILE operations
-- ANALYZE operations (older versions)
-- Any structural changes

Common Modification Scenarios

-- Typical modification patterns:
-- Package body recompilation (frequent)
-- Index rebuilds (maintenance)
-- Table alterations (schema changes)
-- View modifications (logic changes)
-- Privilege grants (security updates)

Activity Pattern Analysis

Development Activity

-- High modification counts indicate:
-- Active development periods
-- Bug fix deployments
-- Feature enhancements
-- Performance tuning efforts

Maintenance Windows

-- Regular modification patterns:
-- Weekend maintenance activities
-- Monthly statistics gathering
-- Quarterly recompilations
-- Annual partition maintenance

Common Use Cases

1. Recent Change Tracking

   • Monitor schema modifications
   • Track development activity
   • Identify unauthorized changes
   • Verify deployment success

2. Troubleshooting Support

   • Find recently modified objects
   • Correlate changes with issues
   • Identify potential problem sources
   • Support root cause analysis

3. Change Management

   • Validate scheduled changes
   • Detect unscheduled modifications
   • Track modification patterns
   • Support change documentation

4. Development Monitoring

   • Track developer activity
   • Monitor code deployment
   • Identify hot spots of change
   • Measure development velocity

Advanced Analysis

Recent Modifications

-- Find objects modified in last 7 days:
SELECT owner,
       TRUNC(last_ddl_time) modified_date,
       object_type,
       object_name
FROM dba_objects
WHERE last_ddl_time > SYSDATE - 7
  AND owner NOT IN ('SYS','SYSTEM')
ORDER BY last_ddl_time DESC;

Modification Frequency

-- Identify frequently modified objects:
SELECT owner, object_name, object_type,
       created,
       last_ddl_time,
       ROUND(last_ddl_time - created) days_since_creation
FROM dba_objects
WHERE owner LIKE upper('&owner')
  AND last_ddl_time > created + 1  -- Modified after creation
ORDER BY last_ddl_time DESC;

Modification Heatmap

-- Create modification activity heatmap:
SELECT
  TO_CHAR(TRUNC(last_ddl_time),'Day') day_of_week,
  TO_CHAR(TRUNC(last_ddl_time),'HH24') hour_of_day,
  COUNT(*) modifications
FROM dba_objects
WHERE last_ddl_time > SYSDATE - 30
  AND owner NOT IN ('SYS','SYSTEM')
GROUP BY
  TO_CHAR(TRUNC(last_ddl_time),'Day'),
  TO_CHAR(TRUNC(last_ddl_time),'HH24')
ORDER BY 1, 2;

Change Detection Strategies

Baseline Comparison

Creating Baselines

-- Capture current state:
CREATE TABLE object_baseline AS
SELECT owner, object_name, object_type,
       last_ddl_time, status
FROM dba_objects
WHERE owner NOT IN ('SYS','SYSTEM');

-- Compare later:
SELECT curr.owner, curr.object_name, curr.object_type,
       base.last_ddl_time baseline_ddl,
       curr.last_ddl_time current_ddl
FROM dba_objects curr
JOIN object_baseline base
  ON curr.owner = base.owner
  AND curr.object_name = base.object_name
  AND curr.object_type = base.object_type
WHERE curr.last_ddl_time > base.last_ddl_time;

Change Categories

-- Classify modifications:
-- Same day: Likely part of same deployment
-- Recent (1-7 days): Current development
-- Older (7-30 days): Previous release
-- Ancient (>30 days): Stable objects

Unauthorized Change Detection

Monitoring Strategy

-- Key indicators of unauthorized changes:
-- Modifications outside change windows
-- Changes by non-standard users
-- Modifications without change tickets
-- Unexpected object types modified

Alert Criteria

-- Set up monitoring for:
-- Production modifications during business hours
-- Changes by non-deployment accounts
-- Modifications to critical objects
-- Structural changes to tables

Integration with Development Processes

Deployment Verification

Pre-Deployment Snapshot

-- Before deployment:
-- Record current modification dates
-- Note objects expected to change
-- Document baseline state
-- Save for comparison

Post-Deployment Validation

-- After deployment:
-- Verify expected objects were modified
-- Check for unexpected modifications
-- Confirm modification dates align
-- Document any discrepancies

Development Activity Tracking

Sprint Analysis

-- Track development velocity:
SELECT
  TO_CHAR(TRUNC(last_ddl_time),'YYYY-MM') month,
  object_type,
  COUNT(*) modifications
FROM dba_objects
WHERE owner IN ('DEV1','DEV2','DEV3')
  AND last_ddl_time > ADD_MONTHS(SYSDATE,-3)
GROUP BY
  TO_CHAR(TRUNC(last_ddl_time),'YYYY-MM'),
  object_type
ORDER BY 1, 2;

Code Review Support

-- Identify objects for review:
-- Recently modified procedures/packages
-- Changed by specific developers
-- Modified multiple times recently
-- Critical business logic changes

Performance and Maintenance Patterns

Recompilation Tracking

Invalid Object Management

-- Track recompilation efforts:
-- Package bodies frequently recompiled
-- Dependencies causing invalidations
-- Compilation success patterns
-- Maintenance window activities

Statistics Gathering Impact

-- Monitor statistics-related modifications:
-- Tables analyzed (older versions)
-- Index statistics updates
-- Histogram creation effects
-- Statistics locking/unlocking

Maintenance Window Analysis

Typical Maintenance Patterns

-- Common maintenance modifications:
-- Index rebuilds (ALTER INDEX REBUILD)
-- Partition maintenance (ADD/DROP)
-- Statistics gathering
-- Object recompilation

Maintenance Verification

-- Confirm maintenance completion:
-- Expected objects modified
-- Modification times within window
-- No failed operations
-- All invalid objects recompiled

Troubleshooting with Modification Data

Performance Problem Correlation

Recent Change Analysis

-- When performance degrades:
-- Check for recent object modifications
-- Identify changed execution plans
-- Find modified procedures/functions
-- Correlate with problem timeframe

Change Impact Assessment

-- Evaluate modification impact:
-- Which objects were modified
-- What types of changes
-- When changes occurred
-- Who made the changes

Root Cause Analysis

Investigation Steps

-- For production issues:
1. Identify problem timeframe
2. Find objects modified before issue
3. Review modification types
4. Check for related objects
5. Analyze change patterns

Common Problem Patterns

-- Modification-related issues:
-- Package body changes affecting logic
-- Index modifications changing plans
-- View alterations breaking queries
-- Privilege changes causing failures

Best Practices

Regular Monitoring

Daily Checks

-- Monitor critical environments:
-- Production modifications
-- Unexpected change patterns
-- After-hours activity
-- Weekend modifications

Weekly Reviews

-- Comprehensive analysis:
-- All schema modifications
-- Development activity summary
-- Maintenance completion
-- Trend analysis

Documentation Requirements

Change Documentation

-- Record for each modification:
-- Object name and type
-- Modification date/time
-- Reason for change
-- Associated change ticket

Audit Trail Maintenance

-- Maintain modification history:
-- Regular baseline snapshots
-- Archive modification reports
-- Track long-term patterns
-- Support compliance audits

Related Scripts

• dobjcre.sql - Objects by creation date
• dobject.sql - Comprehensive object listing
• dinvalid.sql - Invalid objects analysis
• compile_invalid.gen - Generate recompilation statements

Summary

This script is essential for:

1. Change Management - Tracking all DDL modifications
2. Development Monitoring - Understanding development patterns
3. Troubleshooting - Correlating changes with issues
4. Compliance - Maintaining modification audit trails
5. Maintenance Verification - Confirming maintenance completion