Querying SQL statements that Oracle is executing

--Query the SQL statement that Oracle is executing and the user who executes the statement

[SQL]View Plain copy

1. select b.sid oracleid,   
2.        b.username  Login Oracle user name,   
Li class= "alt" >       B.SERIAL#,  
3.         spid  operating system id,  
4.         paddr,  
5.        sql_text  executing sql,  
6.        b.machine  computer name   
7. FROM V$PROCESS A, V$SESSION B, V$SQLAREA C  
8. where a.addr = b.paddr  
9.    and b.sql_hash_value = c.hash_value  

--View the issuer's release procedure for executing SQL

[SQL]View Plain copy

2. SELECT osuser Computer Login status,
4. program that initiated the request,
6. USERNAME user name of the login system,
8. SCHEMANAME,
10. B.cpu_time spends Cpu time,
12. STATUS,
14. SQL executed by B.sql_text
16. From V$session A
18. Left JOIN v$sql B on a.sql_address = b.address
20. and A.sql_hash_value = B.hash_value
22. ORDER by b.cpu_time DESC

--Identify Oracle's current locked object

[SQL]View Plain copy

2. SELECT l.session_id SID,
4. s.serial#,
6. L.locked_mode lock mode,
8. L.oracle_username logged in user,
10. L.os_user_name Login Machine User name,
12. S.machine machine Name,
14. S.terminal End user Name,
16. O.object_name is locked object name,
18. S.logon_time Logon Database time
20. From V$locked_object L, all_objects O, v$session s
22. WHERE l.object_id = o.object_id
24. and l.session_id = S.sid
26. ORDER by Sid, S.serial#;

--kill drop the current lock object can be

Alter system kill session ' Sid, S.serial# ';

Querying SQL statements that Oracle is executing