Queries to check the SQL Queries Status Posted by MOHAMMED ABDUL HAMEED on 6:04 AM

# Current Running SQL Queries

select b.sid,b.status,b.last_call_et,b.program,c.sql_id,c.sql_text

from v$session b,v$sqlarea c

where b.sql_id=c.sql_id;

# Latest Running SQL Queries

select inst_id,sample_time,session_id,session_serial#,sql_id from gv$active_session_history

where sql_id is not null

order by 1 desc;

# SQL Queries Running for Long Time

alter session set nls_date_format = 'dd/mm/yyyy hh24:mi';

select sid

      ,opname

      ,target

      ,round(sofar/totalwork*100,2)   as percent_done

      ,start_time

      ,last_update_time

      ,time_remaining

from

       v$session_longops;

# Active Sessions running for more than 1 hour

SELECT USERNAME,machine,inst_id,sid,serial#,PROGRAM,

to_char(logon_time,'dd-mm-yy hh:mi:ss AM')"Logon Time",

ROUND((SYSDATE-LOGON_TIME)*(24*60),1) as MINUTES_LOGGED_ON,

ROUND(LAST_CALL_ET/60,1) as Minutes_FOR_CURRENT_SQL

From gv$session

WHERE STATUS='ACTIVE'

AND USERNAME IS NOT NULL and ROUND((SYSDATE-LOGON_TIME)*(24*60),1) > 60

ORDER BY MINUTES_LOGGED_ON DESC;

# Inactive Sessions for more than 1 hour

SELECT USERNAME,machine,inst_id,sid,serial#,PROGRAM,

to_char(logon_time,'dd-mm-yy hh:mi:ss AM')"Logon Time",

ROUND((SYSDATE-LOGON_TIME)*(24*60),1) as MINUTES_LOGGED_ON,

ROUND(LAST_CALL_ET/60,1) as Minutes_FOR_CURRENT_SQL

From gv$session

WHERE STATUS='INACTIVE'

AND USERNAME IS NOT NULL and ROUND((SYSDATE-LOGON_TIME)*(24*60),1) > 60

ORDER BY MINUTES_LOGGED_ON DESC;

# Session details with SID and Event waiting

select a.sid, a.serial#, a.status, a.program, b.event,to_char(a.logon_time, 'dd-mon-yy hh24:mi') LOGON_TIME,to_char(Sysdate, 'dd-mon-yy-hh24:mi') CURRENT_TIME, (a.last_call_et/3600) "Hrs connected" from v$session a,v$session_wait b where a.sid in(&SIDs) and a.sid=b.sid order by 8;

# Count of Session and Processes

select count(*) from v$session;

select count(*) from v$process;

select (select count(*) from v$session) sessions, (select count(*) from v$process) processes from dual;

# To list inactive Sessions from username

SELECT username,count(*) num_inv_sess

FROM v$session

where last_call_et > 3600

and username is not null

AND STATUS='INACTIVE'

group by username

order by num_inv_sess DESC;

SELECT count(*) FROM v$session where last_call_et > 43200 and username is not null AND

STATUS='INACTIVE';

SELECT count(*) FROM v$session where last_call_et > 3600 and username is not null AND

STATUS='INACTIVE';