Resources

Oracle Problems, with the Query to Run and the Warning You Could Have Had

Each guide takes one problem that pages an Oracle DBA: what the error says, the queries that show who or what caused it, the fixes in the order that does not make it worse, and how Radar Engine reports it before the error. The SQL runs on Oracle 12.2 or later and needs no Diagnostics Pack unless the page says so.

Capacity

• Tablespace full: ORA-01653
  The usage query that counts autoextend and the disk, what took the space, and the date it fills.
• Temp tablespace full: ORA-01652
  Who is using TEMP, why a statement spills, and the fixes without downtime.
• Undo tablespace full: ORA-30036
  Active, unexpired and expired undo, the transaction that holds it, and how much undo you need.
• Fast recovery area full: ORA-00257
  What fills the FRA, the fixes that do not break backups or a standby, and the warning a day earlier.
• ASM disk group full
  The number that counts (USABLE_FILE_MB), why a group is full with free space on the screen, and the fix from a new disk to rebalance.
• ORA-01555 snapshot too old
  The three causes, the V$UNDOSTAT query that tells them apart, and the fix for each.
• CPU at 100%
  The database's share against the host's, the sessions on CPU and in the Resource Manager queue, the statements that burn it, and the fix in order.
• ORA-01536: space quota exceeded
  The owner's quota against the tablespace, who has no limit, the fix that needs no new space, and the warning before the quota fills.
• ORA-65114: PDB storage limit reached
  Every PDB against its MAXSIZE, why empty file space counts, the fix from the limit to the datafiles, and the forecast.
• Database growing fast
  What grew and since when, the segments and datafiles behind it, the history Oracle does not keep, and the fix from archiving to compression.

Limits and memory

• ORA-00020: maximum number of processes exceeded
  Who holds the processes, how to get in when nobody can, and the leak that causes it.
• ORA-04031: shared pool out of memory
  A draining pool or a fragmented one, the hard-parse and literal-SQL causes, and the fixes without a restart.
• ORA-01000: maximum open cursors exceeded
  The session that leaks, the statement it holds open, the OPEN_CURSORS value from the data, and the leak seen days earlier.
• ORA-04036: PGA memory exceeds PGA_AGGREGATE_LIMIT
  Where the limit comes from, which sessions Oracle ends and why, the collection or the pool behind the climb, and the warning before the first termination.
• ORA-04030: out of process memory
  The process that grew and what it holds, the host numbers behind it, the instance limit that keeps one session from taking the server, and HugePages.
• ORA-12827: parallel server pool full
  The pool, who holds the servers, the silent downgrades to serial, and the fix with queuing and the Resource Manager.
• ORA-00059: maximum number of DB_FILES exceeded
  How close the datafile count is to the limit, the rate it climbs, the fix that needs a restart, and what to do until then.
• Logon storm
  Which pool reconnected at once and why, what it cost in processes and CPU, the listener rate limiter, and the pool settings that cannot storm.

Accounts

• ORA-28002: the password will expire
  Which accounts expire when, the reset that changes nothing for the application, gradual rollover, and the warning 30 days earlier.

Backups

• RMAN backup failed or overdue
  The last good backup of each kind, what the failed run said, how far the running one has got, which logs exist in one copy, and the fix in order.

Jobs

• Scheduler job failed, broken or stuck
  The job log with the error, the run that never ends, the chain step that stalled, the legacy jobs, and the fix in order.

Redo and archiving

• Archiver stuck: ORA-16014, ORA-16038
  The destination that failed and its error, the archiver processes, the switch that waits, the fix in order, and the warning before the database stops.

Data Guard

• Data Guard standby lag and gap
  Transport lag, apply lag or an archive gap: the queries on the standby and the primary that tell them apart, the fix in order, and the warning minutes after a standby starts falling behind.
• NOLOGGING with a standby: ORA-26040
  The unrecoverable changes on the primary, the nonlogged blocks on the standby, the RMAN repair, and FORCE LOGGING.

Locks and waits

• Blocking sessions: find the root blocker
  The query that ends the chain at its root, what the blocker is doing, when to kill it, and ORA-00060.
• Log file sync waits
  Slow redo disks or too many commits, the queries that tell them apart, the Data Guard case, and the fix in order.
• db file sequential read waits
  Slower single-block reads or more of them: the queries that tell slow storage from a plan that reads too much or a cache too small, and the fix in order.
• Oracle wait events, by class
  What each wait event means and its usual cause, from log file sync to gc buffer busy, with the guide where one exists.
• Log file switch (checkpoint incomplete)
  Redo logs too small or the database writer too slow: the groups and their state, the switch rate, the size Oracle recommends, and the fix.
• enq: TX - row lock contention
  The three reasons Oracle gives, the query that names the locked row and its holder, the idle holder, when to kill it, and the commit that belongs earlier.
• db file scattered read
  The full scan or fast full index scan behind the multiblock reads, the statement and the segment, whether the scan should exist, and the fix.
• direct path read
  A large scan, a parallel query or a sort spilling to temp: the statistics that tell them apart, the work areas, and the fix for each.
• buffer busy waits, read by other session
  The hot block and its class, the segment and the statement behind it, and the fix for an index edge, a hot row, a free list or a cold popular block.
• latch: cache buffers chains
  The hot chain and the statement that reads it millions of times, the plan behind it, and why the fix is the plan and not the latch.
• library cache lock, mutex X, cursor: pin S wait on X
  The parse waits: who holds the lock, the cursor being rebuilt, the parse rate and the version counts, and the fix from DDL timing to bind variables.
• free buffer waits
• log buffer space
• enq: TM - contention
• resmgr:cpu quantum

RAC

• RAC lost blocks: gc cr block lost
  The lost-block counters and transfer times, the waits that are the network and the ones that are contention, the interconnect check, and the fix.
• gc buffer busy acquire and release
  The hot block and its segment, busy or congested transfers in V$INSTANCE_CACHE_TRANSFER, the statements that pay for it, and the fix from services to GCS_SERVER_PROCESSES.

SQL

• SQL plan changed
  How to see the old plan without the Diagnostics Pack, why plans flip, and how to get the old plan back.
• One SQL taking the whole database
  The statement behind most of the active sessions, the CPU or the reads, what it waits on, and what to do with it.
• SQL suddenly slow, plan unchanged
  The five causes when the plan did not change, the V$SQL queries that tell them apart, and the fix for each.

Schema objects that run out

• ORA-14400: no partition for the key
  The date every range-partitioned table stops, the fixes from a new partition to interval partitioning, and the countdown.
• ORA-08004: sequence exceeds MAXVALUE
  Which sequences and identity columns run out, the date at the current rate, and the fix that checks the column and the application too.
• Invalid objects and unusable indexes
  Which ones and why, with the compile errors, the index partitions that hide, and the rebuild and recompile in the right order.

How the warnings work

Radar Engine is the detection layer of Scops: 75+ detectors that run every 5 minutes, hourly and daily, and report countdowns, pile-ups and anomalies instead of thresholds. Catching Oracle Problems Before They Become Outages explains how it decides, and the detector reference lists every detector with its thresholds. Scops needs no agents on the database servers and no Diagnostics Pack, and is free for 2 databases.