To Find ONLY EXECUTING sql :

set lines 1000 pages 9999
column sid format 9999
column serial for 999999
column status format a15
column username format a10
column sql_text format a80
column module format a30
col program for a30
col SQL_EXEC_START for a20
SELECT * FROM (SELECT status,inst_id,sid,SESSION_SERIAL# as Serial,username,sql_id,SQL_PLAN_HASH_VALUE, MODULE,program,
TO_CHAR(sql_exec_start,'dd-mon-yyyy hh24:mi:ss') AS sql_exec_start,
ROUND(elapsed_time/1000000) AS "Elapsed (s)", ROUND(cpu_time /1000000) AS "CPU (s)",
substr(sql_text,1,30) sql_text FROM gv$sql_monitor where status='EXECUTING' and module not like '%emagent%' ORDER BY sql_exec_start desc );

OVERALL EXECUTING SQL’s:

set lines 1000 pages 9999
column sid format 9999
column serial for 999999
column status format a15
column username format a10
column sql_text format a80
column module format a30
col program for a30
col SQL_EXEC_START for a20
SELECT * FROM (SELECT status,inst_id,sid,SESSION_SERIAL# as Serial,username,sql_id,SQL_PLAN_HASH_VALUE, MODULE,program,
TO_CHAR(sql_exec_start,'dd-mon-yyyy hh24:mi:ss') AS sql_exec_start,
ROUND(elapsed_time/1000000) AS "Elapsed (s)", ROUND(cpu_time /1000000) AS "CPU (s)",
substr(sql_text,1,30) sql_text FROM gv$sql_monitor where module not like '%emagent%'
ORDER BY sql_exec_start desc ) WHERE rownum<=20;

Sql-Monitor report for a sql_id ( Like OEM report):

column text_line format a254
set lines 750 pages 9999
set long 20000 longchunksize 20000
select
dbms_sqltune.report_sql_monitor_list() text_line
from dual;
select
dbms_sqltune.report_sql_monitor() text_line
from dual;

set pagesize 0
echo off timing off
linesize 1000
trimspool on
trim on
long 2000000
longchunksize 2000000
select DBMS_SQLTUNE.REPORT_SQL_MONITOR(
sql_id=>'&sql_id', report_level=>'ALL', type=>'TEXT') from dual;

Historical SQL Monitor reports:

SELECT report_id,EXTRACTVALUE(XMLType(report_summary),'/report_repository_summary/sql/@sql_id') sql_id,EXTRACTVALUE(XMLType(report_summary),'/report_repository_summary/sql/@sql_exec_id') sql_exec_id, EXTRACTVALUE(XMLType(report_summary),'/report_repository_summary/sql/@sql_exec_start') sql_exec_start  FROM dba_hist_reports WHERE component_name = 'sqlmonitor';

To get text from above Report id :

set long 10000000 longchunksize 10000000 pages 0
SELECT DBMS_AUTO_REPORT.REPORT_REPOSITORY_DETAIL(RID => 1022, TYPE => 'text') FROM dual;

Sid Level Debug

gv$session:

set linesize 750 pages 9999
column box format a30
column spid format a10
column username format a20
column program format a30
column os_user format a20
col LOGON_TIME for a20
select b.inst_id,b.sid,b.serial#,a.spid, substr(b.machine,1,30) box,to_char (b.logon_time, 'dd-mon-yyyy hh24:mi:ss') logon_time,
substr(b.username,1,20) username,
substr(b.osuser,1,20) os_user,
substr(b.program,1,30) program,status,b.last_call_et AS last_call_et_secs,b.sql_id
from gv$session b,gv$process a
where b.paddr = a.addr
and a.inst_id = b.inst_id
and type='USER' and b.sid=&sid
order by logon_time;

gv$sql_monitor:

set lines 1000 pages 9999
column sid format 9999
column serial for 999999
column status format a15
column username format a20
column sql_text format a80
col program for a33
col SQL_EXEC_START for a20
SELECT * FROM (SELECT status,inst_id,sid,SESSION_SERIAL# as Serial,username,sql_id,SQL_PLAN_HASH_VALUE,program,
TO_CHAR(sql_exec_start,'dd-mon-yyyy hh24:mi:ss') AS sql_exec_start,
ROUND(elapsed_time/1000000) AS "Elapsed (s)",
ROUND(cpu_time /1000000) AS "CPU (s)",
substr(sql_text,1,30) sql_text FROM gv$sql_monitor where module not like '%emagent%' and sid=&sid ORDER BY sql_exec_start desc );

set pages 50000 lines 32767
col OPNAME for a10
col SID form 9999
col SERIAL form 9999999
col PROGRAM for a10
col USERNAME for a10
col SQL_TEXT for a40
col START_TIME for a10
col LAST_UPDATE_TIME for a10
col TARGET for a25
col MESSAGE for a25
alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';
SELECT inst_id,sid, serial#, sql_id, opname, username, target, sofar, totalwork, start_time,last_update_time,round(time_remaining/60,2) "REMAIN MINS", round(elapsed_seconds/60,2) "ELAPSED MINS", round((time_remaining+elapsed_seconds)/60,2) "TOTAL MINS", ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE", message
FROM gv$session_longops
WHERE OPNAME NOT LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND sofar<>totalwork AND time_remaining > 0
/

COLUMN sid FORMAT 99999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A30
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10
SELECT s.sid, s.serial#, s.machine,
ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct FROM gv$session s, gv$session_longops sl WHERE s.sid = sl.sid AND s.serial# = sl.serial#;

SELECT SID, SERIAL#, OPNAME, TARGET, SOFAR, TOTALWORK, UNITS,
TO_CHAR(START_TIME,'DD/MON/YYYY HH24:MI:SS') START_TIME,
TO_CHAR(LAST_UPDATE_TIME,'DD/MON/YYYY HH24:MI:SS') LAST_UPDATE_TIME,
TIME_REMAINING, ELAPSED_SECONDS, MESSAGE, USERNAME
FROM V$SESSION_LONGOPS WHERE TIME_REMAINING != 0;

col username for a20
Select s.username, s.Sid, s.serial#, S.Sql_Id, round((Sysdate-Sql_Exec_Start)246060/60,0) MINUTES, Sql_Text
From V$Session S, V$Sqltext T
Where S.Sql_Id = T.Sql_Id
And Sql_Exec_Start Is Not Null And Piece = 0
And (Sysdate-Sql_Exec_Start)24*60 > 1
order by MINUTES desc;

Long Running query – long ops:

SELECT a.sid,RPAD(a.opname,30),a.sofar,a.totalwork,a.ELAPSED_SECONDS,ROUND(((a.sofar)*100)/a.totalwork,3) "%_COMPLETED",
RPAD(a.username,10) username,a.SQL_HASH_VALUE,B.STATUS
FROM gV$SESSION_LONGOPS a, gv$session b
WHERE a.sid=b.sid
and a.sid=&sid
--AND b.status='ACTIVE'
AND a.sofar<> a.totalwork
/

set pages 50000 lines 32767
col OPNAME for a10
col SID form 9999
col SERIAL form 9999999
col PROGRAM for a10
col USERNAME for a10
col SQL_TEXT for a40
col START_TIME for a10
col LAST_UPDATE_TIME for a10
col TARGET for a25
col MESSAGE for a25
alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';
SELECT inst_id,sid, serial#, sql_id, opname, username, target, sofar, totalwork, start_time,last_update_time,round(time_remaining/60,2) "REMAIN MINS", round(elapsed_seconds/60,2) "ELAPSED MINS", round((time_remaining+elapsed_seconds)/60,2) "TOTAL MINS", ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE", message
FROM gv$session_longops
WHERE OPNAME NOT LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND sofar<>totalwork AND time_remaining > 0
/

COLUMN sid FORMAT 99999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A30
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10
SELECT s.sid,
s.serial#,
s.machine,
ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM gv$session s,
gv$session_longops sl
WHERE s.sid = sl.sid
AND s.serial# = sl.serial#;

SELECT SID, SERIAL#, OPNAME, TARGET, SOFAR, TOTALWORK, UNITS,
TO_CHAR(START_TIME,'DD/MON/YYYY HH24:MI:SS') START_TIME,
TO_CHAR(LAST_UPDATE_TIME,'DD/MON/YYYY HH24:MI:SS') LAST_UPDATE_TIME,
TIME_REMAINING, ELAPSED_SECONDS, MESSAGE, USERNAME
FROM V$SESSION_LONGOPS
WHERE TIME_REMAINING != 0;
/

column username format a20
Select s.username, s.Sid, s.serial#, S.Sql_Id, round((Sysdate-Sql_Exec_Start)246060/60,0) MINUTES, Sql_Text
From V$Session S, V$Sqltext T
Where S.Sql_Id = T.Sql_Id
And Sql_Exec_Start Is Not Null And Piece = 0
And (Sysdate-Sql_Exec_Start)24*60 > 1
order by MINUTES desc
;

Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8