OracleStorage

Tablespace usage

Used and free space in GB against the maximum each tablespace can autoextend to, with its type (permanent, temporary or undo), fullest first.

Not yet verified. How scripts are tested

1CLEAR COLUMNS2SET LINESIZE 130 PAGESIZE 100 TRIMOUT ON TAB OFF3COLUMN tablespace_name HEADING "TABLESPACE" FORMAT A254COLUMN contents HEADING "TYPE" FORMAT A95COLUMN used_gb  HEADING "USED GB"  FORMAT 999,990.996COLUMN free_gb  HEADING "FREE GB"  FORMAT 999,990.997COLUMN max_gb   HEADING "MAX GB"   FORMAT 999,990.998COLUMN used_pct HEADING "USED %"   FORMAT 990.99 10SELECT m.tablespace_name,11       t.contents,12       ROUND(m.used_space * t.block_size / POWER(1024, 3), 2) AS used_gb,13       ROUND((m.tablespace_size - m.used_space) * t.block_size / POWER(1024, 3), 2) AS free_gb,14       ROUND(m.tablespace_size * t.block_size / POWER(1024, 3), 2) AS max_gb,15       ROUND(m.used_percent, 1) AS used_pct16FROM dba_tablespace_usage_metrics m17JOIN dba_tablespaces t ON t.tablespace_name = m.tablespace_name18ORDER BY m.used_percent DESC;

Save it as b-ora-ts.sql and run it with SQL> @b-ora-ts.

Open in denrepoRelated tool: Tablespace runway and datafiles

Helps with

• ORA-01653: unable to extend table %s.%s by %s in tablespace %s
• ORA-01654: unable to extend index %s.%s by %s in tablespace %s
• ORA-01688: unable to extend table %s.%s partition %s by %s in tablespace %s
• ORA-01691: unable to extend lob segment %s.%s by %s in tablespace %s

Part of these runbooks

• Morning health check
• Space emergency

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.
• 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.
• Top 20 largest segmentsThe biggest tables, indexes, LOBs and partitions in the database.