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

The Script

rem  dobjcre.sql
rem
ttitle 'Object Summary by Creation Date'
rem
col owner format a12 heading 'OWNER'
col created format a9 heading 'CREATION|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(created) created,
       object_type,
       count(*) object_count
  from sys.dba_objects
 where owner like upper('&owner')
 group by owner, trunc(created), object_type;

What This Script Does

This script provides a chronological summary of database object creation, grouping objects by owner, creation date, and object type. It’s invaluable for understanding when database objects were deployed, tracking schema evolution over time, and auditing object creation patterns. The script shows a count of each object type created on each date, making it easy to identify deployment events and schema changes.

Key Features

• Chronological View: Groups objects by creation date for historical analysis
• Type Breakdown: Shows object counts by type (TABLE, INDEX, PROCEDURE, etc.)
• Owner-Based Organization: Groups results by schema owner
• Deployment Tracking: Identifies when batches of objects were created
• Audit Trail: Provides creation history for compliance and documentation

Usage

@dobjcre.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 Creation Date

OWNER        CREATION  OBJECT TYPE   OBJECT
             DATE                     TYPE
------------ --------- ------------- ------
HR           15-JAN-20 TABLE            7
             15-JAN-20 INDEX           11
             15-JAN-20 SEQUENCE         3
             15-JAN-20 TRIGGER          2

             22-JAN-20 PROCEDURE        5
             22-JAN-20 FUNCTION         3

             01-FEB-20 TABLE            2
             01-FEB-20 INDEX            4
             01-FEB-20 VIEW             3

             15-MAR-20 PACKAGE          6
             15-MAR-20 PACKAGE BODY     6
             15-MAR-20 TYPE             2

SALES        10-JAN-20 TABLE           12
             10-JAN-20 INDEX           23
             10-JAN-20 SEQUENCE         5

             15-FEB-20 TABLE            3
             15-FEB-20 INDEX            5
             15-FEB-20 MATERIALIZED VIEW 2

             01-APR-20 PROCEDURE        8
             01-APR-20 FUNCTION         4
             01-APR-20 PACKAGE          5
             01-APR-20 PACKAGE BODY     5

Key Output Columns

• OWNER: Schema that owns the objects
• CREATION DATE: Date when objects were created (time truncated)
• OBJECT TYPE: Type of database object (TABLE, INDEX, PROCEDURE, etc.)
• OBJECT TYPE: Count of objects of that type created on that date

Understanding Object Creation Patterns

Deployment Events

Initial Schema Creation

-- Large numbers of objects on same date indicates:
-- Initial application deployment
-- Schema import/migration
-- Database refresh from another environment
-- Major application release

Incremental Changes

-- Small numbers of objects over time indicates:
-- Ongoing development
-- Bug fixes and patches
-- Feature additions
-- Maintenance updates

Object Type Patterns

Related Object Creation

-- Common patterns:
-- Tables and indexes created together
-- Packages and package bodies as pairs
-- Views following table creation
-- Triggers with table modifications

Development Phases

-- Typical progression:
-- 1. Tables and constraints (data model)
-- 2. Indexes (performance optimization)
-- 3. Procedures/Functions (business logic)
-- 4. Packages (organized code)
-- 5. Views (data access layer)

Common Use Cases

1. Deployment Verification

   • Confirm successful deployments
   • Verify all objects were created
   • Track deployment dates
   • Compare environments

2. Change Management

   • Document schema changes over time
   • Track release deployments
   • Identify unauthorized changes
   • Support change control processes

3. Audit and Compliance

   • Provide creation audit trail
   • Support compliance requirements
   • Document schema evolution
   • Track object lifecycle

4. Environment Comparison

   • Compare creation patterns between environments
   • Identify missing objects
   • Verify environment synchronization
   • Plan migrations

Advanced Analysis

Deployment Pattern Analysis

-- Identify major deployment dates:
SELECT owner, created, SUM(object_count) total_objects
FROM (
  SELECT owner, TRUNC(created) created, COUNT(*) object_count
  FROM dba_objects
  WHERE owner NOT IN ('SYS','SYSTEM')
  GROUP BY owner, TRUNC(created)
)
GROUP BY owner, created
HAVING SUM(object_count) > 10
ORDER BY owner, created;

Recent Object Creation

-- Find objects created in last 30 days:
SELECT owner, TRUNC(created) created, object_type, COUNT(*) count
FROM dba_objects
WHERE created > SYSDATE - 30
  AND owner NOT IN ('SYS','SYSTEM')
GROUP BY owner, TRUNC(created), object_type
ORDER BY created DESC, owner, object_type;

Object Creation Timeline

-- Create timeline of schema development:
SELECT
  TO_CHAR(TRUNC(created),'YYYY-MM') month,
  owner,
  COUNT(*) objects_created
FROM dba_objects
WHERE owner LIKE upper('&owner')
GROUP BY TO_CHAR(TRUNC(created),'YYYY-MM'), owner
ORDER BY 1, 2;

Deployment Analysis Techniques

Release Identification

Major Releases

-- Identify likely release dates:
-- High object counts on single date
-- Multiple object types created together
-- Coordinated across multiple schemas
-- Often includes tables, indexes, code objects

Patch Deployments

-- Identify patch/hotfix deployments:
-- Few objects created
-- Usually procedures/packages only
-- No table structure changes
-- Scattered dates between major releases

Schema Evolution Tracking

Growth Analysis

-- Track schema growth over time:
SELECT
  owner,
  MIN(created) first_object,
  MAX(created) last_object,
  COUNT(*) total_objects,
  COUNT(DISTINCT TRUNC(created)) deployment_days
FROM dba_objects
WHERE owner NOT IN ('SYS','SYSTEM')
GROUP BY owner
ORDER BY total_objects DESC;

Object Type Evolution

-- See how object types evolved:
SELECT
  object_type,
  MIN(created) first_created,
  MAX(created) last_created,
  COUNT(*) total_count
FROM dba_objects
WHERE owner = upper('&owner')
GROUP BY object_type
ORDER BY MIN(created);

Integration with Change Management

Documentation Support

Deployment Documentation

-- Generate deployment summary:
-- Run before and after deployments
-- Compare results to verify changes
-- Document in change tickets
-- Archive for compliance

Change Verification

-- Verify expected objects were created:
-- Compare deployment manifest with actual
-- Identify any missing objects
-- Confirm object types match expectations
-- Validate deployment success

Compliance and Auditing

Audit Trail Generation

-- Create audit reports showing:
-- When objects were created
-- What types of objects
-- Which schemas were affected
-- Deployment frequency patterns

Unauthorized Change Detection

-- Monitor for unexpected changes:
-- Objects created outside deployment windows
-- Unusual creation patterns
-- Objects in production without change tickets
-- Schema modifications by unauthorized users

Best Practices

Regular Monitoring

Scheduled Reviews

-- Weekly: Check for recent object creation
-- Monthly: Review deployment patterns
-- Quarterly: Analyze schema growth trends
-- Annually: Document schema evolution

Baseline Establishment

-- Document normal patterns:
-- Typical deployment days/times
-- Expected object counts per release
-- Standard object type distributions
-- Authorized schema owners

Change Control Integration

Pre-Deployment Snapshots

-- Before deployments:
-- Capture current object inventory
-- Document existing creation dates
-- Save results for comparison
-- Include in change documentation

Post-Deployment Verification

-- After deployments:
-- Run script to verify new objects
-- Compare with deployment manifest
-- Document any discrepancies
-- Update change records

Troubleshooting Deployment Issues

Missing Objects

Investigation Steps

-- When objects are missing:
-- Check for creation errors in logs
-- Verify deployment scripts completed
-- Look for privilege issues
-- Check for name conflicts

Common Causes

-- Deployment failures:
-- Insufficient privileges
-- Space allocation errors
-- Dependency issues
-- Script execution errors

Unexpected Objects

Detection Methods

-- Identify unauthorized objects:
-- Objects created outside deployment windows
-- Unusual object naming patterns
-- Objects without documentation
-- Creation by non-standard users

Response Actions

-- For unexpected objects:
-- Document finding immediately
-- Investigate creation source
-- Verify with development team
-- Follow security procedures if needed

Performance Considerations

Query Optimization

-- For large databases:
-- Consider date range filters
-- Limit to specific schemas
-- Use parallel query if available
-- Index DBA_OBJECTS if permitted

Resource Usage

-- Script impact:
-- Minimal resource consumption
-- Quick execution time
-- No locking concerns
-- Safe for production use

Related Scripts

• dobjectd.sql - Objects by creation date with details
• dobjmod.sql - Objects by modification date
• dobject.sql - Comprehensive object listing
• objcnt.sql - Object count analysis

Best Practices Summary

1. Regular Execution

   • Run weekly to track changes
   • Archive results for historical analysis
   • Include in deployment procedures
   • Monitor for unauthorized changes

2. Documentation Integration

   • Include output in deployment documentation
   • Track schema evolution over time
   • Support compliance requirements
   • Maintain deployment history

3. Change Management

   • Use for pre/post deployment verification
   • Validate deployment success
   • Detect unauthorized changes
   • Support audit requirements