Current SQL’s running on Instance
This below query useful to find out current running SQL statements of instance, These SQL’s can be either DML or DDL statements or even Select queries. This query useful to track whether that specific SQL is still running or not.
set linesize 400
set pagesize 100
col oracle_usrename for a10
col session_status for a8
col os_username for a12
col os_pid for 99999
col session_machine for a15
col session_program for a15
col current_sql for a120
SELECT
s.sid sid
, s.serial# serial_id
, s.status session_status
, s.username oracle_username
, s.osuser os_username
, p.spid os_pid
, s.machine session_machine
, s.program session_program
, s.client_info
, SUBSTR(sa.sql_text, 1, 1024) current_sql
FROM
v$process p
, v$session s
, v$sqlarea sa
WHERE
p.addr (+) = s.paddr
AND s.sql_address = sa.address(+)
AND s.sql_hash_value = sa.hash_value(+)
AND s.audsid <> userenv('SESSIONID')
AND s.username IS NOT NULL
AND s.status = 'ACTIVE'
ORDER BY sid;
It results output with username, sid, serial, Machine information including entire SQL Text. Sample output is shown below only for the Query, remaining output was encrypted.
