查詢Oracle正在執行的sql語句

?

--查詢Oracle正在執行的sql語句及執行該語句的用戶

SELECT?b.sid?oracleID,??
???????b.username?登錄Oracle用戶名,??
???????b.serial#,??
???????spid?操作系統ID,??
???????paddr,??
???????sql_text?正在執行的SQL,??
???????b.machine?計算機名??
FROM?v$process?a,?v$session?b,?v$sqlarea?c??
WHERE?a.addr?=?b.paddr??
???AND?b.sql_hash_value?=?c.hash_value??

--查看正在執行sql的發起者的發放程序

SELECT?OSUSER?電腦登錄身份,??
???????PROGRAM?發起請求的程序,??
???????USERNAME?登錄系統的用戶名,??
???????SCHEMANAME,??
???????B.Cpu_Time?花費cpu的時間,??
???????STATUS,??
???????B.SQL_TEXT?執行的sql??
FROM?V$SESSION?A??
LEFT?JOIN?V$SQL?B?ON?A.SQL_ADDRESS?=?B.ADDRESS??
???????????????????AND?A.SQL_HASH_VALUE?=?B.HASH_VALUE??
ORDER?BY?b.cpu_time?DESC??

--查出oracle當前的被鎖對象

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#;??

?

--kill掉當前的鎖對象可以為

alter system kill session 'sid， s.serial#‘;