Oracle Top 10 Largest Segments Query with dba_segments

Show the ten largest oracle objects in the database using dba_segments

Capacity planning starts with knowing which segments hold the most space. DBA_SEGMENTS describes the storage allocated for all segments in the database, so one sorted query shows the ten biggest consumers.

SQL Code

 1col owner format a15
 2col segment_name format a30
 3col segment_type format a15
 4col mb format 999,999,999
 5select  owner
 6, segment_name
 7, segment_type
 8, mb
 9from (
10 select owner
11 , segment_name
12 , segment_type
13 , bytes / 1024 / 1024 "MB"
14 from dba_segments
15 order by bytes desc
16 )
17where rownum < 11
18/

Sample Oracle Output

 1OWNER           SEGMENT_NAME                   SEGMENT_TYPE              MB
 2--------------- ------------------------------ --------------- ------------
 3MYAPP           TRANS_LOG                      TABLE                  7,880
 4MYAPP           DESCRIPTION                    INDEX                  6,028
 5MYAPP           EVT_LOG                        TABLE                  4,984
 6ARCHIVE         EVT_LOG                        TABLE                  3,080
 7MYAPP           ATTRIBUTES                     TABLE                  2,312
 8MYAPP           UNIQUE_EVT                     INDEX                  2,129
 9MYAPP           EVT_HOSTNAME                   INDEX                  1,409
10MYAPP           SOURCE                         INDEX                  1,407

The sample is illustrative and trimmed to eight rows. The real query returns up to ten rows, and on a real database SYS and SYSTEM segments can appear among them.

Purpose:

• To list the 10 largest database segments by allocated size. The result shows where space goes and supports space management and capacity planning.

Breakdown:

Formatting Output:

• col owner format a15, col segment_name format a30, col segment_type format a15, col mb format 999,999,999: SQL*Plus COLUMN ... FORMAT sets the display format of a column. Here it sets character widths and a thousands-separated number format.

Retrieving and Ordering Segment Data:

• (select owner, segment_name, segment_type, bytes / 1024 / 1024 "MB" from dba_segments order by bytes desc):

  • Queries the dba_segments view, which describes the storage allocated for all segments in the database.
  • Selects owner, segment name, segment type, and size in megabytes.
  • Orders results by size in descending order, so the largest segments come first.

Limiting Results:

• where rownum < 11: Keeps only the first 10 rows of the ordered inline view. The ORDER BY sits in the subquery and the ROWNUM condition in the top-level query, so the condition is applied after the rows are ordered.

Displaying Final Output:

• Presents the following information for the top 10 segments:

  • owner: Username of the segment owner.
  • segment_name: Name of the segment.
  • segment_type: Type of segment, such as TABLE, INDEX, TABLE PARTITION, INDEX PARTITION, LOBSEGMENT or LOBINDEX.
  • mb: Allocated size of the segment in megabytes (BYTES / 1024 / 1024).

Key Points:

• Focus on Largest Segments: Shows which segments hold the most allocated space.
• Allocated, not used: BYTES is the size allocated to the segment, not the amount of data inside it.
• Partitioned objects: Partitions are listed as their own segment types (TABLE PARTITION, INDEX PARTITION), so a partitioned table can show up as several rows.

Insights and Explanations:

• Space Management:

  • Helps identify segments to review for space-saving steps, such as compression, archiving, or partitioning.
  • Guides storage allocation decisions and capacity planning.

• Troubleshooting:

  • Can be used to investigate space-related issues, such as unexpected growth or storage constraints.

• Customization:

  • Can be modified to filter for specific segment types or owners.

• Integration: Consider running the query on a schedule and keeping the results, so you can compare segment sizes over time.

References

• DBA_SEGMENTS (Oracle Database 19c Reference) - column definitions and the list of segment types.
• DBA_SEGMENTS (Oracle Database 23ai Reference) - the same view in the 23ai release.
• ROWNUM Pseudocolumn (Oracle Database 19c SQL Language Reference) - the ordered-subquery pattern for top-N results.
• COLUMN (SQL*Plus User's Guide, 19c) - the COLUMN ... FORMAT command.
• Top-N Queries (ORACLE-BASE) - top-N query techniques, including the inline view with ROWNUM.