OracleStorage
Top 20 largest segments
The biggest tables, indexes, LOBs and partitions in the database.
Not yet verified. How scripts are tested
1CLEAR COLUMNS2SET LINESIZE 180 PAGESIZE 100 TRIMOUT ON TAB OFF3COLUMN owner FORMAT A204COLUMN segment_name HEADING "SEGMENT" FORMAT A355COLUMN partition_name HEADING "PARTITION" FORMAT A256COLUMN segment_type HEADING "TYPE" FORMAT A187COLUMN tablespace_name HEADING "TABLESPACE" FORMAT A208COLUMN size_gb HEADING "SIZE GB" FORMAT 999,990.999 10SELECT * FROM (11 SELECT owner, segment_name, partition_name, segment_type, tablespace_name,12 ROUND(bytes / POWER(1024, 3), 2) AS size_gb13 FROM dba_segments14 ORDER BY bytes DESC15) WHERE ROWNUM <= 20;Save it as ora-top-segments.sql and run it with SQL> @ora-top-segments.
Helps with
- ORA-01653: unable to extend table %s.%s by %s in tablespace %s
- ORA-01691: unable to extend lob segment %s.%s by %s in tablespace %s
Part of these runbooks
More Oracle scripts: Storage
- Datafile usage with a bar graphEvery datafile with its size, used space, max size and a ten-character usage bar (X = used, - = free). Free_MB is the largest free extent in the…
- Database size three ways: allocated, used and totalAllocated datafile size, space actually used by segments, and the overall footprint including temp files, online redo logs and control files. All in…
- Database size summary: total, used and freeOne line with the database name and its total size (datafiles, temp files and redo logs), used space and free space, rounded to whole GB.
- Size of one schemaTotal segment size in GB for the schema you enter.
- Tablespace usageUsed and free space in GB against the maximum each tablespace can autoextend to, with its type (permanent, temporary or undo), fullest first.
- Datafiles with size and autoextend limitsEvery datafile, its current size, whether it can grow, and how far. Files with AUTOEXTEND NO in a busy tablespace are the ones that page you at night.