Table Storage Analysis (dtable.sql)

What This Script Does

Displays detailed storage parameters for Oracle tables including tablespace assignment, extent management, block utilization settings, and parallel processing configuration.

The Script

rem dtable.sql
rem
rem  linesize = 500
rem
set linesize 500
rem
ttitle 'Table Storage Parameters'
rem
col owner format a15 heading 'OWNER'
col table_lock format a1 heading 'L'
col cache format a1 heading 'C'
col table_name format a34 heading 'TABLE NAME'
col tablespace_name format a15 heading 'TABLESPACE'
col logging format a3 heading 'LOG'
col iot_type format a3 heading 'IOT'
col pct_free format 999 heading 'PCT|FREE'
col pct_used format 999 heading 'PCT|USED'
col pct_sum format 999 heading 'PCT|SUM'
col ini_trans format 999 heading 'INI|TRX'
col max_trans format 999 heading 'MAX|TRX'
col initial_extent format 9,999,999 heading 'INITIAL|EXTENT|(K)'
col next_extent format 9,999,999 heading 'NEXT|EXTENT|(K)'
col pct_increase format 999 heading 'PCT|INC'
col min_extents format 99 heading 'MIN|EXT'
col max_extents format 99,999 heading 'MAX|EXT'
col freelists format 999 heading 'FREE|LIST'
col freelist_groups format 999 heading 'FREE|LIST|GROUP'
col degree format a7 heading 'DEGREE'
col temporary format a1 heading 'T'
rem
break on owner skip 1
rem
select owner,
       table_name,
       tablespace_name,
       decode( table_lock, 'ENABLED', null, 'DISABLED', 'X', '?' ) table_lock,
       decode( cache, '    Y', 'C' ) cache,
       decode(substr(logging,1,1),'N', 'N', null) logging,
--       temporary,
       -- iot_name,
       iot_type,
       pct_free, pct_used,
       ini_trans, max_trans,
       initial_extent/1024 initial_extent,
--       pct_increase,
       min_extents, max_extents,
--       freelists,
       -- freelist_groups,
       ltrim(rtrim(degree)) degree
  from sys.dba_tables
 where owner like nvl(upper('&owner'),'%')
   and table_name like nvl(upper('&table'),'%')
 order by owner, table_name;
rem
set linesize 80

Usage

-- Basic usage
@dtable.sql

-- When prompted, enter:
-- owner: ** - Schema owner (use % for all schemas)
-- table: ** - Table name pattern (use % for all tables)

Parameters

The script prompts for:

&owner - ** - Schema owner (use % for all schemas)
&table - ** - Table name pattern (use % for all tables)

Required Privileges

SELECT ANY DICTIONARY
-- OR --
SELECT ON SYS.DBA_TABLES

Sample Output

Table Storage Parameters

OWNER           TABLE NAME                 TABLESPACE      LOG IOT PCT PCT INI MAX  INITIAL   MIN MAX   DEGREE
                                                              FREE USED TRX TRX EXTENT  EXT EXT
                                                                          (K)
--------------- -------------------------- --------------- --- --- --- --- ---  ------- --- ----- -------
SALES           CUSTOMERS                  SALES_DATA          10  40   1 255      64   1 99999       1
                ORDERS                     SALES_DATA          10  40   2 255    1024   1 99999       4
                ORDER_ITEMS                SALES_DATA          10  40   1 255     512   1 99999       1

HR              EMPLOYEES                  USERS               10  40   1 255      64   1 99999       1
                DEPARTMENTS                USERS               10  40   1 255      64   1 99999       1

Key Output Columns

OWNER - Schema that owns the table
TABLE NAME - Name of the table
TABLESPACE - Tablespace where table is stored
LOG - Logging enabled (blank = yes, N = no)
IOT - Index Organized Table type
PCT FREE/USED - Block space management settings
INI/MAX TRX - Transaction slot settings
INITIAL EXTENT - Initial extent size in KB
MIN/MAX EXT - Minimum and maximum extent counts
DEGREE - Parallel processing degree

Common Use Cases

Storage Planning

-- Analyze all tables in a schema
@dtable.sql
-- Enter: SALES for owner, % for table

Find Large Tables

-- Look at tables with high initial extents
-- Review output for INITIAL EXTENT column

Parallel Processing Check

-- Check DEGREE column for parallel settings
-- Values > 1 indicate parallel processing enabled

Index Storage Analysis - Companion script for indexes
Table Column Analysis - Column details for same tables