查询正在执行的sql
SELECT b.sid oracleID,
b.username 登录Oracle用户名,
b.serial#,
spid 操作系统ID,
paddr,
sql_text 正在执行的SQL,
c.FIRST_LOAD_TIME 开始执行时间,
b.machine 计算机名,
'ALTER SYSTEM KILL SESSION '''||B.SID ||','||b.serial# ||''';'
FROM v$process a, v$session b, v$sqlarea c
WHERE a.addr = b.paddr
AND b.sql_hash_value = c.hash_value;
查看阻塞源
Select b_s.SQL_ID blocked_sql_id,
'节点 ' || a_s.INST_ID || ' session ' || a_s.sid || ',' || a_s.SERIAL# ||' 阻塞了 节点 ' || b_s.INST_ID || ' session ' || b_s.SID || ',' ||
b_s.SERIAL# blockinfo,
a_s.INST_ID,
a_s.SID,
a_s.SCHEMANAME,
a_s.MODULE,
a_s.STATUS,
'后为被阻塞信息',
b_s.INST_ID blocked_inst_id,
b_s.SID blocked_sid,
b_s.SCHEMANAME blocked_SCHEMANAME,
b_s.EVENT blocked_event,
b_s.MODULE blocked_module,
b_s.STATUS blocked_status,
b_s.SQL_ID blocked_sql_id,
obj.owner blocked_owner,
obj.object_name blocked_object_name,
obj.OBJECT_TYPE blocked_OBJECT_TYPE,
case
when b_s.ROW_WAIT_OBJ# <> -1 then
dbms_rowid.rowid_create(1,
obj.DATA_OBJECT_ID,
b_s.ROW_WAIT_FILE#,
b_s.ROW_WAIT_BLOCK#,
b_s.ROW_WAIT_ROW#)
else
'-1'
end blocked_rowid, --被阻塞数据的rowid
decode(obj.object_type,
'TABLE',
'select * from ' || obj.owner || '.' || obj.object_name ||
' where rowid=''' ||
dbms_rowid.rowid_create(1,
obj.DATA_OBJECT_ID,
b_s.ROW_WAIT_FILE#,
b_s.ROW_WAIT_BLOCK#,
b_s.ROW_WAIT_ROW#) || '''',
NULL) blocked_data_querysql
from gv$session a_s,
gv$session b_s,
dba_objects obj
where b_s.BLOCKING_INSTANCE is not null
and b_s.BLOCKING_SESSION is not null
and a_s.INST_ID = b_s.BLOCKING_INSTANCE
and a_s.SID = b_s.BLOCKING_SESSION
and b_s.ROW_WAIT_OBJ# = obj.object_id(+)
order by a_s.inst_id, a_s.sid;
查看Oracle版本号
select *from v$version
