能看懂的拿
---locked obj
select object_name,machine,s.sid,s.serial#,s.inst_id
from gv$locked_object l,dba_objects o ,gv$session s
where l.object_id = o.object_id and l.session_id=s.sid and l.inst_id =s.inst_id
order by 5,3
---waiting time too long
select * from (select sid,event,p1,p2,p3,p1text,WAIT_TIME,SECONDS_IN_WAIT from gv$session_wait where wait_class# <> 6
order by wait_time desc) where rownum <=10
---rac session active
select b.SPID,a.SID,a.INST_ID,a.OSUSER,a.MACHINE,a.TERMINAL,a.PROGRAM,a.ACTION,a.*,b.* from gv$session a,gv$process b
where a.username is not null and a.status = 'ACTIVE' and a.PADDR=b.ADDR
order by a.logon_time, a.INST_ID,a.sid
---event
select action,program,module,event,wait_class,wait_time,
row_wait_obj#,
row_wait_file#,
row_wait_block#,
sql_address,sql_id,sql_hash_value,gv$session.* from gv$session
where username is not null and status='ACTIVE'
order by logon_time, sid
---oracle9i superlock
SELECT bs.username "Blocking User", bs.username "DB User",
ws.username "Waiting User", bs.SID "SID", ws.SID "WSID",
bs.serial# "Serial#", bs.sql_address "address",
bs.sql_hash_value "Sql hash", bs.program "Blocking App",
ws.program "Waiting App", bs.machine "Blocking Machine",
ws.machine "Waiting Machine", bs.osuser "Blocking OS User",
ws.osuser "Waiting OS User", bs.serial# "Serial#",
ws.serial# "WSerial#",
DECODE (wk.TYPE,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'USER Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL USER LOCK',
'DX', 'Distributed Xaction',
'CF', 'Control FILE',
'IS', 'Instance State',
'FS', 'FILE SET',
'IR', 'Instance Recovery',
'ST', 'Disk SPACE Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'LOG START OR Switch',
'RW', 'ROW Wait',
'SQ', 'Sequence Number',
'TE', 'Extend TABLE',
'TT', 'Temp TABLE',
wk.TYPE
) lock_type,
DECODE (hk.lmode,
0, 'None',
1, 'NULL',
2, 'ROW-S (SS)',
3, 'ROW-X (SX)',
4, 'SHARE',
5, 'S/ROW-X (SSX)',
6, 'EXCLUSIVE',
TO_CHAR (hk.lmode)
) mode_held,
DECODE (wk.request,
0, 'None',
1, 'NULL',
2, 'ROW-S (SS)',
3, 'ROW-X (SX)',
4, 'SHARE',
5, 'S/ROW-X (SSX)',
6, 'EXCLUSIVE',
TO_CHAR (wk.request)
) mode_requested,
TO_CHAR (hk.id1) lock_id1, TO_CHAR (hk.id2) lock_id2,
DECODE
(hk.BLOCK,
0, 'NOT Blocking', /**//* Not blocking any other processes */
1, 'Blocking', /**//* This lock blocks other processes */
2, 'Global', /**//* This lock is global, so we can't tell */
TO_CHAR (hk.BLOCK)
) blocking_others
FROM v$lock hk, v$session bs, v$lock wk, v$session ws
WHERE hk.BLOCK = 1
AND hk.lmode != 0
AND hk.lmode != 1
AND wk.request != 0
AND wk.TYPE(+) = hk.TYPE
AND wk.id1(+) = hk.id1
AND wk.id2(+) = hk.id2
AND hk.SID = bs.SID(+)
AND wk.SID = ws.SID(+)
AND (bs.username IS NOT NULL)
AND (bs.username <> 'SYSTEM')
AND (bs.username <> 'SYS')
ORDER BY 1
----superlock
select 'alter system kill session ''' || a.SID || ',' || a.SERIAL# || ''';',
a.sid,
a.inst_id,
a.final_blocking_session,
a.final_blocking_instance,
action,
program,
module,
event,
wait_class,
wait_time,
row_wait_obj#,
(select object_name from dba_objects where object_id = row_wait_obj#) objname,
row_wait_file#,
(select file_name from dba_data_files where file_id = row_wait_file#) filename,
row_wait_block#,
dbms_rowid.rowid_create(1, (select data_object_id from dba_objects where object_id = row_wait_obj#), ROW_WAIT_FILE#, ROW_WAIT_BLOCK#,
ROW_WAIT_ROW#) rid,
sql_address,
sql_id,
sql_hash_value,
a.*
from gv$session a
where a.final_blocking_session is not null
or a.sid || a.inst_id in
(select final_blocking_session || final_blocking_instance
from gv$session)
order by a.logon_time, a.sid
---sql-locking-session
select 'alter system kill session ''' || a.SID || ',' || a.SERIAL# || ''';',
(select to_char( wm_concat( b.SPID )) from gv$process b where a.PADDR = b.ADDR ) SPID,
a.sid,
a.inst_id,
a.final_blocking_session,
a.final_blocking_instance,
action,
program,
module,
event,
wait_class,
wait_time,
row_wait_obj#,
(select object_name from dba_objects where object_id = row_wait_obj#) objname,
row_wait_file#,
(select file_name from dba_data_files where file_id = row_wait_file#) filename,
row_wait_block#,
sql_address,
sql_id,
sql_hash_value,
a.*
from gv$session a
where a.final_blocking_session is not null
or a.sid || a.inst_id in
(select final_blocking_session || final_blocking_instance
from gv$session)
order by a.logon_time, a.sid
---Active sessions
select * from gv$session
where username is not null and status = 'ACTIVE'
order by logon_time, sid
=================================