Below query can be used to find the currently running active queries in the Oracle database. This is applicable to RAC and Single Instance database.
set pages 333 lines 255
set wrap off
col "Sid/Serial" for a12
col UnixPid for a8
col OraUser for a8
col OsUser for a8
col machine for a18
col Minutes for 99990.9
col "ClientProgram" format a28
col "ServerProgram" format a32
col "Program" format a25
col "Module" format a25
col "LastSQL" format a40
col "MBmem" format 99999
BREAK ON REPORT
COMPUTE SUM LABEL "Total PGA MB" OF "MBmem" ON REPORT
select distinct
s.INST_ID "INST_ID",s.sid||','||s.serial# "Sid/Serial", s.sql_id,p.spid "UnixPid",s.username "OraUser",s.osuser "OsUser", s.machine,
s.last_call_et/60 "Minutes",s.status, nvl(s.program,'~'||p.program) "Program", s.module "Module",
q.sql_text "LastSQL", p.pga_alloc_mem/1024/1024 "MBmem"
from gv$session s,gv$process p, gv$sql q
where s.INST_ID=p.INST_ID and
s.paddr=p.addr and
s.sql_id=q.sql_id(+)
and s.username is not null
and s.sql_id is not null
and s.status <> 'INACTIVE'
order by "INST_ID","Minutes" desc;
-----------------------------------------------------------------------------------------------------------
1 1597,54853 fnqd0du3r6czf 31161 PCL oracle xxxxxxx 1092.4 ACTIVE sqlplus@XXXXXX MT_PUR_BLOCKK DELETE FROM HR.ORD 137
1 1532,53286 abb47cudcu3kk 1711 PCL oracle xxxxxxx 23.6 ACTIVE oracle@xxxxxxx ED_PARENT_PURGE UPDATE HR 5
1 1489,17415 adnf0kn7g835x 2903 GG oracle xxxxxxx 0.0 ACTIVE replicat@xxxxx OGG-MTREP-OPEN_DATA_SO INSERT INTO "HR"."CE 9
1 1392,45034 an6fy8p65ygxf 30553 GG oracle xxxxxxx 0.0 ACTIVE replicat@xxxxx OGG-MTREP-OPEN_DATA_SO INSERT INTO "HR"."TA 1741
------
Total PGA 1913
Comments
Post a Comment