Tables with Zero Rows but Allocated Blocks (dstatzero.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 dstatzero.sql script.

The Script

rem  dstatzero.sql
rem
ttitle 'Table Statistics - No Rows'
rem
col owner format a8 heading 'OWNER'
col table_name format a26 heading 'TABLE NAME'
col num_rows format 999999990 heading 'ROWS'
col chain_cnt format 9990 heading 'CHAIN|COUNT'
col avg_row_len format 9990 heading 'AVG|ROW|SIZE'
col blocks format 999990 heading 'USED|BLOCKS'
col empty_blocks format 999990 heading 'EMPTY|BLOCKS'
col avg_space format 9990 heading 'AVG|FREE|SPACE'
rem
break on report on owner skip 1
compute sum of num_rows blocks empty_blocks on report 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 blocks <> 0
   and num_rows = 0
 order by owner, table_name;

What This Script Does

This script identifies tables that show zero rows in their statistics but have allocated database blocks, which can indicate space waste, stale statistics, or tables that have been truncated but not deallocated. It’s essential for space management and identifying potential storage optimization opportunities.

Key Features

• Space Anomaly Detection: Finds tables consuming space with zero rows
• Statistics Analysis: Shows various table statistics for comparison
• Storage Metrics: Displays block usage and space information
• Flexible Filtering: Filter by owner and table name patterns
• Summary Reporting: Provides totals grouped by owner

Usage

Run the script and provide filter criteria when prompted:

@dstatzero.sql

Input Parameters:

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

Required Privileges

SELECT on SYS.DBA_TABLES

Sample Output

                                        Table Statistics - No Rows

OWNER    TABLE NAME                 ROWS CHAIN AVG  USED EMPTY    AVG
                                         COUNT ROW BLOCKS BLOCKS  FREE
                                               SIZE              SPACE
-------- -------------------------- ---- ----- ---- ------ ------ ----
HR       EMPLOYEE_BACKUP               0     0    0     25      0    0
         TEMP_EMPLOYEE_DATA            0     0    0     45      0    0
         OLD_SALARY_HISTORY            0     0    0     18      0    0
                                      ---- ----- ------ ------
sum                                     0     0     88      0

SALES    CUSTOMER_ARCHIVE              0     0    0    156      0    0
         MONTHLY_TEMP_DATA             0     0    0     67      0    0
         ORDER_STAGING                 0     0    0     23      0    0
         QUARTERLY_BACKUP              0     0    0     89      0    0
                                      ---- ----- ------ ------
sum                                     0     0    335      0

FINANCE  BUDGET_TEMP                   0     0    0     12      0    0
         RECONCILIATION_STAGE          0     0    0     34      0    0
         YEAR_END_BACKUP               0     0    0     78      0    0
                                      ---- ----- ------ ------
sum                                     0     0    124      0

                                      ==== ===== ====== ======
sum                                     0     0    547      0

Key Output Columns

• OWNER: Schema that owns the table
• TABLE NAME: Name of the table
• ROWS: Number of rows according to statistics (always 0 in this report)
• CHAIN COUNT: Number of chained rows
• AVG ROW SIZE: Average row length in bytes
• USED BLOCKS: Number of blocks allocated to the table
• EMPTY BLOCKS: Number of empty blocks in the table
• AVG FREE SPACE: Average free space per block

Understanding Zero-Row Tables with Blocks

Possible Causes

Stale Statistics

• Outdated information: Statistics not updated after data deletion
• Truncate operations: Table truncated but statistics not refreshed
• Bulk delete: Large delete operations without statistics update

Space Management Issues

• High Water Mark: Blocks allocated but not deallocated after truncate
• Deferred segment creation: 11g+ feature may show unusual patterns
• Table reorganization: Incomplete reorganization operations

Temporary or Staging Tables

• ETL processes: Tables used for data loading and processing
• Backup tables: Tables created for backup purposes
• Temporary storage: Tables used for temporary data processing

Block Allocation Patterns

Small Block Counts (< 50 blocks)

• Initial extent: Tables with initial extent allocation
• Minimal impact: Low priority for space reclamation
• Common pattern: Normal for many tables

Large Block Counts (> 100 blocks)

• Significant space: Potential for meaningful space reclamation
• Investigation needed: Determine if space can be reclaimed
• High priority: Should be addressed for space optimization

Common Use Cases

1. Space Optimization

   • Identify tables consuming unnecessary space
   • Find candidates for space reclamation
   • Plan storage cleanup activities

2. Statistics Management

   • Find tables with stale statistics
   • Identify tables needing statistics refresh
   • Plan statistics gathering schedules

3. Database Cleanup

   • Locate abandoned temporary tables
   • Find unused backup tables
   • Identify orphaned staging tables

4. Capacity Planning

   • Understand wasted space patterns
   • Plan for space reclamation
   • Optimize storage allocation

Analysis and Resolution

Statistics Refresh

-- Update statistics for identified tables:
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEE_BACKUP');
EXEC DBMS_STATS.GATHER_TABLE_STATS('SALES', 'CUSTOMER_ARCHIVE');

Space Reclamation

-- For tables that are truly empty:
-- Option 1: Truncate and deallocate space
TRUNCATE TABLE hr.employee_backup DROP STORAGE;

-- Option 2: Move table to reclaim space
ALTER TABLE sales.customer_archive MOVE;

-- Option 3: Shrink table (if applicable)
ALTER TABLE finance.budget_temp ENABLE ROW MOVEMENT;
ALTER TABLE finance.budget_temp SHRINK SPACE;

Table Analysis

-- Verify if table is actually empty:
SELECT COUNT(*) FROM hr.employee_backup;

-- Check actual space usage:
SELECT table_name, num_rows, blocks, empty_blocks
FROM dba_tables
WHERE owner = 'HR' AND table_name = 'EMPLOYEE_BACKUP';

Advanced Analysis

Actual Row Count Verification

-- For suspicious tables, verify actual row count:
-- This may take time for large tables
SELECT 'HR.EMPLOYEE_BACKUP' table_name, COUNT(*) actual_rows
FROM hr.employee_backup
UNION ALL
SELECT 'SALES.CUSTOMER_ARCHIVE', COUNT(*)
FROM sales.customer_archive;

Space Calculation

-- Calculate potential space savings:
SELECT owner,
       SUM(blocks * 8192) / 1024 / 1024 potential_mb_savings
FROM dba_tables
WHERE blocks > 0 AND num_rows = 0
GROUP BY owner
ORDER BY potential_mb_savings DESC;

High Water Mark Analysis

-- Check for high water mark issues:
SELECT owner, table_name, blocks, empty_blocks,
       blocks - NVL(empty_blocks,0) as hwm_blocks
FROM dba_tables
WHERE blocks > 0 AND num_rows = 0
AND blocks > 50  -- Focus on larger tables
ORDER BY hwm_blocks DESC;

Filter Examples

Check Specific Schema

Enter value for owner: HR
Enter value for table: %

Find Large Zero-Row Tables

-- After running, focus on tables with USED BLOCKS > 100
-- These represent the most significant space savings opportunities

Check Temporary Tables

Enter value for owner: %
Enter value for table: %TEMP%

Troubleshooting and Investigation

Verify Table Emptiness

-- Before taking action, verify tables are actually empty:
-- 1. Check actual row count with SELECT COUNT(*)
-- 2. Review table usage patterns
-- 3. Check for recent data loading activities
-- 4. Verify with application teams before cleanup

Statistics Issues

-- Common statistics problems:
-- 1. Statistics gathered before data deletion
-- 2. Manual statistics updates with incorrect values
-- 3. Locked statistics preventing updates
-- 4. Incremental statistics issues

Space Management

-- Understanding space allocation:
-- 1. Check tablespace free space
-- 2. Review table creation patterns
-- 3. Analyze extent allocation
-- 4. Consider deferred segment creation effects

Best Practices

Regular Monitoring

1. Monthly reviews: Run during maintenance windows
2. Threshold monitoring: Focus on tables > 50 blocks
3. Trend analysis: Track space waste over time
4. Documentation: Record cleanup activities

Statistics Management

1. Regular updates: Schedule statistics gathering
2. Post-maintenance: Update statistics after bulk operations
3. Monitoring: Check for stale statistics regularly
4. Automation: Use automated statistics gathering where possible

Space Optimization

1. Impact assessment: Calculate space savings potential
2. Application coordination: Verify with application teams
3. Testing: Test space reclamation in development
4. Scheduling: Plan cleanup during maintenance windows

Related Scripts

• dstatbig.sql - Large table statistics analysis
• dtable.sql - Table storage analysis
• dsegbig.sql - Large segment analysis
• dcontig.sql - Free space analysis

Integration with Maintenance

Space Management Process

1. Run dstatzero.sql to identify candidates
2. Verify tables are actually empty
3. Check with application teams
4. Plan and execute space reclamation
5. Update statistics and re-verify

Statistics Maintenance

1. Include identified tables in regular statistics gathering
2. Monitor for recurring zero-row situations
3. Investigate patterns in space allocation
4. Optimize table creation and management practices