Tablespace Definitions (dtspace.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 a comprehensive view of tablespace configuration including extent management, space allocation, and segment management settings. It displays only non-default values for cleaner output, helping DBAs understand tablespace architecture and identify configuration differences across tablespaces.

Script

rem  dtspace.sql
rem
set linesize 132
rem
ttitle 'Tablespace Definitions'
rem
clear break
rem
col contents format a9 heading 'CONTENTS'
col status format a6 heading 'STATUS'
col logging format a3 heading 'LOG'
col tablespace_name format a25 heading 'TABLESPACE'
col initial_extent format 999,999 heading 'INITIAL|EXTENT|(K)'
col next_extent format 999,999 heading 'NEXT|EXTENT|(K)'
col min_extents format 99 heading 'MIN|EXT'
col max_extents format 999999 heading 'MAX|EXT'
col pct_increase format 999 heading 'PCT|INC'
col EXTENT_MANAGEMENT format a5 heading 'EXT|MGMT'
col ALLOCATION_TYPE format a7 heading 'ALLOC'
col SEGMENT_SPACE_MANAGEMENT format a6 heading 'SEG|MGMT'
select decode( contents, 'PERMANENT', null, contents ) contents,
       decode( status, 'ONLINE', null, status ) status,
       decode(logging, 'NOLOGGING', 'NO', NULL) logging,
       decode(extent_management, 'DICTIONARY', null, extent_management)
          extent_management,
       decode(allocation_type, 'USER', null, allocation_type) allocation_type,
       segment_space_management,
       tablespace_name,
       initial_extent/1024 initial_extent,
       next_extent/1024 next_extent,
       min_extents,
       max_extents,
       pct_increase,
       block_size
  from sys.dba_tablespaces
 where tablespace_name like nvl(upper('&tablespace'), '%')
 order by tablespace_name;

Usage

-- Run the script in SQL*Plus or SQLcl
@dtspace.sql

-- When prompted, enter:
-- tablespace: Tablespace name pattern (% for all)

-- Examples
Enter value for tablespace: %
-- Or filter specific tablespace
Enter value for tablespace: USERS

Parameters

• &tablespace: Tablespace name pattern (use % for all tablespaces)

Required Privileges

• SELECT on SYS.DBA_TABLESPACES
• Typically requires DBA role or SELECT_CATALOG_ROLE

Sample Output

Tablespace Definitions

CONTENTS STATUS LOG EXT   ALLOC   SEG    TABLESPACE                INITIAL NEXT    MIN MAX    PCT BLOCK_SIZE
                    MGMT          MGMT                              EXTENT  EXTENT  EXT EXT    INC
--------- ------ --- ----- ------- ------ ------------------------- ------- ------- --- ------ --- ----------
                             LOCAL SYSTEM AUTO   SYSAUX                 64     512   1 2147     0       8192
                             LOCAL SYSTEM AUTO   SYSTEM                 64     512   1 2147     0       8192
TEMPORARY        NO  LOCAL UNIFORM MANUAL TEMP                        1024    1024   1   4095    0       8192
UNDO             NO  LOCAL SYSTEM AUTO   UNDOTBS1                     64     512   1 2147     0       8192
                             LOCAL SYSTEM AUTO   USERS                  64     512   1 2147     0       8192

Key Output Columns

• CONTENTS: Tablespace content type (TEMPORARY, UNDO, or blank for PERMANENT)
• STATUS: Status if not ONLINE (OFFLINE, READ ONLY)
• LOG: Logging mode (NO for NOLOGGING, blank for LOGGING)
• EXT MGMT: Extent management (LOCAL or blank for DICTIONARY)
• ALLOC: Allocation type (SYSTEM, UNIFORM, or blank for USER)
• SEG MGMT: Segment space management (AUTO, MANUAL)
• TABLESPACE: Tablespace name
• INITIAL EXTENT: Initial extent size in KB
• NEXT EXTENT: Next extent size in KB
• MIN EXT: Minimum extents
• MAX EXT: Maximum extents (2147 = UNLIMITED)
• PCT INC: Percent increase for next extent
• BLOCK_SIZE: Block size in bytes

Understanding Tablespace Configuration

Extent Management Types

• LOCAL: Locally managed tablespaces (recommended)
• DICTIONARY: Dictionary-managed (deprecated)

Allocation Types

• SYSTEM: Oracle determines extent sizes automatically
• UNIFORM: All extents are same size
• USER: User-specified extent sizes (with DICTIONARY management)

Segment Space Management

• AUTO: ASSM (Automatic Segment Space Management) - recommended
• MANUAL: Manual segment space management with freelists

Content Types

• PERMANENT: Regular data tablespaces
• TEMPORARY: Temporary segments for sorting
• UNDO: Undo/rollback segments

Performance Analysis

Optimal Configuration Indicators

1. LOCAL extent management: Better performance than DICTIONARY
2. AUTO segment space management: Reduces contention
3. SYSTEM allocation: Automatic extent sizing
4. Appropriate block sizes: Match workload characteristics

Configuration Issues

• DICTIONARY management: Should be migrated to LOCAL
• MANUAL segment management: Consider migrating to AUTO
• PCT_INCREASE > 0: Can cause fragmentation
• Small INITIAL_EXTENT: May cause excessive extents

Common Use Cases

Configuration Audit

-- Review all tablespace configurations
@dtspace.sql
-- Enter % to see all tablespaces
-- Look for non-optimal settings

Migration Planning

-- Identify tablespaces needing migration
@dtspace.sql
-- Focus on DICTIONARY managed tablespaces
-- Plan LOCAL management migration

Performance Tuning

-- Analyze extent and block configurations
@dtspace.sql
-- Review extent sizes and allocation types
-- Optimize for workload patterns

Troubleshooting Configuration Issues

Dictionary-Managed Tablespaces

1. Migration to LOCAL:

   -- Migrate to locally managed
   EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('tablespace_name');

2. Verification:

   SELECT tablespace_name, extent_management
   FROM dba_tablespaces
   WHERE extent_management = 'DICTIONARY';

Manual Segment Space Management

1. Migration to ASSM:

   -- Cannot migrate directly - requires rebuild
   -- Create new tablespace with AUTO
   CREATE TABLESPACE new_ts
   DATAFILE '/path/to/datafile.dbf' SIZE 100M
   EXTENT MANAGEMENT LOCAL
   SEGMENT SPACE MANAGEMENT AUTO;

Fragmentation Issues

1. Check for fragmentation:

   SELECT tablespace_name, COUNT(*) extent_count,
          MAX(bytes)/1024/1024 max_extent_mb
   FROM dba_free_space
   GROUP BY tablespace_name
   HAVING COUNT(*) > 100;

2. Resolve with uniform allocation:

   CREATE TABLESPACE uniform_ts
   DATAFILE '/path/to/file.dbf' SIZE 100M
   EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Advanced Analysis

Tablespace Comparison

-- Compare configurations across tablespaces
SELECT extent_management, allocation_type,
       segment_space_management, COUNT(*) tablespace_count
FROM dba_tablespaces
GROUP BY extent_management, allocation_type, segment_space_management
ORDER BY tablespace_count DESC;

Block Size Analysis

-- Analyze block size distribution
SELECT block_size, COUNT(*) tablespace_count,
       LISTAGG(tablespace_name, ', ') WITHIN GROUP (ORDER BY tablespace_name) tablespaces
FROM dba_tablespaces
GROUP BY block_size
ORDER BY block_size;

Extent Size Recommendations

-- Calculate optimal extent sizes based on object sizes
SELECT tablespace_name,
       ROUND(AVG(bytes)/1024/1024, 2) avg_segment_size_mb,
       CASE
         WHEN AVG(bytes)/1024/1024 < 100 THEN '1M UNIFORM'
         WHEN AVG(bytes)/1024/1024 < 1000 THEN '8M UNIFORM'
         ELSE 'SYSTEM ALLOCATION'
       END recommended_allocation
FROM dba_segments
WHERE tablespace_name NOT IN ('SYSTEM', 'SYSAUX')
GROUP BY tablespace_name;

Related Scripts

• Tablespace Space Usage (ddbspace.sql) - Space utilization analysis
• Database Files (dfile.sql) - Datafile information
• Segment Analysis (dsegment.sql) - Object space usage
• Database Information (vdb.sql) - Overall database configuration