How to find SQL,SQL_ID history on Oracle
Session related Queries
Last/Latest Running SQL
———————–
set pages 50000 lines 32767
col “Last SQL” for 100
SELECT t.inst_id,[Link], [Link], [Link]#,t.sql_id,t.sql_text “Last SQL”
FROM gv$session s, gv$sqlarea t
WHERE s.sql_address =[Link] AND
s.sql_hash_value =t.hash_value
/
Current Running SQLs
——————–
set pages 50000 lines 32767
col HOST_NAME for a20
col EVENT for a40
col MACHINE for a30
col SQL_TEXT for a50
col USERNAME for a15
select
sid,serial#,a.sql_id,a.SQL_TEXT,[Link],i.host_name,machine,[Link],S.seconds_in
_wait sec_wait,
to_char(logon_time,’DD-MON-RR HH24:MI’) login
from gv$session S,gV$SQLAREA A,gv$instance i
where [Link] is not null
— and [Link]=’ACTIVE’
AND S.sql_address=[Link]
and s.inst_id=a.inst_id and i.inst_id = a.inst_id
and sql_text not like ‘select [Link],S.seconds_in_wait%’
/
Current Running SQLs
——————–
set pages 50000 lines 32767
col program format a20
col sql_text format a50
select [Link],[Link],b.last_call_et,[Link],c.sql_id,c.sql_text
from v$session b,v$sqlarea c
where b.sql_id=c.sql_id
/
Last/Latest Running SQL
———————–
set pages 50000 lines 32767
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
/
SQLs Running from longtime
————————–
alter session set nls_date_format = ‘dd/mm/yyyy hh24:mi’;
set pages 50000 lines 32767
col target format a25
col opname format a40
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
———————————————
set pages 50000 lines 32767
col USERNAME for a10
col MACHINE for a15
col PROGRAM for a40
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;
Session details associated with SID and Event waiting for
———————————————————
set pages 50000 lines 32767
col EVENT for a40
select [Link], [Link]#, [Link], [Link], [Link],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 [Link]
in(&SIDs) and [Link]=[Link] order by 8;
Session details associated with Oracle SID
——————————————-
set head off
set verify off
set echo off
set pages 1500
set linesize 100
set lines 120
prompt
prompt Details of SID / SPID / Client PID
prompt ==================================
select /*+ CHOOSE*/
‘Session Id………………………………………: ‘||[Link],
‘Serial Num……………………………………….: ‘||[Link]#,
‘User Name ……………………………………….: ‘||[Link],
‘Session Status …………………………………..: ‘||[Link],
‘Client Process Id on Client Machine ………………..: ‘||’*’||[Link]||’*’ Client,
‘Server Process ID ………………………………..: ‘||[Link] Server,
‘Sql_Address ……………………………………..: ‘||s.sql_address,
‘Sql_hash_value …………………………………..: ‘||s.sql_hash_value,
‘Schema Name ….. ………………………………..: ‘||[Link],
‘Program ………………………………………..: ‘||[Link],
‘Module ………………………………………….: ‘|| [Link],
‘Action ………………………………………….: ‘||[Link],
‘Terminal ………………………………………..: ‘||[Link],
‘Client Machine …………………………………..: ‘||[Link],
‘LAST_CALL_ET …………………………………….: ‘||s.last_call_et,
‘S.LAST_CALL_ET/3600 ………………………………: ‘||s.last_call_et/3600
from v$session s, v$process p
where [Link]=[Link] and
[Link]=nvl(‘&sid’,[Link])
/
set head on
Checking for Active Transactions SID
————————————
select username,t.used_ublk,t.used_urec from v$transaction t,v$session s where
[Link]=[Link];
Session details from Session longops
————————————-
select inst_id,SID,SERIAL#,OPNAME,SOFAR,TOTALWORK,START_TIME,LAST_UPDATE_TIME,
username from gv$session_longops;
Session details with SPID
————————-
select sid, serial#, USERNAME, STATUS, OSUSER, PROCESS,
MACHINE, MODULE, ACTION, to_char(LOGON_TIME,’yyyy-mm-dd hh24:mi:ss’)
from v$session where paddr in (select addr from v$process where spid = ‘&spid’)
/
To find Undo Generated For a given session
——————————————
select username,
t.used_ublk ,t.used_urec
from gv$transaction t,gv$session s
where [Link]=[Link] and
[Link]=’&sid’;
To list count of connections from other machines
————————————————
select count(1),machine from gv$session where inst_id=’&inst_id’ group by machine;
To get total count of sessions 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 find sqltext thru sqladdress
——————————-
select sql_address from v$session where sid=1999;
select sql_text from v$sqltext where ADDRESS=’C00000027FF00AF0′ order by PIECE;
To find sqltext for different sql hashvalue
——————————————-
select hash_value,sql_text from v$sql where hash_value in (1937378691,1564286875,
248741712,2235840973,2787402785)
To list long running forms user sessions
—————————————-
select [Link],[Link],[Link],[Link] ,[Link],[Link], (s.last_call_et/3600)
from
v$session s, v$process p where round(last_call_et/3600) >4 and action like ‘%FRM%’
and
[Link]=[Link] ;
Advertisement
To list inactive Sessions respective 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’;
To find session id with set of SPIDs
————————————
select sid from v$session, v$process where addr=paddr and spid in
(‘11555′,’26265′,’11533′);
To find Sql Text given SQLHASH & SQLADDR
—————————————-
select piece,sql_text from v$sqltext where HASH_VALUE = &hash and ADDRESS =’&addr’
order by piece;
select piece,sql_text from v$sqltext where ADDRESS =’&addr’ order by piece;