Oracle Series Script 1: Life-saving emergency session processing scripts

background: A deadlock session in the database has soared. The following is expected to quickly locate common locks, rapid intervention processing, and database performance recovery. Long-term operation through the following statements? T more than the database, a.

One, the query out 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, based on SID location blocking statement
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-for less urgent situations)
Select ' Alter system kill session ' | | Chr (39) | | t2.sid| | ', ' | | t2.serial#| | Chr (39) | | immediate; '
From V$locked_object t1,v$session T2
where T1.session_id=t2.sid ORDER by t2.logon_time

--Kill Lock (operating system level-for emergency situations)
Select ' Kill-9 ' | | T3.spid
From V$locked_object t1,v$session T2, v$process T3
where t1.session_id=t2.sid and t2.paddr = t3.addr ORDER by T2.logon_time

A daily session query statement is attached:
--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

Oracle Series Script 1: Life-saving emergency session processing scripts