Runbook8 steps

Space emergency

For ORA-01653, ORA-01652 and a filling FRA: find what's full, what's growing and what can be reclaimed before you add storage.

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.
Fast Recovery Area usageFRA size, used and reclaimable space, then what's using it by file type. Real used % is the number that matters: at 100% the database stops archiving and hangs.
Temporary tablespace usageSize, allocated and free space for each temporary tablespace.
Sessions using temp spaceWho is using temp right now and for what (sort, hash, LOB), largest first. Run it when you see ORA-01652.
Top 20 largest segmentsThe biggest tables, indexes, LOBs and partitions in the database.
Archived redo generated per dayCount and size of archived logs for the last 14 days, from the local destination only so standby copies aren't double-counted. Useful for sizing the FRA and…
Recycle bin contents by ownerDropped objects still taking up space. Oracle reclaims it under space pressure, but a large recycle bin can make tablespace reports look worse than they are.

Open in denrepo

The whole runbook as one file: SQL> @space

space.sql

1-- denrepo runbook: Space emergency2-- For ORA-01653, ORA-01652 and a filling FRA: find what's full, what's growing and what can be reclaimed before you add storage.3-- Source: https://denrepo.com/runbooks/space/4 5SET ECHO OFF6 7-- Not yet verified8PROMPT9PROMPT ==== 1 of 8: Tablespace usage ====10CLEAR COLUMNS11SET LINESIZE 130 PAGESIZE 100 TRIMOUT ON TAB OFF12COLUMN tablespace_name HEADING "TABLESPACE" FORMAT A2513COLUMN contents HEADING "TYPE" FORMAT A914COLUMN used_gb  HEADING "USED GB"  FORMAT 999,990.9915COLUMN free_gb  HEADING "FREE GB"  FORMAT 999,990.9916COLUMN max_gb   HEADING "MAX GB"   FORMAT 999,990.9917COLUMN used_pct HEADING "USED %"   FORMAT 990.918 19SELECT m.tablespace_name,20       t.contents,21       ROUND(m.used_space * t.block_size / POWER(1024, 3), 2) AS used_gb,22       ROUND((m.tablespace_size - m.used_space) * t.block_size / POWER(1024, 3), 2) AS free_gb,23       ROUND(m.tablespace_size * t.block_size / POWER(1024, 3), 2) AS max_gb,24       ROUND(m.used_percent, 1) AS used_pct25FROM dba_tablespace_usage_metrics m26JOIN dba_tablespaces t ON t.tablespace_name = m.tablespace_name27ORDER BY m.used_percent DESC;28 29-- Not yet verified30PROMPT31PROMPT ==== 2 of 8: Datafiles with size and autoextend limits ====32CLEAR COLUMNS33SET LINESIZE 180 PAGESIZE 200 TRIMOUT ON TAB OFF34COLUMN tablespace_name HEADING "TABLESPACE" FORMAT A2035COLUMN file_name       HEADING "FILE"       FORMAT A7036COLUMN size_gb         HEADING "SIZE GB"    FORMAT 99,990.9937COLUMN autoextensible  HEADING "AUTO"       FORMAT A438COLUMN max_gb          HEADING "MAX GB"     FORMAT 99,990.9939COLUMN status          FORMAT A940 41SELECT tablespace_name, file_name,42       ROUND(bytes / POWER(1024, 3), 2) AS size_gb,43       autoextensible,44       ROUND(maxbytes / POWER(1024, 3), 2) AS max_gb,45       status46FROM dba_data_files47ORDER BY tablespace_name, file_name;48 49-- Not yet verified50PROMPT51PROMPT ==== 3 of 8: Fast Recovery Area usage ====52CLEAR COLUMNS53SET LINESIZE 140 PAGESIZE 100 TRIMOUT ON TAB OFF54COLUMN name           HEADING "LOCATION"       FORMAT A3055COLUMN limit_gb       HEADING "LIMIT GB"       FORMAT 999,990.956COLUMN used_gb        HEADING "USED GB"        FORMAT 999,990.957COLUMN reclaimable_gb HEADING "RECLAIMABLE GB" FORMAT 999,990.958COLUMN real_used_pct  HEADING "REAL USED %"    FORMAT 990.959COLUMN files          FORMAT 999,99060COLUMN file_type      HEADING "FILE TYPE"      FORMAT A2561COLUMN used_pct       HEADING "USED %"         FORMAT 990.9962COLUMN reclaim_pct    HEADING "RECLAIM %"      FORMAT 990.9963 64SELECT name,65       ROUND(space_limit / POWER(1024, 3), 1) AS limit_gb,66       ROUND(space_used / POWER(1024, 3), 1) AS used_gb,67       ROUND(space_reclaimable / POWER(1024, 3), 1) AS reclaimable_gb,68       ROUND(100 * (space_used - space_reclaimable) / NULLIF(space_limit, 0), 1) AS real_used_pct,69       number_of_files AS files70FROM v$recovery_file_dest;71 72SELECT file_type,73       percent_space_used AS used_pct,74       percent_space_reclaimable AS reclaim_pct,75       number_of_files AS files76FROM v$recovery_area_usage77ORDER BY percent_space_used DESC;78 79-- Not yet verified80PROMPT81PROMPT ==== 4 of 8: Temporary tablespace usage ====82CLEAR COLUMNS83SET LINESIZE 100 PAGESIZE 100 TRIMOUT ON TAB OFF84COLUMN tablespace_name HEADING "TABLESPACE"   FORMAT A2085COLUMN size_gb         HEADING "SIZE GB"      FORMAT 99,990.9986COLUMN allocated_gb    HEADING "ALLOCATED GB" FORMAT 99,990.9987COLUMN free_gb         HEADING "FREE GB"      FORMAT 99,990.9988 89SELECT tablespace_name,90       ROUND(tablespace_size / POWER(1024, 3), 2) AS size_gb,91       ROUND(allocated_space / POWER(1024, 3), 2) AS allocated_gb,92       ROUND(free_space / POWER(1024, 3), 2) AS free_gb93FROM dba_temp_free_space;94 95-- Not yet verified96PROMPT97PROMPT ==== 5 of 8: Sessions using temp space ====98CLEAR COLUMNS99SET LINESIZE 160 PAGESIZE 100 TRIMOUT ON TAB OFF100COLUMN sid_serial HEADING "SID,SERIAL#" FORMAT A14101COLUMN username   FORMAT A15102COLUMN program    FORMAT A30 TRUNCATE103COLUMN sql_id     FORMAT A13104COLUMN tablespace FORMAT A12105COLUMN segtype    HEADING "USE"     FORMAT A9106COLUMN used_mb    HEADING "USED MB" FORMAT 9,999,990107 108SELECT s.sid || ',' || s.serial# AS sid_serial,109       s.username, s.program, u.sql_id, u.tablespace, u.segtype,110       ROUND(u.blocks * t.block_size / 1048576) AS used_mb111FROM v$tempseg_usage u112JOIN v$session s ON s.saddr = u.session_addr113JOIN dba_tablespaces t ON t.tablespace_name = u.tablespace114ORDER BY u.blocks DESC;115 116-- Not yet verified117PROMPT118PROMPT ==== 6 of 8: Top 20 largest segments ====119CLEAR COLUMNS120SET LINESIZE 180 PAGESIZE 100 TRIMOUT ON TAB OFF121COLUMN owner           FORMAT A20122COLUMN segment_name    HEADING "SEGMENT"    FORMAT A35123COLUMN partition_name  HEADING "PARTITION"  FORMAT A25124COLUMN segment_type    HEADING "TYPE"       FORMAT A18125COLUMN tablespace_name HEADING "TABLESPACE" FORMAT A20126COLUMN size_gb         HEADING "SIZE GB"    FORMAT 999,990.99127 128SELECT * FROM (129  SELECT owner, segment_name, partition_name, segment_type, tablespace_name,130         ROUND(bytes / POWER(1024, 3), 2) AS size_gb131  FROM dba_segments132  ORDER BY bytes DESC133) WHERE ROWNUM <= 20;134 135-- Not yet verified136PROMPT137PROMPT ==== 7 of 8: Archived redo generated per day ====138CLEAR COLUMNS139SET LINESIZE 100 PAGESIZE 100 TRIMOUT ON TAB OFF140COLUMN day  FORMAT A10141COLUMN logs FORMAT 99,990142COLUMN gb   HEADING "GB" FORMAT 99,990.99143 144SELECT TO_CHAR(TRUNC(completion_time), 'YYYY-MM-DD') AS day,145       COUNT(*) AS logs,146       ROUND(SUM(blocks * block_size) / POWER(1024, 3), 2) AS gb147FROM v$archived_log148WHERE completion_time > TRUNC(SYSDATE) - 14149  AND dest_id = 1150GROUP BY TRUNC(completion_time)151ORDER BY TRUNC(completion_time);152 153-- Not yet verified154PROMPT155PROMPT ==== 8 of 8: Recycle bin contents by owner ====156CLEAR COLUMNS157SET LINESIZE 100 PAGESIZE 100 TRIMOUT ON TAB OFF158COLUMN owner   FORMAT A25159COLUMN objects FORMAT 999,990160COLUMN mb      HEADING "MB" FORMAT 9,999,990161 162SELECT owner,163       COUNT(*) AS objects,164       ROUND(SUM(space) * (SELECT TO_NUMBER(value) FROM v$parameter WHERE name = 'db_block_size') / 1048576) AS mb165FROM dba_recyclebin166GROUP BY owner167ORDER BY mb DESC;168

-- denrepo runbook: Space emergency
-- For ORA-01653, ORA-01652 and a filling FRA: find what's full, what's growing and what can be reclaimed before you add storage.
-- Source: https://denrepo.com/runbooks/space/

SET ECHO OFF

-- Not yet verified
PROMPT
PROMPT ==== 1 of 8: Tablespace usage ====
CLEAR COLUMNS
SET LINESIZE 130 PAGESIZE 100 TRIMOUT ON TAB OFF
COLUMN tablespace_name HEADING "TABLESPACE" FORMAT A25
COLUMN contents HEADING "TYPE" FORMAT A9
COLUMN used_gb  HEADING "USED GB"  FORMAT 999,990.99
COLUMN free_gb  HEADING "FREE GB"  FORMAT 999,990.99
COLUMN max_gb   HEADING "MAX GB"   FORMAT 999,990.99
COLUMN used_pct HEADING "USED %"   FORMAT 990.9

SELECT m.tablespace_name,
       t.contents,
       ROUND(m.used_space * t.block_size / POWER(1024, 3), 2) AS used_gb,
       ROUND((m.tablespace_size - m.used_space) * t.block_size / POWER(1024, 3), 2) AS free_gb,
       ROUND(m.tablespace_size * t.block_size / POWER(1024, 3), 2) AS max_gb,
       ROUND(m.used_percent, 1) AS used_pct
FROM dba_tablespace_usage_metrics m
JOIN dba_tablespaces t ON t.tablespace_name = m.tablespace_name
ORDER BY m.used_percent DESC;

-- Not yet verified
PROMPT
PROMPT ==== 2 of 8: Datafiles with size and autoextend limits ====
CLEAR COLUMNS
SET LINESIZE 180 PAGESIZE 200 TRIMOUT ON TAB OFF
COLUMN tablespace_name HEADING "TABLESPACE" FORMAT A20
COLUMN file_name       HEADING "FILE"       FORMAT A70
COLUMN size_gb         HEADING "SIZE GB"    FORMAT 99,990.99
COLUMN autoextensible  HEADING "AUTO"       FORMAT A4
COLUMN max_gb          HEADING "MAX GB"     FORMAT 99,990.99
COLUMN status          FORMAT A9

SELECT tablespace_name, file_name,
       ROUND(bytes / POWER(1024, 3), 2) AS size_gb,
       autoextensible,
       ROUND(maxbytes / POWER(1024, 3), 2) AS max_gb,
       status
FROM dba_data_files
ORDER BY tablespace_name, file_name;

-- Not yet verified
PROMPT
PROMPT ==== 3 of 8: Fast Recovery Area usage ====
CLEAR COLUMNS
SET LINESIZE 140 PAGESIZE 100 TRIMOUT ON TAB OFF
COLUMN name           HEADING "LOCATION"       FORMAT A30
COLUMN limit_gb       HEADING "LIMIT GB"       FORMAT 999,990.9
COLUMN used_gb        HEADING "USED GB"        FORMAT 999,990.9
COLUMN reclaimable_gb HEADING "RECLAIMABLE GB" FORMAT 999,990.9
COLUMN real_used_pct  HEADING "REAL USED %"    FORMAT 990.9
COLUMN files          FORMAT 999,990
COLUMN file_type      HEADING "FILE TYPE"      FORMAT A25
COLUMN used_pct       HEADING "USED %"         FORMAT 990.99
COLUMN reclaim_pct    HEADING "RECLAIM %"      FORMAT 990.99

SELECT name,
       ROUND(space_limit / POWER(1024, 3), 1) AS limit_gb,
       ROUND(space_used / POWER(1024, 3), 1) AS used_gb,
       ROUND(space_reclaimable / POWER(1024, 3), 1) AS reclaimable_gb,
       ROUND(100 * (space_used - space_reclaimable) / NULLIF(space_limit, 0), 1) AS real_used_pct,
       number_of_files AS files
FROM v$recovery_file_dest;

SELECT file_type,
       percent_space_used AS used_pct,
       percent_space_reclaimable AS reclaim_pct,
       number_of_files AS files
FROM v$recovery_area_usage
ORDER BY percent_space_used DESC;

-- Not yet verified
PROMPT
PROMPT ==== 4 of 8: Temporary tablespace usage ====
CLEAR COLUMNS
SET LINESIZE 100 PAGESIZE 100 TRIMOUT ON TAB OFF
COLUMN tablespace_name HEADING "TABLESPACE"   FORMAT A20
COLUMN size_gb         HEADING "SIZE GB"      FORMAT 99,990.99
COLUMN allocated_gb    HEADING "ALLOCATED GB" FORMAT 99,990.99
COLUMN free_gb         HEADING "FREE GB"      FORMAT 99,990.99

SELECT tablespace_name,
       ROUND(tablespace_size / POWER(1024, 3), 2) AS size_gb,
       ROUND(allocated_space / POWER(1024, 3), 2) AS allocated_gb,
       ROUND(free_space / POWER(1024, 3), 2) AS free_gb
FROM dba_temp_free_space;

-- Not yet verified
PROMPT
PROMPT ==== 5 of 8: Sessions using temp space ====
CLEAR COLUMNS
SET LINESIZE 160 PAGESIZE 100 TRIMOUT ON TAB OFF
COLUMN sid_serial HEADING "SID,SERIAL#" FORMAT A14
COLUMN username   FORMAT A15
COLUMN program    FORMAT A30 TRUNCATE
COLUMN sql_id     FORMAT A13
COLUMN tablespace FORMAT A12
COLUMN segtype    HEADING "USE"     FORMAT A9
COLUMN used_mb    HEADING "USED MB" FORMAT 9,999,990

SELECT s.sid || ',' || s.serial# AS sid_serial,
       s.username, s.program, u.sql_id, u.tablespace, u.segtype,
       ROUND(u.blocks * t.block_size / 1048576) AS used_mb
FROM v$tempseg_usage u
JOIN v$session s ON s.saddr = u.session_addr
JOIN dba_tablespaces t ON t.tablespace_name = u.tablespace
ORDER BY u.blocks DESC;

-- Not yet verified
PROMPT
PROMPT ==== 6 of 8: Top 20 largest segments ====
CLEAR COLUMNS
SET LINESIZE 180 PAGESIZE 100 TRIMOUT ON TAB OFF
COLUMN owner           FORMAT A20
COLUMN segment_name    HEADING "SEGMENT"    FORMAT A35
COLUMN partition_name  HEADING "PARTITION"  FORMAT A25
COLUMN segment_type    HEADING "TYPE"       FORMAT A18
COLUMN tablespace_name HEADING "TABLESPACE" FORMAT A20
COLUMN size_gb         HEADING "SIZE GB"    FORMAT 999,990.99

SELECT * FROM (
  SELECT owner, segment_name, partition_name, segment_type, tablespace_name,
         ROUND(bytes / POWER(1024, 3), 2) AS size_gb
  FROM dba_segments
  ORDER BY bytes DESC
) WHERE ROWNUM <= 20;

-- Not yet verified
PROMPT
PROMPT ==== 7 of 8: Archived redo generated per day ====
CLEAR COLUMNS
SET LINESIZE 100 PAGESIZE 100 TRIMOUT ON TAB OFF
COLUMN day  FORMAT A10
COLUMN logs FORMAT 99,990
COLUMN gb   HEADING "GB" FORMAT 99,990.99

SELECT TO_CHAR(TRUNC(completion_time), 'YYYY-MM-DD') AS day,
       COUNT(*) AS logs,
       ROUND(SUM(blocks * block_size) / POWER(1024, 3), 2) AS gb
FROM v$archived_log
WHERE completion_time > TRUNC(SYSDATE) - 14
  AND dest_id = 1
GROUP BY TRUNC(completion_time)
ORDER BY TRUNC(completion_time);

-- Not yet verified
PROMPT
PROMPT ==== 8 of 8: Recycle bin contents by owner ====
CLEAR COLUMNS
SET LINESIZE 100 PAGESIZE 100 TRIMOUT ON TAB OFF
COLUMN owner   FORMAT A25
COLUMN objects FORMAT 999,990
COLUMN mb      HEADING "MB" FORMAT 9,999,990

SELECT owner,
       COUNT(*) AS objects,
       ROUND(SUM(space) * (SELECT TO_NUMBER(value) FROM v$parameter WHERE name = 'db_block_size') / 1048576) AS mb
FROM dba_recyclebin
GROUP BY owner
ORDER BY mb DESC;