ORACLE series script 1: life-saving emergency session handling script

Background: database deadlock session soaring situation through the following expectations can quickly locate common locks, rapid intervention processing, restore database performance. Long-term operation and maintenance through the following statements? t or above database? Yes, it works every time.

First, query the deadlock SID and other information

SELECT l.session_id sid,s.serial#,l.locked_mode,l.oracle_username,l.os_user_name

S.machine,s.terminal,o.object_name,s.logon_time

FROM v$locked_object l, all_objects o, v$session s

WHERE l.object_id = o.object_id AND l.session_id = s.sid

ORDER BY sid, s.serial#

Second, locate blocking statements according to SID

SELECT / + PUSH_SUBQ /

Command_Type, Sql_Text, Sharable_Mem, Persistent_Mem, Runtime_Mem, Sorts,Version_Count, Loaded_Versions, Open_Versions, Users_Opening, Executions,Users_Executing, Loads, First_Load_Time, Invalidations, Parse_Calls,Disk_Reads, Buffer_Gets, Rows_Processed, SYSDATE Start_Time

SYSDATE Finish_Time,'>'| | Address Sql_Address,'N' Status

FROM V$sqlarea

WHERE Address = (SELECT Sql_Address FROM V$session WHERE Sid =? );

Third, kill the lock

-- kill lock (database level-- suitable for less urgent situations)

Select 'alter system kill session' | | chr (39) | | t2.sid | |','| | t2.serial# | | chr (39) | | 'immediate;'

From v$locked_object T1 recording session T2

Where t1.session_id=t2.sid order by t2.logon_time

Kill lock (operating system level-suitable for emergency situations)

Select 'kill-9' | | t3.spid

From v$locked_object T1 v$process session T2 v$process T3

Where t1.session_id=t2.sid And t2.paddr = t3.addr order by t2.logon_time

Attached daily regular session query sentence:

-- all session information

Select From v$session

Select Count () From v$session

-- session key information

Select USERNAME,status,state,MACHINE,logon_time From V$SESSION Order By username,MACHINE