The 2 AM Oracle DBA Toolkit: 25 Queries to Save Before Production Breaks (19c to 26ai)

Published Sep 2026 · 13 min read

At 2 AM nobody reads documentation. The phone rings, the application team says "the database is slow" or "it is down", and you have about ten minutes before someone senior joins the bridge. What separates a calm DBA from a panicking one in that window is not knowledge — it is having the right queries already saved, tested, and in the order you will actually need them.

These are the 25 queries we keep in one file and run in this order. They are grouped by what the caller is really describing: sessions and blocking, space, redo and archiving, backups, Data Guard, RAC and ASM, and performance. Every query was run on Oracle Database 19c and Oracle AI Database 26ai; all of them use fixed views that have not changed in years, so they will also work on 12c and 21c. Save the page, or better, paste the queries into your own 2am.sql tonight.

First 60 seconds: where am I, and what is open?

1. Instance, version, role and open mode in one row per node

Before you touch anything, confirm you are on the database you think you are on, that it is really open, and whether it is a primary or a standby. Half of 2 AM mistakes are made on the wrong instance.

SELECT i.inst_id, i.instance_name, i.host_name, i.version_full, i.status,
       TO_CHAR(i.startup_time,'DD-MON HH24:MI') started,
       d.name db_name, d.open_mode, d.database_role, d.log_mode, d.cdb
FROM   gv$instance i CROSS JOIN v$database d
ORDER  BY i.inst_id;

2. Which PDBs are open, and is any of them in restricted mode?

"The database is up" from the root means nothing if the application's PDB is MOUNTED or RESTRICTED. Run this from CDB$ROOT.

SELECT con_id, name, open_mode, restricted,
       ROUND(total_size/1024/1024/1024,1) size_gb
FROM   v$pdbs
ORDER  BY con_id;

-- fix, if one is closed:
ALTER PLUGGABLE DATABASE sales_pdb OPEN;

Sessions and blocking: the real cause of most "it is hung" calls

3. Who is blocking whom right now, across all RAC nodes

Start with final_blocking_session, not blocking_session. In a chain of ten waiters you want the one session at the top, and this column gives it to you without walking the tree.

SELECT s.inst_id, s.sid, s.serial#, s.username, s.status, s.event,
       s.seconds_in_wait, s.blocking_instance, s.blocking_session,
       s.final_blocking_instance, s.final_blocking_session,
       s.sql_id, s.machine, s.program
FROM   gv$session s
WHERE  s.blocking_session IS NOT NULL
ORDER  BY s.seconds_in_wait DESC;

4. The blocking tree, indented, so you can explain it on the call

Managers understand a tree. This prints the root blocker at the left margin and every waiter indented beneath it, with what each one is waiting on.

SELECT LPAD(' ', 2*(LEVEL-1)) || sid || ' ' || username ||
       ' [' || status || '] ' || event || ' ' || seconds_in_wait || 's' AS blocking_tree
FROM   v$session
START  WITH blocking_session IS NULL
       AND sid IN (SELECT blocking_session FROM v$session WHERE blocking_session IS NOT NULL)
CONNECT BY PRIOR sid = blocking_session;

5. Kill the blocker properly — including on another RAC node

Get the application owner's agreement first, then kill the session with the instance number appended; you do not need to log in to the other node. If the session sits in KILLED status for minutes, the OS process is still cleaning up — find its spid and let PMON finish rather than killing it at the OS level.

ALTER SYSTEM KILL SESSION '4711,23891,@2' IMMEDIATE;   -- sid,serial#,@inst_id

-- still KILLED after a while? see what it is doing at the OS level
SELECT s.sid, s.serial#, s.status, p.spid, s.event
FROM   v$session s JOIN v$process p ON p.addr = s.paddr
WHERE  s.sid = 4711;

6. What exactly is that session running, and which plan?

Before killing anything, read the SQL. A blocker that is a batch job's legitimate ten-minute update is a very different conversation from an ad-hoc client that forgot to commit at 6 PM.

SELECT s.sid, s.sql_id, s.sql_child_number, q.plan_hash_value,
       q.executions, ROUND(q.elapsed_time/1e6) total_elapsed_s,
       TO_CHAR(s.sql_exec_start,'HH24:MI:SS') exec_started,
       SUBSTR(q.sql_text,1,90) sql_text
FROM   v$session s
JOIN   v$sql q ON q.sql_id = s.sql_id AND q.child_number = s.sql_child_number
WHERE  s.sid = &sid;

7. Are we about to run out of sessions or processes?

ORA-00020 "maximum number of processes exceeded" locks everyone out, including you. Check the high-water mark against the limit before it happens; if max_utilization is within a few percent of the limit, a connection storm from the application is the story, not the database.

SELECT resource_name, current_utilization, max_utilization, limit_value
FROM   v$resource_limit
WHERE  resource_name IN ('sessions','processes','transactions','enqueue_locks','parallel_max_servers');

8. How long will this long-running operation take?

The most useful answer you can give a bridge call is a time. Full scans, index builds, RMAN backups and Data Pump jobs all report here.

SELECT sid, serial#, opname, target,
       ROUND(sofar/NULLIF(totalwork,0)*100,1) pct_done,
       time_remaining remaining_s, elapsed_seconds, message
FROM   v$session_longops
WHERE  sofar < totalwork
ORDER  BY time_remaining DESC;

Space: ORA-01653, ORA-01654 and the FRA that quietly filled up

9. Tablespace usage that accounts for autoextend

Do not use dba_free_space for this; it ignores how far a datafile can still grow. This view reports usage against the real maximum, which is the only percentage that predicts an out-of-space error.

SELECT m.tablespace_name,
       ROUND(m.used_space*t.block_size/1024/1024/1024,1)     used_gb,
       ROUND(m.tablespace_size*t.block_size/1024/1024/1024,1) max_gb,
       ROUND(m.used_percent,1)                                pct_of_max
FROM   dba_tablespace_usage_metrics m
JOIN   dba_tablespaces t USING (tablespace_name)
ORDER  BY m.used_percent DESC;

10. Which datafiles cannot grow any further

The tablespace can look healthy while one datafile with autoextend off is the one the segment happens to be extending into. This lists every file that is either fixed-size or within 10% of its maximum.

SELECT tablespace_name, file_name, autoextensible,
       ROUND(bytes/1024/1024/1024,1)    gb,
       ROUND(maxbytes/1024/1024/1024,1) max_gb
FROM   dba_data_files
WHERE  autoextensible = 'NO'
   OR  (maxbytes > 0 AND bytes/maxbytes > 0.9)
ORDER  BY tablespace_name;

11. Add space the right way

On ASM or Oracle Managed Files, add a file and let it grow; on a filesystem, resize the existing one if the mount point has room. Either way, cap the maximum so one runaway load cannot fill the disk.

-- ASM / OMF
ALTER TABLESPACE users ADD DATAFILE SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 32767M;

-- filesystem
ALTER DATABASE DATAFILE '/u02/oradata/ORCL/users01.dbf' RESIZE 20G;
ALTER DATABASE DATAFILE '/u02/oradata/ORCL/users01.dbf' AUTOEXTEND ON NEXT 1G MAXSIZE 32767M;

12. Fast Recovery Area: full, and how much is reclaimable

A full FRA stops the archiver, which stops the database from taking new transactions — the symptom is "hung", the cause is space. The second query tells you what is filling it and whether Oracle can reclaim any of it on its own.

SELECT name,
       ROUND(space_limit/1024/1024/1024,1)       limit_gb,
       ROUND(space_used/1024/1024/1024,1)        used_gb,
       ROUND(space_reclaimable/1024/1024/1024,1) reclaimable_gb,
       number_of_files
FROM   v$recovery_file_dest;

SELECT file_type, percent_space_used, percent_space_reclaimable, number_of_files
FROM   v$recovery_area_usage
ORDER  BY percent_space_used DESC;

-- the honest fix is a backup, not a bigger FRA:
-- RMAN> BACKUP ARCHIVELOG ALL DELETE INPUT;
-- RMAN> DELETE NOPROMPT OBSOLETE;

13. TEMP and UNDO pressure

ORA-01555 and ORA-01652 at 2 AM usually mean a batch job is doing something it does not do during the day. Check how much temp is really free and how much undo is unexpired and cannot be reused yet.

SELECT tablespace_name,
       ROUND(tablespace_size/1024/1024) size_mb,
       ROUND(allocated_space/1024/1024) allocated_mb,
       ROUND(free_space/1024/1024)      free_mb
FROM   dba_temp_free_space;

SELECT tablespace_name, status, ROUND(SUM(bytes)/1024/1024) mb, COUNT(*) extents
FROM   dba_undo_extents
GROUP  BY tablespace_name, status
ORDER  BY 1, 2;

Redo and archiving: the "hang" that is really ORA-00257

14. Archiver destinations and log switches per hour

If a destination shows an error, that is your outage. The second query tells you whether tonight's redo rate is normal; thirty switches an hour when you normally do four means a job is generating redo it should not be.

SELECT dest_id, status, error, destination
FROM   v$archive_dest
WHERE  status <> 'INACTIVE';

SELECT TO_CHAR(first_time,'DD-MON HH24') hour, COUNT(*) switches,
       ROUND(SUM(blocks*block_size)/1024/1024/1024,1) redo_gb
FROM   v$archived_log
WHERE  first_time > SYSDATE - 1 AND dest_id = 1
GROUP  BY TO_CHAR(first_time,'DD-MON HH24')
ORDER  BY 1;

15. Redo log groups: size, status and "checkpoint not complete"

Every group ACTIVE with none INACTIVE means the database is waiting for DBWR to catch up before it can reuse a log — the classic undersized-redo symptom. The fix is bigger or more groups, not a restart.

SELECT l.thread#, l.group#, l.sequence#, ROUND(l.bytes/1024/1024) mb,
       l.members, l.status, l.archived
FROM   v$log l
ORDER  BY l.thread#, l.group#;

-- add larger groups online, then drop the small ones once INACTIVE
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 11 SIZE 2G;

16. The alert log, from SQL, last 60 minutes

You do not need to find the diag directory. This view reads the XML alert log directly and lets you filter for errors, which is faster than paging through tail output on a busy system.

SELECT TO_CHAR(originating_timestamp,'HH24:MI:SS') at, message_text
FROM   v$diag_alert_ext
WHERE  originating_timestamp > SYSDATE - 1/24
  AND  (message_text LIKE '%ORA-%' OR message_text LIKE '%Checkpoint not complete%')
ORDER  BY originating_timestamp;

Backups: was last night's backup real, and can I restore?

17. RMAN job history for the week

Before any recovery decision, know when the last successful full and archive-log backups finished and how big they were. A backup that "ran" with status COMPLETED WITH WARNINGS deserves a look at the log before you rely on it.

SELECT session_key, input_type, status,
       TO_CHAR(start_time,'DD-MON HH24:MI') started,
       ROUND(elapsed_seconds/60) mins,
       ROUND(output_bytes/1024/1024/1024,1) out_gb, output_device_type
FROM   v$rman_backup_job_details
WHERE  start_time > SYSDATE - 7
ORDER  BY start_time DESC;

18. Files that need recovery, files stuck in backup mode, and flashback headroom

Three quick checks that change the plan: a datafile needing media recovery, a datafile someone left in hot-backup mode after a failed script, and how far back Flashback Database can actually take you — which is often much less than the retention target.

SELECT file#, error, change#, time FROM v$recover_file;

SELECT file#, status, change#, time FROM v$backup WHERE status = 'ACTIVE';

SELECT d.flashback_on, TO_CHAR(f.oldest_flashback_time,'DD-MON HH24:MI') oldest_flashback,
       f.retention_target/60 target_hours,
       ROUND(f.flashback_size/1024/1024/1024,1) flashback_gb
FROM   v$database d CROSS JOIN v$flashback_database_log f;

-- and from RMAN, before you promise anything:
-- RMAN> RESTORE DATABASE PREVIEW SUMMARY;
-- RMAN> REPORT NEED BACKUP;

Data Guard: is the standby actually applying?

19. Transport lag, apply lag and the apply process

Run this on the standby. A transport lag that grows while apply lag stays flat means the network or the primary's archiver; an apply lag that grows while transport stays at zero means the standby cannot keep up or apply has stopped. The second query shows whether a redo apply process exists at all.

SELECT name, value, TO_CHAR(datum_time,'HH24:MI:SS') as_of
FROM   v$dataguard_stats
WHERE  name IN ('transport lag','apply lag','apply finish time');

SELECT name, role, action, thread#, sequence#, block#
FROM   v$dataguard_process
WHERE  role LIKE '%apply%' OR role LIKE '%receiver%' OR role LIKE '%RFS%'
ORDER  BY role;

SELECT * FROM v$archive_gap;

-- Broker view of the same thing, from either side:
-- DGMGRL> SHOW CONFIGURATION LAG;
-- DGMGRL> VALIDATE DATABASE orcl_stby;

RAC and ASM: is the cluster healthy underneath the database?

20. ASM diskgroup space and any disk that is not where it should be

A diskgroup at 95% with a disk OFFLINE is two problems, and the second one matters more: if the disk repair timer expires, ASM drops it and starts a rebalance you did not plan for. The second query returns zero rows on a healthy system.

SELECT name, state, type, ROUND(total_mb/1024) total_gb, ROUND(free_mb/1024) free_gb,
       ROUND(usable_file_mb/1024) usable_gb, offline_disks
FROM   v$asm_diskgroup;

SELECT group_number, disk_number, name, path, mount_status, header_status, mode_status, state
FROM   v$asm_disk
WHERE  mount_status <> 'CACHED' OR header_status <> 'MEMBER' OR mode_status <> 'ONLINE';

21. Cluster resources, services and the global-cache waits that say "interconnect"

From the shell as the Grid owner, the first two commands tell you whether every resource and service is where it should be. The SQL then shows whether the nodes are spending their time waiting on each other, which points at the interconnect or at an application that should have been pinned to one node.

crsctl check cluster -all
crsctl stat res -t
srvctl status database -d ORCL -v
srvctl status service -d ORCL

SELECT inst_id, event, total_waits, ROUND(time_waited_micro/1e6) waited_s
FROM   gv$system_event
WHERE  event LIKE 'gc %'
ORDER  BY time_waited_micro DESC
FETCH FIRST 10 ROWS ONLY;

Performance: "it is slow" — in five minutes, with evidence

22. What are active sessions waiting on, right now (no licence needed)

This is a free, instantaneous picture of the database. Fifty sessions on enq: TX - row lock contention is a blocking problem; fifty on db file sequential read is I/O or a plan change; fifty on log file sync is redo. The answer decides which section of this page you go to next.

SELECT event, wait_class, COUNT(*) sessions
FROM   v$session
WHERE  status = 'ACTIVE' AND type = 'USER' AND wait_class <> 'Idle'
GROUP  BY event, wait_class
ORDER  BY sessions DESC;

23. Top SQL in the last 15 minutes from ASH

Active Session History needs the Diagnostics Pack on Enterprise Edition, but if you have it, this is the single most valuable query on the page. One sql_id taking 60% of samples is your culprit; look at when it was first seen and compare it with when the call came in.

SELECT sql_id, COUNT(*) samples,
       ROUND(COUNT(*)*100/SUM(COUNT(*)) OVER (),1) pct,
       TO_CHAR(MIN(sample_time),'HH24:MI') first_seen,
       TO_CHAR(MAX(sample_time),'HH24:MI') last_seen,
       MAX(event) KEEP (DENSE_RANK LAST ORDER BY COUNT(*)) top_event
FROM   v$active_session_history
WHERE  sample_time > SYSDATE - 15/1440 AND sql_id IS NOT NULL
GROUP  BY sql_id
ORDER  BY samples DESC
FETCH FIRST 10 ROWS ONLY;

24. Did the execution plan change this week?

Most sudden slowness on a stable system is a plan change after statistics were gathered or a bind value skewed. This compares the plan hash and per-execution cost across the week's AWR snapshots (Diagnostics Pack again). If a new plan_hash_value appears the same day the problem started, you have your answer and a quick fix: load the old plan as a SQL plan baseline.

SELECT TO_CHAR(sn.begin_interval_time,'DD-MON HH24:MI') snap,
       st.plan_hash_value,
       st.executions_delta execs,
       ROUND(st.elapsed_time_delta/NULLIF(st.executions_delta,0)/1000) ms_per_exec,
       ROUND(st.buffer_gets_delta/NULLIF(st.executions_delta,0))       gets_per_exec
FROM   dba_hist_sqlstat st
JOIN   dba_hist_snapshot sn
  ON   sn.snap_id = st.snap_id AND sn.dbid = st.dbid AND sn.instance_number = st.instance_number
WHERE  st.sql_id = '&sql_id'
  AND  sn.begin_interval_time > SYSDATE - 7
ORDER  BY sn.begin_interval_time;

-- see the plan that is running now, with real row counts
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', NULL, 'ALLSTATS LAST'));

The morning after: the four checks before you close the ticket

25. Invalid objects, failed jobs, expiring accounts and the current patch level

Whatever you did at 2 AM, these four queries tell you whether you left anything behind and give you the facts the incident report will ask for. The patch query is the one you will be asked for first when you raise a Service Request.

SELECT owner, object_type, COUNT(*) invalid
FROM   dba_objects WHERE status = 'INVALID'
GROUP  BY owner, object_type ORDER BY invalid DESC;

SELECT owner, job_name, status, error#, TO_CHAR(actual_start_date,'DD-MON HH24:MI') started
FROM   dba_scheduler_job_run_details
WHERE  log_date > SYSDATE - 1 AND status <> 'SUCCEEDED'
ORDER  BY log_date DESC;

SELECT username, account_status, expiry_date
FROM   dba_users
WHERE  oracle_maintained = 'N' AND expiry_date < SYSDATE + 7;

SELECT patch_id, patch_type, action, status, description,
       TO_CHAR(action_time,'DD-MON-YYYY') applied
FROM   dba_registry_sqlpatch
ORDER  BY action_time DESC
FETCH FIRST 5 ROWS ONLY;

Common mistakes at 2 AM

• Restarting before reading. A bounce destroys v$session, v$sql and the blocking tree — the evidence you need for the incident report. Run queries 3, 6 and 22 first; they take twenty seconds.
• Killing the wrong end of the chain. Killing a waiter frees nothing. Use final_blocking_session from query 3.
• Adding space to the FRA instead of taking a backup. It buys an hour and hides the fact that archive logs are not being backed up or deleted.
• Trusting dba_free_space. It does not know about autoextend. Query 9 does.
• Running these on the wrong container. dba_ views are per-PDB; from the root use cdb_ views or switch containers with ALTER SESSION SET CONTAINER.

FAQ

Which of these queries need the Diagnostics Pack licence?

Only queries 23 and 24, which read v$active_session_history and dba_hist_*. Everything else uses free fixed views available in every edition. Without the pack, query 22 plus v$sql ordered by elapsed time gives you most of the same picture, and Statspack still works for history.

Do these work on Oracle AI Database 26ai and inside a PDB?

Yes. All 25 were run on 26ai and 19c; the views used here have not changed. Run them from CDB$ROOT to see the whole container, and note that dba_ views show only the current container while cdb_ views show all of them with a CON_ID column.

What is the safest way to kill a session on RAC?

Use ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id' IMMEDIATE from any node; the @inst_id part routes the kill to the right instance. Never kill background processes, and if the session stays in KILLED status, let PMON clean it up rather than killing the OS process, unless you have confirmed it is a client shadow process.

What privileges do I need?

To read every view here, SELECT ANY DICTIONARY or the SELECT_CATALOG_ROLE role is enough. Killing sessions and adding datafiles need ALTER SYSTEM and ALTER TABLESPACE. The crsctl and srvctl commands run as the Grid Infrastructure owner on the cluster node.

Keep it in one file

Put all 25 in 2am.sql with SET LINES 250 PAGES 100 at the top, a PROMPT line before each query naming it, and keep the file in the same place on every server. The next 2 AM call will still be a 2 AM call, but you will have the answer before the second person joins the bridge.

If you would rather practise these on a real cluster than on your production system, our Oracle 26ai DBA training runs each of these scenarios as a live lab — blocking, space, archiver, RMAN, Data Guard and RAC — and it sits alongside the rest of our live Oracle DBA training programs. The standby section goes deeper in our Data Guard scenarios with commands, and the RAC section in the RAC scenario guide. For the column-by-column definitions of every view used above, keep Oracle's Database Reference open in the next tab.