Recently Created and Modified Database Objects (dobjnew.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 dobjnew.sql script.

The Script

rem  dobjnew.sql
rem
set  linesize 132
rem
ttitle 'New Database Objects'
rem
col owner format a12 heading 'OWNER'
col object_name format a30 heading 'OBJECT NAME'
col subobject_name format a25 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 a14 heading 'CREATED'
col modified format a14 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,
       to_char(created, 'mm/dd/yy hh24:mi') created,
       to_char(decode( last_ddl_time, created, to_date(null), last_ddl_time ),'mm/dd/yy hh24:mi') modified,
       decode( status, 'VALID', null, 'INVALID', 'I', '?' ) status
  from sys.dba_objects
 where owner like nvl(upper('&owner'), '%')
   and object_type like nvl(upper('&type'), '%')
   and object_name like nvl(upper('&object'), '%')
   and (subobject_name like nvl(upper('&subobject'), '%')
        or subobject_name is null)
   and last_ddl_time >= nvl(to_date('&dateval'),trunc(sysdate))
 order by owner, last_ddl_time desc, object_name;

What This Script Does

This script identifies database objects that have been created or modified since a specified date, providing detailed timestamps and status information. It’s essential for tracking recent development activity, verifying deployments, monitoring unauthorized changes, and understanding database evolution patterns. The script offers flexible filtering by owner, object type, name patterns, and date ranges.

Key Features

• Flexible Date Filtering: Search from specific date or default to today
• Multiple Filter Options: Filter by owner, type, name, and subobject
• Detailed Timestamps: Shows both creation and modification times with precision
• Partition Support: Includes subobject information for partitioned objects
• Status Indication: Displays object validity status
• Chronological Ordering: Results sorted by modification time (newest first)

Usage

@dobjnew.sql

Input Parameters:

• Owner: Schema owner name or pattern (press Enter for all)
• Type: Object type or pattern (press Enter for all)
• Object: Object name or pattern (press Enter for all)
• Subobject: Subobject name or pattern (press Enter for all)
• Dateval: Starting date for search (MM/DD/YY format, press Enter for today)

Required Privileges

SELECT on SYS.DBA_OBJECTS

Sample Output

                                          New Database Objects

OWNER        OBJECT NAME                    SUBOBJECT NAME           OBJECT TYPE   CREATED        MODIFIED       S
------------ ------------------------------ ------------------------ ------------- -------------- -------------- -
HR           SALARY_CALCULATION             PACKAGE BODY  11/15/24 14:30 11/15/24 16:45
             EMPLOYEE_LOOKUP                                         PROCEDURE     11/15/24 14:25 11/15/24 14:30
             EMPLOYEES                      EMP_2024_Q4              TABLE PARTITION 11/15/24 10:15
             DEPT_SUMMARY                                           VIEW          11/15/24 09:30

SALES        ORDER_PROCESSING                                       PACKAGE       11/15/24 16:00 11/15/24 16:30
             ORDER_PROCESSING                                       PACKAGE BODY  11/15/24 16:05 11/15/24 16:30
             DAILY_SALES_VW                                         VIEW          11/15/24 15:45
             CUSTOMER_IDX_NEW                                       INDEX         11/15/24 13:20               I

FINANCE      MONTH_END_PROC                                         PROCEDURE     11/15/24 11:45 11/15/24 12:15
             GL_TEMP_TABLE                                          TABLE         11/15/24 11:30
             BUDGET_CONSTRAINT                                      CHECK         11/15/24 11:35

Key Output Columns

• OWNER: Schema that owns the object
• OBJECT NAME: Name of the database object
• SUBOBJECT NAME: Name of subobject (partitions, subpartitions, constraints)
• OBJECT TYPE: Type of database object
• CREATED: Date and time when object was created (MM/DD/YY HH24:MI)
• MODIFIED: Date and time when object was last modified (blank if never modified)
• S: Status indicator (blank=VALID, I=INVALID, ?=other)

Understanding Recent Object Changes

Creation vs Modification

New Objects (CREATED only)

-- Objects with CREATED but no MODIFIED indicate:
-- Recently created objects
-- Never altered since creation
-- Fresh deployments
-- New development artifacts

Modified Objects (both CREATED and MODIFIED)

-- Objects with different CREATED and MODIFIED times:
-- Objects altered after creation
-- Bug fixes or enhancements
-- Recompilation due to dependencies
-- Structural changes

Object Types and Patterns

Application Objects

-- Common application objects:
-- PACKAGE/PACKAGE BODY: Business logic changes
-- PROCEDURE/FUNCTION: Code modifications
-- VIEW: Query logic updates
-- TRIGGER: Business rule changes

Schema Objects

-- Database structure objects:
-- TABLE: New tables or alterations
-- INDEX: Performance optimization
-- CONSTRAINT: Data integrity rules
-- SEQUENCE: Auto-numbering objects

Common Use Cases

1. Deployment Verification

   • Verify successful deployment of changes
   • Confirm all expected objects were created/modified
   • Identify any missing deployment artifacts
   • Validate deployment timing

2. Change Management

   • Track development activity over time
   • Monitor unauthorized production changes
   • Document change frequency by developer
   • Support change control processes

3. Development Monitoring

   • Monitor team development activity
   • Track feature development progress
   • Identify development hotspots
   • Support project management

4. Security and Compliance

   • Detect unauthorized schema changes
   • Monitor sensitive object modifications
   • Support security auditing
   • Maintain compliance documentation

Advanced Analysis

Development Activity Tracking

-- Track activity patterns:
-- High volume changes = active development
-- Weekend/evening changes = emergency fixes
-- Pattern changes = project milestones
-- Object type distribution = development focus

Change Impact Assessment

-- Assess change impact:
-- Package body changes = logic updates
-- Table alterations = schema evolution
-- Index changes = performance tuning
-- View modifications = reporting updates

Deployment Pattern Analysis

-- Analyze deployment patterns:
-- Coordinated timestamps = planned deployment
-- Scattered times = ad-hoc changes
-- Multiple object types = comprehensive release
-- Single object focus = targeted fix

Filtering Examples

Recent Development Activity

-- Check today's activity:
Enter value for owner: %
Enter value for type: %
Enter value for object: %
Enter value for subobject: %
Enter value for dateval: [Press Enter for today]

Specific Schema Changes

-- Monitor HR schema changes since last week:
Enter value for owner: HR
Enter value for type: %
Enter value for object: %
Enter value for subobject: %
Enter value for dateval: 11/08/24

Package Modifications

-- Find recent package changes:
Enter value for owner: %
Enter value for type: PACKAGE%
Enter value for object: %
Enter value for subobject: %
Enter value for dateval: 11/01/24

Partition Activity

-- Monitor partition changes:
Enter value for owner: SALES
Enter value for type: TABLE PARTITION
Enter value for object: %
Enter value for subobject: %
Enter value for dateval: 11/01/24

Troubleshooting Applications

Post-Deployment Issues

Missing Objects

-- If expected objects don't appear:
-- Check date range (deployment may be outside range)
-- Verify owner and object name patterns
-- Check for deployment failures
-- Review deployment logs

Invalid Objects

-- Objects with Status = 'I':
-- Check compilation errors with DBA_ERRORS
-- Review object dependencies
-- Plan recompilation sequence
-- Identify cascade compilation needs

Change Detection

Unauthorized Changes

-- Unexpected objects in results:
-- Verify change authorization
-- Check change management records
-- Identify change source
-- Assess security implications

Development Conflicts

-- Overlapping development activity:
-- Multiple modifications to same object
-- Conflicting change patterns
-- Coordinate development teams
-- Plan merge strategies

Integration with Change Management

Pre-Deployment Baseline

-- Before deployments:
-- Run script to capture current state
-- Document expected changes
-- Save baseline for comparison
-- Plan deployment verification

Post-Deployment Verification

-- After deployments:
-- Compare results with deployment manifest
-- Verify all objects were created/modified
-- Check for compilation errors
-- Document any discrepancies

Continuous Monitoring

-- Regular monitoring schedule:
-- Daily: Check for unauthorized changes
-- Weekly: Review development patterns
-- Monthly: Analyze change trends
-- Quarterly: Update change procedures

Performance Considerations

Large Databases

-- For large databases:
-- Use specific date ranges to limit results
-- Filter by owner to reduce scan time
-- Consider running during low-activity periods
-- Monitor query execution time

Historical Analysis

-- For historical analysis:
-- Use broader date ranges carefully
-- Consider impact on system performance
-- Run during maintenance windows
-- Save results for trend analysis

Automation and Alerting

Daily Change Monitoring

-- Automate daily monitoring:
-- Schedule script execution
-- Email results to administrators
-- Alert on unexpected changes
-- Log results for historical tracking

Security Monitoring

-- Monitor for security concerns:
-- After-hours object creation
-- Changes by unauthorized users
-- Sensitive object modifications
-- Unusual change patterns

Related Scripts

• dobjcre.sql - Objects grouped by creation date
• dobjmod.sql - Objects grouped by modification date
• dobject.sql - General object listing
• dinvalid.sql - Invalid objects analysis

Best Practices

Regular Monitoring

-- Establish monitoring routine:
-- Daily checks for production changes
-- Weekly development activity reviews
-- Monthly change pattern analysis
-- Document normal vs. abnormal patterns

Documentation

-- Maintain change documentation:
-- Record deployment activities
-- Document emergency changes
-- Track development milestones
-- Support audit requirements

Security

-- Security considerations:
-- Monitor for unauthorized changes
-- Alert on sensitive object modifications
-- Track privileged user activity
-- Maintain access control documentation

Summary

This script is essential for:

1. Deployment Verification - Confirming successful deployments
2. Change Management - Tracking and monitoring database changes
3. Development Monitoring - Understanding development activity patterns
4. Security Auditing - Detecting unauthorized modifications
5. Compliance Support - Maintaining change documentation and audit trails