Large Segment Analyzer (dsegbig.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 and analyzes large database segments that exceed a specified size threshold. It helps DBAs find space-consuming objects, plan storage capacity, identify candidates for partitioning or archiving, and monitor segment growth patterns across schemas and tablespaces.

Script

rem  dsegbig.sql
rem
rem  linesize = 97
rem
set linesize 132
rem
ttitle 'Large Segment Definitions'
rem
col owner format a12 heading 'OWNER'
col segment_name format a30 heading 'SEGMENT NAME'
col segment_type format a8 heading 'SEGMENT|TYPE'
col tablespace_name format a15 heading 'TABLESPACE'
col bytes format 9,999,999 heading 'BYTES (K)'
col blocks format 999999 heading 'BLOCKS'
col extents format 999 heading 'EXTS'
col max_extents format 9999 heading 'MAX|EXTS'
rem
break on report on owner skip 1
compute sum of bytes blocks extents on report owner
rem
select owner, segment_name, segment_type, tablespace_name,
       bytes/1024 bytes, blocks, extents, max_extents
  from sys.dba_segments
 where owner like upper('&owner')
   and segment_type like upper('&type')
   and segment_name like upper('&object')
   and bytes >= &bytes
 order by owner, segment_name;
rem
set linesize 80

Usage

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

-- When prompted, enter parameters:
-- owner: Schema owner pattern (% for all)
-- type: Segment type pattern (% for all)
-- object: Object name pattern (% for all)
-- bytes: Minimum size in bytes

-- Examples:
Enter value for owner: %
Enter value for type: TABLE
Enter value for object: %
Enter value for bytes: 104857600  -- 100MB

-- Find large indexes in HR schema
Enter value for owner: HR
Enter value for type: INDEX
Enter value for object: %
Enter value for bytes: 52428800   -- 50MB

Parameters

• &owner - Schema owner pattern (supports wildcards)
• &type - Segment type (TABLE, INDEX, LOB, etc.)
• &object - Object name pattern
• &bytes - Minimum size threshold in bytes

Required Privileges

• SELECT on DBA_SEGMENTS
• Typically requires DBA role

Sample Output

Large Segment Definitions

                                      SEGMENT                       BYTES (K)  BLOCKS EXTS  MAX
OWNER        SEGMENT NAME             TYPE     TABLESPACE                                   EXTS
------------ ------------------------ -------- --------------- ------------ ------- ---- -----
HR           EMPLOYEES                TABLE    USERS               2,048,576  262144  128  2048
             EMPLOYEES_HISTORY        TABLE    USERS               5,242,880  655360  256  2048
             EMP_SALARY_IDX           INDEX    USERS                 524,288   65536   64  2048
             EMP_NAME_IDX             INDEX    USERS                 131,072   16384   16  2048
             EMPLOYEE_DOCS            LOB      USERS               8,388,608 1048576  512  2048
                                               ------------   -------  ----
sum                                              16,335,424 2048000  976

SALES        ORDERS                   TABLE    SALES_DATA         10,485,760 1310720  640  4096
             ORDERS_2023              TABLE    SALES_DATA         12,582,912 1572864  768  4096
             ORDER_ITEMS              TABLE    SALES_DATA          8,388,608 1048576  512  4096
             ORDER_IDX                INDEX    SALES_DATA          2,097,152  262144  128  4096
                                               ------------   -------  ----
sum                                              33,554,432 4194304 2048
                                               ============   ======= ====
sum                                              49,889,856 6242304 3024

Key Output Columns

• OWNER - Schema owning the segment
• SEGMENT NAME - Name of the segment
• SEGMENT TYPE - Type (TABLE, INDEX, LOB, etc.)
• TABLESPACE - Tablespace containing the segment
• BYTES (K) - Size in kilobytes
• BLOCKS - Number of database blocks
• EXTS - Number of extents
• MAX EXTS - Maximum extents allowed

Understanding Large Segments

Segment Types

• TABLE - Regular heap tables
• INDEX - B-tree or bitmap indexes
• LOB - Large object segments
• TABLE PARTITION - Partitioned table segments
• INDEX PARTITION - Partitioned index segments

Size Considerations

• Large segments impact backup times
• May cause space allocation issues
• Candidates for partitioning
• Affect query performance

Common Use Cases

Find Top Space Consumers

SELECT owner, segment_name, segment_type,
       ROUND(bytes/1024/1024/1024, 2) size_gb
FROM dba_segments
WHERE bytes > 1073741824  -- 1GB
ORDER BY bytes DESC
FETCH FIRST 20 ROWS ONLY;

Analyze Growth by Tablespace

SELECT tablespace_name,
       COUNT(*) large_segments,
       ROUND(SUM(bytes)/1024/1024/1024, 2) total_gb
FROM dba_segments
WHERE bytes > 104857600  -- 100MB
GROUP BY tablespace_name
ORDER BY SUM(bytes) DESC;

Check Extent Usage

SELECT owner, segment_name,
       extents,
       max_extents,
       ROUND(extents/max_extents*100, 2) pct_used
FROM dba_segments
WHERE bytes > 104857600
AND extents/max_extents > 0.8
ORDER BY extents/max_extents DESC;

Space Management

Segment Advisor Recommendations

-- Run segment advisor for large segments
DECLARE
  task_name VARCHAR2(30) := 'LARGE_SEG_ADVICE';
BEGIN
  DBMS_ADVISOR.CREATE_TASK(
    advisor_name => 'Segment Advisor',
    task_name    => task_name
  );

  DBMS_ADVISOR.CREATE_OBJECT(
    task_name    => task_name,
    object_type  => 'TABLE',
    attr1        => 'SCHEMA',
    attr2        => 'TABLE_NAME'
  );

  DBMS_ADVISOR.EXECUTE_TASK(task_name);
END;
/

Shrink Operations

-- Enable row movement
ALTER TABLE owner.table_name ENABLE ROW MOVEMENT;

-- Shrink space
ALTER TABLE owner.table_name SHRINK SPACE;

-- Shrink space cascade (includes indexes)
ALTER TABLE owner.table_name SHRINK SPACE CASCADE;

Partitioning Candidates

Identify Tables for Partitioning

-- Large tables with date columns
SELECT s.owner, s.segment_name,
       ROUND(s.bytes/1024/1024/1024, 2) size_gb,
       c.column_name date_column
FROM dba_segments s, dba_tab_columns c
WHERE s.owner = c.owner
AND s.segment_name = c.table_name
AND s.segment_type = 'TABLE'
AND s.bytes > 5368709120  -- 5GB
AND c.data_type = 'DATE'
ORDER BY s.bytes DESC;

Check Partition Feasibility

-- Tables with high DML activity
SELECT s.owner, s.segment_name,
       ROUND(s.bytes/1024/1024/1024, 2) size_gb,
       m.inserts, m.updates, m.deletes
FROM dba_segments s, dba_tab_modifications m
WHERE s.owner = m.table_owner
AND s.segment_name = m.table_name
AND s.bytes > 1073741824
ORDER BY s.bytes DESC;

Performance Impact

Buffer Cache Usage

-- Large segments in buffer cache
SELECT owner, object_name, object_type,
       COUNT(*) buffers,
       ROUND(COUNT(*)*8/1024, 2) size_mb
FROM v$bh b, dba_objects o
WHERE b.objd = o.data_object_id
GROUP BY owner, object_name, object_type
HAVING COUNT(*) > 1000
ORDER BY COUNT(*) DESC;

Full Table Scan Impact

-- Large tables with FTS
SELECT p.object_owner, p.object_name,
       s.bytes/1024/1024 size_mb,
       p.operation, p.options
FROM v$sql_plan p, dba_segments s
WHERE p.object_owner = s.owner
AND p.object_name = s.segment_name
AND p.operation = 'TABLE ACCESS'
AND p.options = 'FULL'
AND s.bytes > 104857600
GROUP BY p.object_owner, p.object_name,
         s.bytes, p.operation, p.options;

Best Practices

Storage Management

1. Monitor segment growth trends
2. Set appropriate initial and next extent sizes
3. Use locally managed tablespaces
4. Consider compression for large tables

Maintenance Planning

• Schedule shrink operations during low activity
• Plan partitioning for tables over 2GB
• Archive or purge old data regularly
• Monitor extent allocation

Related Scripts

• Table Storage Analysis (dtable.md) - Detailed table storage
• Maximum Shrink Analysis (../administration/maxshrink.md) - Space reclamation
• Database Space Report (../administration/ddbspace.md) - Overall space usage

Advanced Analysis

Growth Trending

-- Track segment growth over time
WITH seg_sizes AS (
  SELECT segment_name,
         bytes/1024/1024 size_mb,
         SYSDATE capture_date
  FROM dba_segments
  WHERE owner = '&owner'
  AND bytes > 104857600
)
SELECT segment_name, size_mb,
       LAG(size_mb) OVER (ORDER BY segment_name) prev_size,
       size_mb - LAG(size_mb) OVER (ORDER BY segment_name) growth_mb
FROM seg_sizes
ORDER BY growth_mb DESC NULLS LAST;

Compression Candidates

-- Estimate compression benefits
SELECT owner, segment_name,
       ROUND(bytes/1024/1024, 2) current_mb,
       compression, compress_for
FROM dba_segments
WHERE segment_type = 'TABLE'
AND bytes > 1073741824
AND (compression = 'DISABLED'
     OR compression IS NULL)
ORDER BY bytes DESC;