Empty Tables with Allocated Storage (dstatzer.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 identifies empty tables that still have allocated storage blocks, revealing opportunities for space reclamation and storage optimization. These tables consume disk space despite containing no data, which can occur after bulk deletions, failed data loads, or during development activities. The script is essential for database housekeeping and storage optimization initiatives.

The Script

rem  dstatzer.sql
rem
ttitle 'Zero Row Table Statistics'
rem
col owner format a8 heading 'OWNER'
col table_name format a21 heading 'TABLE NAME'
col num_rows format 9999999990 heading 'ROWS'
col chain_cnt format 9990 heading 'CHAIN|COUNT'
col avg_row_len format 9990 heading 'AVG|ROW|SIZE'
col blocks format 999999990 heading 'USED|BLOCKS'
col empty_blocks format 9999990 heading 'EMPTY|BLOCKS'
col avg_space format 9990 heading 'AVG|FREE|SPACE'
rem
break on owner skip 1
compute sum of num_rows blocks empty_blocks on owner
rem
select owner, table_name, num_rows, chain_cnt, avg_row_len,
       blocks, empty_blocks, avg_space
  from sys.dba_tables
 where owner like upper('&owner')
   and table_name like upper('&table')
   and num_rows = 0
   and blocks > 0
 order by owner, table_name;

Key Features

• Empty Table Detection: Finds tables with zero rows but allocated blocks
• Space Waste Identification: Highlights storage that can be reclaimed
• Schema and Table Filtering: Supports pattern matching for targeted analysis
• Storage Metrics: Shows detailed block allocation information
• Summary Statistics: Provides totals by owner for planning purposes
• Optimization Support: Enables informed space reclamation decisions

Usage

@dstatzer.sql

Input Parameters:

• owner: Schema owner pattern (use % for wildcards)
• table: Table name pattern (use % for wildcards)

Required Privileges

SELECT on SYS.DBA_TABLES

Sample Output

                                            Zero Row Table Statistics

OWNER    TABLE NAME               ROWS CHAIN AVG    USED    EMPTY AVG
                                       COUNT ROW     BLOCKS  BLOCKS FREE
                                             SIZE                   SPACE
-------- --------------------- --------- ----- ---- --------- ------- ----
DEV      TEMP_LOAD_TABLE               0     0    0       125      45 8192
         TEST_DATA_BACKUP              0     0    0        89      23 8192
         STAGING_CUSTOMER_DATA         0     0    0       234      67 8192
                               --------- --------- ---------
sum                                    0       448     135

SALES    OLD_ORDERS_ARCHIVE            0     0    0       567     123 8192
         TEMP_IMPORT_DATA              0     0    0        45      12 8192
                               --------- --------- ---------
sum                                    0       612     135

TEST     PERFORMANCE_TEST_TBL          0     0    0       789     234 8192
         BULK_DELETE_TEST              0     0    0       345      89 8192
         DATA_MIGRATION_TEMP           0     0    0       156      45 8192
                               --------- --------- ---------
sum                                    0      1290     368

Key Output Columns

• OWNER: Schema owner of the table
• TABLE NAME: Name of the empty table
• ROWS: Number of rows (always 0 for this analysis)
• CHAIN COUNT: Number of chained rows (always 0 for empty tables)
• AVG ROW SIZE: Average row length (always 0 for empty tables)
• USED BLOCKS: Number of blocks still allocated to the table
• EMPTY BLOCKS: Number of empty blocks allocated
• AVG FREE SPACE: Average free space per block

Understanding Empty Table Storage

Why Empty Tables Consume Storage

Common Scenarios

-- Reasons for empty tables with allocated blocks:
-- Bulk deletions leaving allocated space
-- Failed or interrupted data loads
-- Test data creation and cleanup
-- ETL process intermediate tables
-- Development and testing activities

Storage Allocation Behavior

-- Oracle storage allocation:
-- Initial extent allocation at table creation
-- Minimum extent size requirements
-- Block allocation for failed INSERT operations
-- Space not automatically deallocated after DELETE

Storage Impact Assessment

Space Consumption Analysis

-- Calculate storage waste:
-- Total blocks = used_blocks + empty_blocks
-- Storage waste = total_blocks * block_size
-- Potential reclamation = blocks * block_size
-- Percentage of total schema storage

Performance Implications

-- Impact on database operations:
-- Unnecessary space consumption
-- Backup size inflation
-- Export/import time increase
-- Statistics gathering overhead

Common Use Cases

1. Storage Optimization

   • Identify space that can be reclaimed
   • Plan storage cleanup activities
   • Reduce database storage footprint
   • Optimize backup and recovery times

2. Database Housekeeping

   • Clean up development and test artifacts
   • Remove abandoned staging tables
   • Eliminate failed data load remnants
   • Support database maintenance programs

3. Capacity Planning

   • Assess actual vs. allocated storage
   • Identify storage waste patterns
   • Plan storage reclamation projects
   • Support infrastructure optimization

4. Environment Management

   • Clean up development environments
   • Standardize test environment procedures
   • Implement automated cleanup processes
   • Support environment refresh activities

Advanced Analysis

Storage Reclamation Assessment

Reclamation Calculations

-- Calculate potential savings:
-- Space per table = (used_blocks + empty_blocks) * block_size
-- Total reclaimable space = SUM(space per table)
-- Percentage of schema storage = reclaimable / total schema storage
-- Cost-benefit analysis of cleanup effort

Priority Assessment

-- Prioritize reclamation efforts:
-- Tables with largest block allocation
-- Development/test schemas with high waste
-- Production schemas with space pressure
-- Tables affecting backup/recovery times

Pattern Analysis

Common Patterns

-- Identify waste patterns:
-- Temporary tables left from ETL processes
-- Test data tables in production schemas
-- Staging tables from failed migrations
-- Archive tables after data purging

Schema-Specific Analysis

-- Schema waste patterns:
-- Development schemas: High waste from testing
-- Production schemas: ETL and maintenance artifacts
-- Test schemas: Incomplete cleanup procedures
-- Archive schemas: Purged but not deallocated

Space Reclamation Strategies

Table Management Options

Immediate Reclamation

-- Quick space reclamation methods:
-- DROP TABLE (if table not needed)
-- TRUNCATE TABLE (reset to initial state)
-- ALTER TABLE DEALLOCATE UNUSED (partial reclamation)
-- MOVE operation to reclaim all space

Conditional Reclamation

-- Careful reclamation approaches:
-- Verify table usage before dropping
-- Check for dependencies and constraints
-- Coordinate with application teams
-- Plan rollback procedures if needed

Automated Cleanup Procedures

Cleanup Scripts

-- Automated cleanup strategies:
-- Regular identification of empty tables
-- Automated DEALLOCATE UNUSED operations
-- Scheduled cleanup of temporary tables
-- Policy-based table lifecycle management

Safety Procedures

-- Safety measures:
-- Backup before major cleanup activities
-- Test procedures in development first
-- Implement approval processes for production
-- Document cleanup activities and results

Filtering Examples

All Schemas Analysis

-- Comprehensive database analysis:
Enter value for owner: %
Enter value for table: %

Development Environment Cleanup

-- Focus on development schemas:
Enter value for owner: %DEV%
Enter value for table: %

Temporary Table Analysis

-- Find temporary and staging tables:
Enter value for owner: %
Enter value for table: %TEMP%
Enter value for table: %STAGING%

Specific Schema Focus

-- Single schema analysis:
Enter value for owner: SALES
Enter value for table: %

Integration with Maintenance

Regular Cleanup Procedures

Cleanup Schedule

-- Regular maintenance:
-- Daily cleanup of temporary tables
-- Weekly analysis of development schemas
-- Monthly production environment review
-- Quarterly comprehensive assessment

Cleanup Validation

-- Validation procedures:
-- Verify table usage before cleanup
-- Check application dependencies
-- Confirm with development teams
-- Document cleanup decisions

Monitoring and Alerting

Waste Monitoring

-- Monitor storage waste:
-- Track empty table storage trends
-- Alert on excessive waste accumulation
-- Report cleanup results
-- Measure storage reclamation success

Policy Implementation

-- Cleanup policies:
-- Automatic cleanup of specific table patterns
-- Approval processes for production cleanup
-- Documentation requirements
-- Audit trail maintenance

Best Practices

Cleanup Strategy

Risk Assessment

-- Assess cleanup risks:
-- Verify table purpose and usage
-- Check for hidden dependencies
-- Coordinate with application owners
-- Plan rollback procedures

Implementation Approach

-- Systematic cleanup:
-- Start with obvious temporary tables
-- Progress to development environments
-- Handle production tables carefully
-- Document all cleanup activities

Prevention Strategies

Development Practices

-- Prevent future waste:
-- Implement table naming conventions
-- Establish cleanup procedures for testing
-- Use temporary tablespaces appropriately
-- Document table lifecycle expectations

Automated Management

-- Automation opportunities:
-- Scheduled cleanup of temporary tables
-- Policy-based table lifecycle management
-- Automated space monitoring
-- Integration with change management

Related Scripts

• dstattr.sql - Table statistics report
• dtable.sql - Comprehensive table analysis
• dsegbig.sql - Large segment analysis
• dobject.sql - Database object analysis

Troubleshooting Applications

Storage Issues

Space Pressure

-- Address space constraints:
-- Identify largest empty table contributors
-- Prioritize reclamation by impact
-- Plan immediate vs. scheduled cleanup
-- Monitor space pressure relief

Performance Impact

-- Performance considerations:
-- Backup and recovery time impact
-- Export/import efficiency
-- Statistics gathering overhead
-- Storage scan performance

Cleanup Challenges

Dependency Issues

-- Handle dependencies:
-- Foreign key constraints
-- View dependencies
-- Application code references
-- Procedure and function usage

Coordination Requirements

-- Team coordination:
-- Application team approval
-- Development team notification
-- Operations team scheduling
-- Documentation updates

Safety Considerations

Backup and Recovery

Pre-Cleanup Backup

-- Backup strategies:
-- Full backup before major cleanup
-- Logical backup of affected schemas
-- Table-level export for critical tables
-- Recovery testing procedures

Rollback Planning

-- Rollback procedures:
-- Recreation scripts for dropped tables
-- Data restoration procedures
-- Constraint recreation
-- Application testing validation

Summary

This script is essential for:

1. Storage Optimization - Identifying and reclaiming storage from empty tables that still consume space
2. Database Housekeeping - Supporting regular cleanup of unused tables and storage waste
3. Capacity Planning - Understanding actual vs. allocated storage utilization
4. Environment Management - Cleaning up development, test, and production environments
5. Cost Optimization - Reducing storage costs and improving backup/recovery efficiency