SQL

查询正在执行的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