Oracle Database Troubleshooting: Common Scripts and Diagnostic Procedures

In many Oracle environments, DBAs are the first to receive emergency calls when an application fails, often facing repeated calls and urgent pressure. The article emphasizes the importance of a calm mindset, systematic diagnosis, and the use of ready‑made scripts to reduce downtime.

Check Operating System Load After logging into the database server, verify CPU, memory, and I/O using OS‑specific commands such as top, topas, vmstat, and iostat.

Inspect Wait Events Waiting events reveal the majority of performance problems. The following script lists each wait event, its occurrence count, and total wait time while filtering out common idle events:

--墨天轮 wait_event</code>
<code>col event for a45</code>
<code>SELECT inst_id, EVENT, SUM(DECODE(WAIT_TIME,0,0,1)) "Prev", SUM(DECODE(WAIT_TIME,0,1,0)) "Curr", COUNT(*) "Tot", SUM(SECONDS_IN_WAIT) SECONDS_IN_WAIT</code>
<code>FROM GV$SESSION_WAIT</code>
<code>WHERE event NOT IN ('smon timer','pmon timer','rdbms ipc message','SQL*Net message from client','gcs remote message')</code>
<code>AND event NOT LIKE '%idle%'</code>
<code>AND event NOT LIKE '%Idle%'</code>
<code>AND event NOT LIKE '%Streams AQ%'</code>
<code>GROUP BY inst_id, EVENT</code>
<code>ORDER BY 1,5 DESC

Familiarity with common problematic wait events (e.g., library cache lock, row cache lock, buffer busy waits, log file sync) allows rapid identification of the root cause.

Find Sessions by Wait Event Once an abnormal wait event is identified, retrieve the sessions that are experiencing it:

--墨天轮 session_by_event</code>
<code>SELECT sid, s.serial#, spid, event, sql_id, seconds_in_wait ws, row_wait_obj# obj, s.username, s.machine, BLOCKING_INSTANCE||'.'||blocking_session b_sess</code>
<code>FROM v$session s, v$process p</code>
<code>WHERE event='&event_name' AND s.paddr = p.addr ORDER BY 6;

Query Session Details To obtain detailed information about a particular session (SQL_ID, login time, etc.), use:

--墨天轮 session_by_sid</code>
<code>SELECT sid, s.serial#, spid, event, sql_id, PREV_SQL_ID, seconds_in_wait ws, row_wait_obj# obj, s.username, s.machine, module, blocking_session b_sess, logon_time</code>
<code>FROM v$session s, v$process p</code>
<code>WHERE sid='&sid' AND s.paddr = p.addr;

Object Information With the object ID obtained from the previous queries, retrieve its metadata:

--墨天轮 obj_info</code>
<code>col OBJECT_NAME for a30</code>
<code>SELECT owner, object_name, subobject_name, object_type FROM dba_objects WHERE object_id=&oid;

SQL Text Lookup Find the full SQL text for a given SQL_ID or HASH_VALUE:

--墨天轮 sql_text</code>
<code>SELECT sql_id, SQL_fullTEXT FROM v$sqlarea WHERE (sql_id='&sqlid' OR hash_value=to_number('&hashvale')) AND rownum < 2;

Blocking Session Analysis Identify how many sessions are blocked by each blocking session:

--墨天轮 blocking_sess</code>
<code>SELECT COUNT(*), blocking_session FROM v$session WHERE blocking_session IS NOT NULL GROUP BY blocking_session;

Lock Inspection Query locks held by a specific session and list long‑running TM/TX locks:

--墨天轮 lock</code>
<code>set linesize 180</code>
<code>col username for a15</code>
<code>col owner for a15</code>
<code>col OBJECT_NAME for a30</code>
<code>col SPID for a10</code>
<code>SELECT /*+rule*/ SESSION_ID, OBJECT_ID, ORACLE_USERNAME, OS_USER_NAME, PROCESS, LOCKED_MODE FROM gv$locked_object WHERE session_id=&sid;</code>
<code>SELECT /*+rule*/ * FROM v$lock WHERE ctime > 100 AND type IN ('TX','TM') ORDER BY 3,9,1;</code>
<code>SELECT /*+rule*/ s.sid, p.spid, l.type, ROUND(MAX(l.ctime)/60,0) lock_min, s.sql_id, s.USERNAME, b.owner, b.object_type, b.object_name FROM v$session s, v$process p, v$lock l, v$locked_object o, dba_objects b WHERE o.SESSION_ID=s.sid AND s.sid=l.sid AND o.OBJECT_ID=b.OBJECT_ID AND s.paddr = p.addr AND l.ctime > 100 AND l.type IN ('TX','TM','FB') GROUP BY s.sid, p.spid, l.type, s.sql_id, s.USERNAME, b.owner, b.object_type, b.object_name ORDER BY 9,1,3;

Preserve Evidence When analysis may take a long time or external help is needed, capture a system state dump and a hang analysis:

--systemstate dump</code>
<code>sqlplus -prelim / as sysdba</code>
<code>oradebug setmypid</code>
<code>oradebug unlimit;</code>
<code>oradebug dump systemstate 266;</code>
<code>--hanganalyze</code>
<code>oradebug dump hanganalyze 3;</code>
<code>oradebug tracefile_name;

Killing Sessions After pinpointing the offending sessions, use the following scripts to terminate them (by SID, SQL_ID, wait event, or user) and optionally kill all processes with LOCAL=NO:

--墨天轮 kill_sess</code>
<code>SELECT /*+ rule */ sid, s.serial#, 'kill -9 '||spid, event, blocking_session b_sess FROM v$session s, v$process p WHERE sid='&sid' AND s.paddr = p.addr ORDER BY 1;</code>
<code>SELECT /*+ rule */ sid, s.serial#, 'kill -9 '||spid FROM v$session s, v$process p WHERE sql_id='&sql_id' AND s.paddr = p.addr ORDER BY 1;</code>
<code>SELECT /*+ rule */ sid, s.serial#, 'kill -9 '||spid FROM v$session s, v$process p WHERE event='&event' AND s.paddr = p.addr ORDER BY 1;</code>
<code>SELECT /*+ rule */ sid, s.serial#, 'kill -9 '||spid FROM v$session s, v$process p WHERE username='&username' AND s.paddr = p.addr ORDER BY 1;</code>
<code>ps -ef|grep LOCAL=NO|grep $ORACLE_SID|grep -v grep|awk '{print $2}' |xargs kill -9

Database Restart If static parameters or memory issues require a restart, follow the standard shutdown/startup sequence without spending time on deep root‑cause analysis:

tail -f alert_.log</code>
<code>alter system checkpoint;</code>
<code>alter system switch logfile;</code>
<code>shutdown immediate;</code>
<code>startup

SecureCRT Button Bar Tip Store frequently used scripts in SecureCRT’s Button Bar to execute them with a single click, but avoid adding DDL or other destructive commands to prevent accidental execution.

Overall, the article provides a collection of ready‑to‑use scripts for Oracle DBAs to diagnose slow or hung applications, capture diagnostic data, kill problematic sessions, and, when necessary, restart the database, emphasizing the need to understand each script before use and to adapt them to the specific environment.