ow 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,s.username, s.sid, s.serial#,t.sql_id,t.sql_text "Last SQL"
FROM gv$session s, gv$sqlarea t
WHERE s.sql_address =t.address 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,S.USERNAME,i.host_name,machine,S.event,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 S.username is not null
-- and S.status='ACTIVE'
AND S.sql_address=A.address
and s.inst_id=a.inst_id and i.inst_id = a.inst_id
and sql_text not like 'select S.USERNAME,S.seconds_in_wait%'
/
Current Running SQLs
--------------------
set pages 50000 lines 32767
col program format a20
col sql_text format a50
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
/
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 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;
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.............................................: '||s.sid,
'Serial Num..............................................: '||s.serial#,
'User Name ..............................................: '||s.username,
'Session Status .........................................: '||s.status,
'Client Process Id on Client Machine ....................: '||'*'||s.process||'*' Client,
'Server Process ID ......................................: '||p.spid Server,
'Sql_Address ............................................: '||s.sql_address,
'Sql_hash_value .........................................: '||s.sql_hash_value,
'Schema Name ..... ......................................: '||s.SCHEMANAME,
'Program ...............................................: '||s.program,
'Module .................................................: '|| s.module,
'Action .................................................: '||s.action,
'Terminal ...............................................: '||s.terminal,
'Client Machine .........................................: '||s.machine,
'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 p.addr=s.paddr and
s.sid=nvl('&sid',s.sid)
/
set head on
Checking for Active Transactions SID
------------------------------------
select username,t.used_ublk,t.used_urec from v$transaction t,v$session s where t.addr=s.taddr;
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 t.addr=s.taddr and
s.sid='&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 s.sid,s.process,p.spid,s.status ,s.action,s.module, (s.last_call_et/3600) from
v$session s, v$process p where round(last_call_et/3600) >4 and action like '%FRM%' and
p.addr=s.paddr ;
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;
ocks on Database
Locks on Database
-----------------
set pages 50000 lines 32767
select object_name,s.inst_id,s.sid,s.serial#,p.sid,s.osuser,s.server,s.machine,s.status
from gv$locked_object l,gv$session s,gv$process p,dba_objects o
where
l.object_id=o.object_id and
l.session_id=s.sid and
s.paddr=p.addr;
Query to find which is locking the other session:
------------------------------------------------
set pages 50000 lines 32767
col SQL_TEXT for a60
col FIRST_LOAD_TIME a20
select sesion.sid,sesion.status,sesion.username,sql_text,sqlarea.first_load_time
from gv$sqlarea sqlarea, gv$session sesion
where sesion.sql_hash_value = sqlarea.hash_value
and sesion.sql_address = sqlarea.address
and sesion.sid=161
and sesion.username is not null;
SID STATUS USERNAME SQL_TEXT FIRST_LOAD_TIME
--- ------ -------- -------- ---------------
To find the Sid and their longops:
--------------------------------
set pages 50000 lines 32767
col OPNAME form a22
select l.sid,l.serial#,l.OPNAME,l.SOFAR,l.TOTALWORK,l.TIME_REMAINING,l.ELAPSED_SECONDS from gv$session_longops l,gv$session
s where s.sid=l.sid and s.serial#=l.serial#;
SID SERIAL# OPNAME SOFAR TOTALWORK TIME_REMAINING ELAPSED_SECONDS
-------- ---------- -------- ---------- ---------- -------------- ---------------
To find the locks for the sid:
-----------------------------
set pages 50000 lines 32767
col OWNER for a20
col NAME for a20
SELECT * FROM dba_dml_locks where SESSION_ID='&sid';
SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS
---------- ----- ---- --------- ------------- ------------ ---------------
To find any holding sessions:
----------------------------
select * from dba_blockers;
HOLDING_SESSION
---------------
161
To find waiters:
---------------
set pages 50000 lines 32767
col LOCK_TYPE for a10
col MODE_HELD for a10
col MODE_REQUESTED for a10
select * from dba_waiters;
WAITING_SESSION HOLDING_SESSION LOCK_TYPE MODE_HELD MODE_REQUESTED LOCK_ID1 LOCK_ID2
--------------- --------------- --------- --------- -------------- -------- --------
Blocking details:
----------------
set pages 50000 lines 32767
select distinct s1.username || '@' || s1.machine || ' ( INST=' || s1.inst_id || ' SID=' || s1.sid || ' ) is blocking ' ||
s2.username || '@' || s2.machine || ' ( INST=' || s1.inst_id || ' SID=' || s2.sid || ' ) ' as blocking_status from gv$lock
l1, gv$session s1, gv$lock l2, gv$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and
l1.id1 = l2.id1 and l2.id2 = l2.id2 and l1.inst_id = s1.inst_id;
set pages 50000 lines 32767
col BLOCKER for a20
col BLOCKEE for a20
select
(select username from v$session where sid = a.sid ) blocker,
a.sid, 'is blocking ',
(select username from v$session where sid =b.sid) blockee,
b.sid
from v$lock a, v$lock b
where a.block =1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2; 2 3 4 5 6 7 8 9 10
BLOCKER SID 'ISBLOCKING' BLOCKEE SID
------- ---------- ---------- ------- --------
Find locks:
----------
set pages 50000 lines 32767
select 'Sid '||a.sid||' waiting on Sid '||b.sid||' for object '||c.owner||'.'|| c.object_name||' since '||round
(d.last_call_et/60)||' Minutes'||decode(f.status,'INACTIVE','
and Sid '||f.sid||' is inactive since '||round(f.last_call_et/60)||' Minutes.','.')
from gv$lock a,gv$lock b, gv$session d,dba_objects c,gv$locked_object e,gv$session f
where a.request!=0 and a.type=b.type
and b.lmode!=0 and b.lmode!=1 and a.id1=b.id1 and a.id2=b.id2 and b.request=0 and b.block=1 and a.sid=d.sid and
d.sid=e.session_id and d.status='ACTIVE' and e.object_id=c.object_id and b.sid=f.sid order by d.last_call_et desc;
Another way to find locks:
-------------------------
set pages 50000 lines 32767
select l1.sid, ' IS BLOCKING ', l2.sid from gv$lock l1, gv$lock l2
where l1.block =1 and l2.request > 0 and l1.id1=l2.id1 and l1.id2=l2.id2;
List of blocking session:
-------------------------
set pages 9999 lines 300
select
blocking_session,
sid,
serial#,
wait_class,
seconds_in_wait
from
v$session
where
blocking_session is not NULL
order by
blocking_session;
BLOCKING_SESSION SID SERIAL# WAIT_CLASS SECONDS_IN_WAIT
---------------- ---------- ---------- -------------------- ---------------
Particular user query:
----------------------
set pages 9999 lines 300
select a.sid, a.serial#, b.sql_text
from v$session a, v$sqlarea b
where a.sql_address=b.address
and a.username='EODSBTCH';
Time since last user activity
-----------------------------
set pages 9999 lines 300
set lines 100 pages 999
select username
, floor(last_call_et / 60) "Minutes"
, status
from v$session
where username is not null
order by last_call_et
/
Sessions sorted by logon time
-----------------------------
set pages 9999 lines 300
set lines 100 pages 999
col ID format a15
col osuser format a15
col login_time format a14
select username
, osuser
, sid || ',' || serial# "ID"
, status
, to_char(logon_time, 'hh24:mi dd/mm/yy') login_time
, last_call_et
from v$session
where username is not null
order by login_time
/
Show user info including os pid
-------------------------------
set pages 9999 lines 300
col "SID/SERIAL" format a10
col username format a15
col osuser format a15
col program format a40
select s.sid || ',' || s.serial# "SID/SERIAL"
, s.username
, s.osuser
, p.spid "OS PID"
, s.program
from v$session s
, v$process p
Where s.paddr = p.addr
order by to_number(p.spid)
/
Show a users current sql
------------------------
set pages 9999 lines 300
Select sql_text
from v$sqlarea
where (address, hash_value) in
(select sql_address, sql_hash_value
from v$session
where username like '&username')
/
Session status associated with the specified os process id
----------------------------------------------------------
set pages 9999 lines 300
select s.username
, s.sid
, s.serial#
, p.spid
, last_call_et
, status
from V$SESSION s
, V$PROCESS p
where s.PADDR = p.ADDR
and p.spid='&pid'
/
Longops Query using v$session_longops
Longops.sql
-----------
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 a25alter 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
/
Note:
Get the SID from v$session_longops and plug it into v$session to check the SQL command details.
Current Running SQLs
--------------------
set pages 50000 lines 32767
col program format a40
col sql_text format a130
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 and b.sid='&sid'
/
set pages 50000 lines 32767
col USERNAME for a10
col OSUSER for a10
col MACHINE for a10
select s.sid,s.serial#,p.spid,s.username,s.osuser,s.status,s.process fg_pid,s.longon_time,s.machine,p.spid bg_pid from gv
$session s,gv$process p where s.addr=p.addr and s.sid='&sid'
/
$ps -ef | grep <spid>
set pages 50000 lines 32767
SELECT INST_ID, SID, SERIAL#, SQL_ID,USERNAME, PROGRAM, MACHINE, SERVICE_NAME
FROM GV$SESSION
WHERE SID IN ('<SID_NO.1>','<SID_NO.2>')
/
Active Running SQLs
--------------------
set pages 50000 lines 32767
col SPID for a10
col PROGRAM for a15
col OSUSER for a10
col ACTION for a10
col EVENT for a25
col SQL_TEXT for a25
col MACHINE for a10
col P1TEXT for a10
col P2TEXT for a10
col P3TEXT for a10
SELECT b.sid, b.serial#, a.spid, b.sql_id, b.program, b.osuser, b.machine, b.type,
b.event, b.action, b.p1text, b.p2text, b.p3text, b.state, c.sql_text,b.logon_time
FROM v$process a, v$session b, v$sqltext c WHERE a.addr=b.paddr
AND b.sql_hash_value = c.hash_value
AND b.STATUS = 'ACTIVE'
AND b.sid = '&sid' ORDER BY a.spid, c.piece
/
OR - Use the below Query
Longops.sql
-----------
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 l.inst_id,l.sid, l.serial#, l.sql_id, l.opname, l.username, l.target, l.sofar, l.totalwork,
l.start_time,l.last_update_time,round(l.time_remaining/60,2) "REMAIN MINS", round(l.elapsed_seconds/60,2) "ELAPSED MINS",
round((l.time_remaining+l.elapsed_seconds)/60,2) "TOTAL MINS", ROUND(l.SOFAR/l.TOTALWORK*100,2) "%_COMPLETE",
l.message,s.sql_text
FROM gv$session_longops l
LEFT OUTER JOIN v$sql s on s.hash_value=l.sql_hash_value and s.address=l.sql_address and s.child_number=0
WHERE l.OPNAME NOT LIKE 'RMAN%' AND l.OPNAME NOT LIKE '%aggregate%' AND l.TOTALWORK != 0 AND l.sofar<>l.totalwork AND
l.time_remaining > 0
/
ow 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,s.username, s.sid, s.serial#,t.sql_id,t.sql_text "Last SQL"
FROM gv$session s, gv$sqlarea t
WHERE s.sql_address =t.address 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,S.USERNAME,i.host_name,machine,S.event,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 S.username is not null
-- and S.status='ACTIVE'
AND S.sql_address=A.address
and s.inst_id=a.inst_id and i.inst_id = a.inst_id
and sql_text not like 'select S.USERNAME,S.seconds_in_wait%'
/
Current Running SQLs
--------------------
set pages 50000 lines 32767
col program format a20
col sql_text format a50
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
/
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 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;
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.............................................: '||s.sid,
'Serial Num..............................................: '||s.serial#,
'User Name ..............................................: '||s.username,
'Session Status .........................................: '||s.status,
'Client Process Id on Client Machine ....................: '||'*'||s.process||'*' Client,
'Server Process ID ......................................: '||p.spid Server,
'Sql_Address ............................................: '||s.sql_address,
'Sql_hash_value .........................................: '||s.sql_hash_value,
'Schema Name ..... ......................................: '||s.SCHEMANAME,
'Program ...............................................: '||s.program,
'Module .................................................: '|| s.module,
'Action .................................................: '||s.action,
'Terminal ...............................................: '||s.terminal,
'Client Machine .........................................: '||s.machine,
'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 p.addr=s.paddr and
s.sid=nvl('&sid',s.sid)
/
set head on
Checking for Active Transactions SID
------------------------------------
select username,t.used_ublk,t.used_urec from v$transaction t,v$session s where t.addr=s.taddr;
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 t.addr=s.taddr and
s.sid='&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 s.sid,s.process,p.spid,s.status ,s.action,s.module, (s.last_call_et/3600) from
v$session s, v$process p where round(last_call_et/3600) >4 and action like '%FRM%' and
p.addr=s.paddr ;
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;
ocks on Database
Locks on Database
-----------------
set pages 50000 lines 32767
select object_name,s.inst_id,s.sid,s.serial#,p.sid,s.osuser,s.server,s.machine,s.status
from gv$locked_object l,gv$session s,gv$process p,dba_objects o
where
l.object_id=o.object_id and
l.session_id=s.sid and
s.paddr=p.addr;
Query to find which is locking the other session:
------------------------------------------------
set pages 50000 lines 32767
col SQL_TEXT for a60
col FIRST_LOAD_TIME a20
select sesion.sid,sesion.status,sesion.username,sql_text,sqlarea.first_load_time
from gv$sqlarea sqlarea, gv$session sesion
where sesion.sql_hash_value = sqlarea.hash_value
and sesion.sql_address = sqlarea.address
and sesion.sid=161
and sesion.username is not null;
SID STATUS USERNAME SQL_TEXT FIRST_LOAD_TIME
--- ------ -------- -------- ---------------
To find the Sid and their longops:
--------------------------------
set pages 50000 lines 32767
col OPNAME form a22
select l.sid,l.serial#,l.OPNAME,l.SOFAR,l.TOTALWORK,l.TIME_REMAINING,l.ELAPSED_SECONDS from gv$session_longops l,gv$session
s where s.sid=l.sid and s.serial#=l.serial#;
SID SERIAL# OPNAME SOFAR TOTALWORK TIME_REMAINING ELAPSED_SECONDS
-------- ---------- -------- ---------- ---------- -------------- ---------------
To find the locks for the sid:
-----------------------------
set pages 50000 lines 32767
col OWNER for a20
col NAME for a20
SELECT * FROM dba_dml_locks where SESSION_ID='&sid';
SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS
---------- ----- ---- --------- ------------- ------------ ---------------
To find any holding sessions:
----------------------------
select * from dba_blockers;
HOLDING_SESSION
---------------
161
To find waiters:
---------------
set pages 50000 lines 32767
col LOCK_TYPE for a10
col MODE_HELD for a10
col MODE_REQUESTED for a10
select * from dba_waiters;
WAITING_SESSION HOLDING_SESSION LOCK_TYPE MODE_HELD MODE_REQUESTED LOCK_ID1 LOCK_ID2
--------------- --------------- --------- --------- -------------- -------- --------
Blocking details:
----------------
set pages 50000 lines 32767
select distinct s1.username || '@' || s1.machine || ' ( INST=' || s1.inst_id || ' SID=' || s1.sid || ' ) is blocking ' ||
s2.username || '@' || s2.machine || ' ( INST=' || s1.inst_id || ' SID=' || s2.sid || ' ) ' as blocking_status from gv$lock
l1, gv$session s1, gv$lock l2, gv$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and
l1.id1 = l2.id1 and l2.id2 = l2.id2 and l1.inst_id = s1.inst_id;
set pages 50000 lines 32767
col BLOCKER for a20
col BLOCKEE for a20
select
(select username from v$session where sid = a.sid ) blocker,
a.sid, 'is blocking ',
(select username from v$session where sid =b.sid) blockee,
b.sid
from v$lock a, v$lock b
where a.block =1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2; 2 3 4 5 6 7 8 9 10
BLOCKER SID 'ISBLOCKING' BLOCKEE SID
------- ---------- ---------- ------- --------
Find locks:
----------
set pages 50000 lines 32767
select 'Sid '||a.sid||' waiting on Sid '||b.sid||' for object '||c.owner||'.'|| c.object_name||' since '||round
(d.last_call_et/60)||' Minutes'||decode(f.status,'INACTIVE','
and Sid '||f.sid||' is inactive since '||round(f.last_call_et/60)||' Minutes.','.')
from gv$lock a,gv$lock b, gv$session d,dba_objects c,gv$locked_object e,gv$session f
where a.request!=0 and a.type=b.type
and b.lmode!=0 and b.lmode!=1 and a.id1=b.id1 and a.id2=b.id2 and b.request=0 and b.block=1 and a.sid=d.sid and
d.sid=e.session_id and d.status='ACTIVE' and e.object_id=c.object_id and b.sid=f.sid order by d.last_call_et desc;
Another way to find locks:
-------------------------
set pages 50000 lines 32767
select l1.sid, ' IS BLOCKING ', l2.sid from gv$lock l1, gv$lock l2
where l1.block =1 and l2.request > 0 and l1.id1=l2.id1 and l1.id2=l2.id2;
List of blocking session:
-------------------------
set pages 9999 lines 300
select
blocking_session,
sid,
serial#,
wait_class,
seconds_in_wait
from
v$session
where
blocking_session is not NULL
order by
blocking_session;
BLOCKING_SESSION SID SERIAL# WAIT_CLASS SECONDS_IN_WAIT
---------------- ---------- ---------- -------------------- ---------------
Particular user query:
----------------------
set pages 9999 lines 300
select a.sid, a.serial#, b.sql_text
from v$session a, v$sqlarea b
where a.sql_address=b.address
and a.username='EODSBTCH';
Time since last user activity
-----------------------------
set pages 9999 lines 300
set lines 100 pages 999
select username
, floor(last_call_et / 60) "Minutes"
, status
from v$session
where username is not null
order by last_call_et
/
Sessions sorted by logon time
-----------------------------
set pages 9999 lines 300
set lines 100 pages 999
col ID format a15
col osuser format a15
col login_time format a14
select username
, osuser
, sid || ',' || serial# "ID"
, status
, to_char(logon_time, 'hh24:mi dd/mm/yy') login_time
, last_call_et
from v$session
where username is not null
order by login_time
/
Show user info including os pid
-------------------------------
set pages 9999 lines 300
col "SID/SERIAL" format a10
col username format a15
col osuser format a15
col program format a40
select s.sid || ',' || s.serial# "SID/SERIAL"
, s.username
, s.osuser
, p.spid "OS PID"
, s.program
from v$session s
, v$process p
Where s.paddr = p.addr
order by to_number(p.spid)
/
Show a users current sql
------------------------
set pages 9999 lines 300
Select sql_text
from v$sqlarea
where (address, hash_value) in
(select sql_address, sql_hash_value
from v$session
where username like '&username')
/
Session status associated with the specified os process id
----------------------------------------------------------
set pages 9999 lines 300
select s.username
, s.sid
, s.serial#
, p.spid
, last_call_et
, status
from V$SESSION s
, V$PROCESS p
where s.PADDR = p.ADDR
and p.spid='&pid'
/
Longops Query using v$session_longops
Longops.sql
-----------
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 a25alter 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
/
Note:
Get the SID from v$session_longops and plug it into v$session to check the SQL command details.
Current Running SQLs
--------------------
set pages 50000 lines 32767
col program format a40
col sql_text format a130
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 and b.sid='&sid'
/
set pages 50000 lines 32767
col USERNAME for a10
col OSUSER for a10
col MACHINE for a10
select s.sid,s.serial#,p.spid,s.username,s.osuser,s.status,s.process fg_pid,s.longon_time,s.machine,p.spid bg_pid from gv
$session s,gv$process p where s.addr=p.addr and s.sid='&sid'
/
$ps -ef | grep <spid>
set pages 50000 lines 32767
SELECT INST_ID, SID, SERIAL#, SQL_ID,USERNAME, PROGRAM, MACHINE, SERVICE_NAME
FROM GV$SESSION
WHERE SID IN ('<SID_NO.1>','<SID_NO.2>')
/
Active Running SQLs
--------------------
set pages 50000 lines 32767
col SPID for a10
col PROGRAM for a15
col OSUSER for a10
col ACTION for a10
col EVENT for a25
col SQL_TEXT for a25
col MACHINE for a10
col P1TEXT for a10
col P2TEXT for a10
col P3TEXT for a10
SELECT b.sid, b.serial#, a.spid, b.sql_id, b.program, b.osuser, b.machine, b.type,
b.event, b.action, b.p1text, b.p2text, b.p3text, b.state, c.sql_text,b.logon_time
FROM v$process a, v$session b, v$sqltext c WHERE a.addr=b.paddr
AND b.sql_hash_value = c.hash_value
AND b.STATUS = 'ACTIVE'
AND b.sid = '&sid' ORDER BY a.spid, c.piece
/
OR - Use the below Query
Longops.sql
-----------
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 l.inst_id,l.sid, l.serial#, l.sql_id, l.opname, l.username, l.target, l.sofar, l.totalwork,
l.start_time,l.last_update_time,round(l.time_remaining/60,2) "REMAIN MINS", round(l.elapsed_seconds/60,2) "ELAPSED MINS",
round((l.time_remaining+l.elapsed_seconds)/60,2) "TOTAL MINS", ROUND(l.SOFAR/l.TOTALWORK*100,2) "%_COMPLETE",
l.message,s.sql_text
FROM gv$session_longops l
LEFT OUTER JOIN v$sql s on s.hash_value=l.sql_hash_value and s.address=l.sql_address and s.child_number=0
WHERE l.OPNAME NOT LIKE 'RMAN%' AND l.OPNAME NOT LIKE '%aggregate%' AND l.TOTALWORK != 0 AND l.sofar<>l.totalwork AND
l.time_remaining > 0
/
Session related Queries
Last/Latest Running SQL
-----------------------
set pages 50000 lines 32767
col "Last SQL" for 100
SELECT t.inst_id,s.username, s.sid, s.serial#,t.sql_id,t.sql_text "Last SQL"
FROM gv$session s, gv$sqlarea t
WHERE s.sql_address =t.address 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,S.USERNAME,i.host_name,machine,S.event,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 S.username is not null
-- and S.status='ACTIVE'
AND S.sql_address=A.address
and s.inst_id=a.inst_id and i.inst_id = a.inst_id
and sql_text not like 'select S.USERNAME,S.seconds_in_wait%'
/
Current Running SQLs
--------------------
set pages 50000 lines 32767
col program format a20
col sql_text format a50
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
/
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 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;
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.............................................: '||s.sid,
'Serial Num..............................................: '||s.serial#,
'User Name ..............................................: '||s.username,
'Session Status .........................................: '||s.status,
'Client Process Id on Client Machine ....................: '||'*'||s.process||'*' Client,
'Server Process ID ......................................: '||p.spid Server,
'Sql_Address ............................................: '||s.sql_address,
'Sql_hash_value .........................................: '||s.sql_hash_value,
'Schema Name ..... ......................................: '||s.SCHEMANAME,
'Program ...............................................: '||s.program,
'Module .................................................: '|| s.module,
'Action .................................................: '||s.action,
'Terminal ...............................................: '||s.terminal,
'Client Machine .........................................: '||s.machine,
'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 p.addr=s.paddr and
s.sid=nvl('&sid',s.sid)
/
set head on
Checking for Active Transactions SID
------------------------------------
select username,t.used_ublk,t.used_urec from v$transaction t,v$session s where t.addr=s.taddr;
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 t.addr=s.taddr and
s.sid='&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 s.sid,s.process,p.spid,s.status ,s.action,s.module, (s.last_call_et/3600) from
v$session s, v$process p where round(last_call_et/3600) >4 and action like '%FRM%' and
p.addr=s.paddr ;
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;
ocks on Database
Locks on Database
-----------------
set pages 50000 lines 32767
select object_name,s.inst_id,s.sid,s.serial#,p.sid,s.osuser,s.server,s.machine,s.status
from gv$locked_object l,gv$session s,gv$process p,dba_objects o
where
l.object_id=o.object_id and
l.session_id=s.sid and
s.paddr=p.addr;
Query to find which is locking the other session:
------------------------------------------------
set pages 50000 lines 32767
col SQL_TEXT for a60
col FIRST_LOAD_TIME a20
select sesion.sid,sesion.status,sesion.username,sql_text,sqlarea.first_load_time
from gv$sqlarea sqlarea, gv$session sesion
where sesion.sql_hash_value = sqlarea.hash_value
and sesion.sql_address = sqlarea.address
and sesion.sid=161
and sesion.username is not null;
SID STATUS USERNAME SQL_TEXT FIRST_LOAD_TIME
--- ------ -------- -------- ---------------
To find the Sid and their longops:
--------------------------------
set pages 50000 lines 32767
col OPNAME form a22
select l.sid,l.serial#,l.OPNAME,l.SOFAR,l.TOTALWORK,l.TIME_REMAINING,l.ELAPSED_SECONDS from gv$session_longops l,gv$session
s where s.sid=l.sid and s.serial#=l.serial#;
SID SERIAL# OPNAME SOFAR TOTALWORK TIME_REMAINING ELAPSED_SECONDS
-------- ---------- -------- ---------- ---------- -------------- ---------------
To find the locks for the sid:
-----------------------------
set pages 50000 lines 32767
col OWNER for a20
col NAME for a20
SELECT * FROM dba_dml_locks where SESSION_ID='&sid';
SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS
---------- ----- ---- --------- ------------- ------------ ---------------
To find any holding sessions:
----------------------------
select * from dba_blockers;
HOLDING_SESSION
---------------
161
To find waiters:
---------------
set pages 50000 lines 32767
col LOCK_TYPE for a10
col MODE_HELD for a10
col MODE_REQUESTED for a10
select * from dba_waiters;
WAITING_SESSION HOLDING_SESSION LOCK_TYPE MODE_HELD MODE_REQUESTED LOCK_ID1 LOCK_ID2
--------------- --------------- --------- --------- -------------- -------- --------
Blocking details:
----------------
set pages 50000 lines 32767
select distinct s1.username || '@' || s1.machine || ' ( INST=' || s1.inst_id || ' SID=' || s1.sid || ' ) is blocking ' ||
s2.username || '@' || s2.machine || ' ( INST=' || s1.inst_id || ' SID=' || s2.sid || ' ) ' as blocking_status from gv$lock
l1, gv$session s1, gv$lock l2, gv$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and
l1.id1 = l2.id1 and l2.id2 = l2.id2 and l1.inst_id = s1.inst_id;
set pages 50000 lines 32767
col BLOCKER for a20
col BLOCKEE for a20
select
(select username from v$session where sid = a.sid ) blocker,
a.sid, 'is blocking ',
(select username from v$session where sid =b.sid) blockee,
b.sid
from v$lock a, v$lock b
where a.block =1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2; 2 3 4 5 6 7 8 9 10
BLOCKER SID 'ISBLOCKING' BLOCKEE SID
------- ---------- ---------- ------- --------
Find locks:
----------
set pages 50000 lines 32767
select 'Sid '||a.sid||' waiting on Sid '||b.sid||' for object '||c.owner||'.'|| c.object_name||' since '||round
(d.last_call_et/60)||' Minutes'||decode(f.status,'INACTIVE','
and Sid '||f.sid||' is inactive since '||round(f.last_call_et/60)||' Minutes.','.')
from gv$lock a,gv$lock b, gv$session d,dba_objects c,gv$locked_object e,gv$session f
where a.request!=0 and a.type=b.type
and b.lmode!=0 and b.lmode!=1 and a.id1=b.id1 and a.id2=b.id2 and b.request=0 and b.block=1 and a.sid=d.sid and
d.sid=e.session_id and d.status='ACTIVE' and e.object_id=c.object_id and b.sid=f.sid order by d.last_call_et desc;
Another way to find locks:
-------------------------
set pages 50000 lines 32767
select l1.sid, ' IS BLOCKING ', l2.sid from gv$lock l1, gv$lock l2
where l1.block =1 and l2.request > 0 and l1.id1=l2.id1 and l1.id2=l2.id2;
List of blocking session:
-------------------------
set pages 9999 lines 300
select
blocking_session,
sid,
serial#,
wait_class,
seconds_in_wait
from
v$session
where
blocking_session is not NULL
order by
blocking_session;
BLOCKING_SESSION SID SERIAL# WAIT_CLASS SECONDS_IN_WAIT
---------------- ---------- ---------- -------------------- ---------------
Particular user query:
----------------------
set pages 9999 lines 300
select a.sid, a.serial#, b.sql_text
from v$session a, v$sqlarea b
where a.sql_address=b.address
and a.username='EODSBTCH';
Time since last user activity
-----------------------------
set pages 9999 lines 300
set lines 100 pages 999
select username
, floor(last_call_et / 60) "Minutes"
, status
from v$session
where username is not null
order by last_call_et
/
Sessions sorted by logon time
-----------------------------
set pages 9999 lines 300
set lines 100 pages 999
col ID format a15
col osuser format a15
col login_time format a14
select username
, osuser
, sid || ',' || serial# "ID"
, status
, to_char(logon_time, 'hh24:mi dd/mm/yy') login_time
, last_call_et
from v$session
where username is not null
order by login_time
/
Show user info including os pid
-------------------------------
set pages 9999 lines 300
col "SID/SERIAL" format a10
col username format a15
col osuser format a15
col program format a40
select s.sid || ',' || s.serial# "SID/SERIAL"
, s.username
, s.osuser
, p.spid "OS PID"
, s.program
from v$session s
, v$process p
Where s.paddr = p.addr
order by to_number(p.spid)
/
Show a users current sql
------------------------
set pages 9999 lines 300
Select sql_text
from v$sqlarea
where (address, hash_value) in
(select sql_address, sql_hash_value
from v$session
where username like '&username')
/
Session status associated with the specified os process id
----------------------------------------------------------
set pages 9999 lines 300
select s.username
, s.sid
, s.serial#
, p.spid
, last_call_et
, status
from V$SESSION s
, V$PROCESS p
where s.PADDR = p.ADDR
and p.spid='&pid'
/
Longops Query using v$session_longops
Longops.sql
-----------
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 a25alter 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
/
Note:
Get the SID from v$session_longops and plug it into v$session to check the SQL command details.
Current Running SQLs
--------------------
set pages 50000 lines 32767
col program format a40
col sql_text format a130
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 and b.sid='&sid'
/
set pages 50000 lines 32767
col USERNAME for a10
col OSUSER for a10
col MACHINE for a10
select s.sid,s.serial#,p.spid,s.username,s.osuser,s.status,s.process fg_pid,s.longon_time,s.machine,p.spid bg_pid from gv
$session s,gv$process p where s.addr=p.addr and s.sid='&sid'
/
$ps -ef | grep <spid>
set pages 50000 lines 32767
SELECT INST_ID, SID, SERIAL#, SQL_ID,USERNAME, PROGRAM, MACHINE, SERVICE_NAME
FROM GV$SESSION
WHERE SID IN ('<SID_NO.1>','<SID_NO.2>')
/
Active Running SQLs
--------------------
set pages 50000 lines 32767
col SPID for a10
col PROGRAM for a15
col OSUSER for a10
col ACTION for a10
col EVENT for a25
col SQL_TEXT for a25
col MACHINE for a10
col P1TEXT for a10
col P2TEXT for a10
col P3TEXT for a10
SELECT b.sid, b.serial#, a.spid, b.sql_id, b.program, b.osuser, b.machine, b.type,
b.event, b.action, b.p1text, b.p2text, b.p3text, b.state, c.sql_text,b.logon_time
FROM v$process a, v$session b, v$sqltext c WHERE a.addr=b.paddr
AND b.sql_hash_value = c.hash_value
AND b.STATUS = 'ACTIVE'
AND b.sid = '&sid' ORDER BY a.spid, c.piece
/
OR - Use the below Query
Longops.sql
-----------
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 l.inst_id,l.sid, l.serial#, l.sql_id, l.opname, l.username, l.target, l.sofar, l.totalwork,
l.start_time,l.last_update_time,round(l.time_remaining/60,2) "REMAIN MINS", round(l.elapsed_seconds/60,2) "ELAPSED MINS",
round((l.time_remaining+l.elapsed_seconds)/60,2) "TOTAL MINS", ROUND(l.SOFAR/l.TOTALWORK*100,2) "%_COMPLETE",
l.message,s.sql_text
FROM gv$session_longops l
LEFT OUTER JOIN v$sql s on s.hash_value=l.sql_hash_value and s.address=l.sql_address and s.child_number=0
WHERE l.OPNAME NOT LIKE 'RMAN%' AND l.OPNAME NOT LIKE '%aggregate%' AND l.TOTALWORK != 0 AND l.sofar<>l.totalwork AND
l.time_remaining > 0
/
ow 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,s.username, s.sid, s.serial#,t.sql_id,t.sql_text "Last SQL"
FROM gv$session s, gv$sqlarea t
WHERE s.sql_address =t.address 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,S.USERNAME,i.host_name,machine,S.event,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 S.username is not null
-- and S.status='ACTIVE'
AND S.sql_address=A.address
and s.inst_id=a.inst_id and i.inst_id = a.inst_id
and sql_text not like 'select S.USERNAME,S.seconds_in_wait%'
/
Current Running SQLs
--------------------
set pages 50000 lines 32767
col program format a20
col sql_text format a50
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
/
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 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;
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.............................................: '||s.sid,
'Serial Num..............................................: '||s.serial#,
'User Name ..............................................: '||s.username,
'Session Status .........................................: '||s.status,
'Client Process Id on Client Machine ....................: '||'*'||s.process||'*' Client,
'Server Process ID ......................................: '||p.spid Server,
'Sql_Address ............................................: '||s.sql_address,
'Sql_hash_value .........................................: '||s.sql_hash_value,
'Schema Name ..... ......................................: '||s.SCHEMANAME,
'Program ...............................................: '||s.program,
'Module .................................................: '|| s.module,
'Action .................................................: '||s.action,
'Terminal ...............................................: '||s.terminal,
'Client Machine .........................................: '||s.machine,
'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 p.addr=s.paddr and
s.sid=nvl('&sid',s.sid)
/
set head on
Checking for Active Transactions SID
------------------------------------
select username,t.used_ublk,t.used_urec from v$transaction t,v$session s where t.addr=s.taddr;
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 t.addr=s.taddr and
s.sid='&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 s.sid,s.process,p.spid,s.status ,s.action,s.module, (s.last_call_et/3600) from
v$session s, v$process p where round(last_call_et/3600) >4 and action like '%FRM%' and
p.addr=s.paddr ;
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;
ocks on Database
Locks on Database
-----------------
set pages 50000 lines 32767
select object_name,s.inst_id,s.sid,s.serial#,p.sid,s.osuser,s.server,s.machine,s.status
from gv$locked_object l,gv$session s,gv$process p,dba_objects o
where
l.object_id=o.object_id and
l.session_id=s.sid and
s.paddr=p.addr;
Query to find which is locking the other session:
------------------------------------------------
set pages 50000 lines 32767
col SQL_TEXT for a60
col FIRST_LOAD_TIME a20
select sesion.sid,sesion.status,sesion.username,sql_text,sqlarea.first_load_time
from gv$sqlarea sqlarea, gv$session sesion
where sesion.sql_hash_value = sqlarea.hash_value
and sesion.sql_address = sqlarea.address
and sesion.sid=161
and sesion.username is not null;
SID STATUS USERNAME SQL_TEXT FIRST_LOAD_TIME
--- ------ -------- -------- ---------------
To find the Sid and their longops:
--------------------------------
set pages 50000 lines 32767
col OPNAME form a22
select l.sid,l.serial#,l.OPNAME,l.SOFAR,l.TOTALWORK,l.TIME_REMAINING,l.ELAPSED_SECONDS from gv$session_longops l,gv$session
s where s.sid=l.sid and s.serial#=l.serial#;
SID SERIAL# OPNAME SOFAR TOTALWORK TIME_REMAINING ELAPSED_SECONDS
-------- ---------- -------- ---------- ---------- -------------- ---------------
To find the locks for the sid:
-----------------------------
set pages 50000 lines 32767
col OWNER for a20
col NAME for a20
SELECT * FROM dba_dml_locks where SESSION_ID='&sid';
SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS
---------- ----- ---- --------- ------------- ------------ ---------------
To find any holding sessions:
----------------------------
select * from dba_blockers;
HOLDING_SESSION
---------------
161
To find waiters:
---------------
set pages 50000 lines 32767
col LOCK_TYPE for a10
col MODE_HELD for a10
col MODE_REQUESTED for a10
select * from dba_waiters;
WAITING_SESSION HOLDING_SESSION LOCK_TYPE MODE_HELD MODE_REQUESTED LOCK_ID1 LOCK_ID2
--------------- --------------- --------- --------- -------------- -------- --------
Blocking details:
----------------
set pages 50000 lines 32767
select distinct s1.username || '@' || s1.machine || ' ( INST=' || s1.inst_id || ' SID=' || s1.sid || ' ) is blocking ' ||
s2.username || '@' || s2.machine || ' ( INST=' || s1.inst_id || ' SID=' || s2.sid || ' ) ' as blocking_status from gv$lock
l1, gv$session s1, gv$lock l2, gv$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and
l1.id1 = l2.id1 and l2.id2 = l2.id2 and l1.inst_id = s1.inst_id;
set pages 50000 lines 32767
col BLOCKER for a20
col BLOCKEE for a20
select
(select username from v$session where sid = a.sid ) blocker,
a.sid, 'is blocking ',
(select username from v$session where sid =b.sid) blockee,
b.sid
from v$lock a, v$lock b
where a.block =1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2; 2 3 4 5 6 7 8 9 10
BLOCKER SID 'ISBLOCKING' BLOCKEE SID
------- ---------- ---------- ------- --------
Find locks:
----------
set pages 50000 lines 32767
select 'Sid '||a.sid||' waiting on Sid '||b.sid||' for object '||c.owner||'.'|| c.object_name||' since '||round
(d.last_call_et/60)||' Minutes'||decode(f.status,'INACTIVE','
and Sid '||f.sid||' is inactive since '||round(f.last_call_et/60)||' Minutes.','.')
from gv$lock a,gv$lock b, gv$session d,dba_objects c,gv$locked_object e,gv$session f
where a.request!=0 and a.type=b.type
and b.lmode!=0 and b.lmode!=1 and a.id1=b.id1 and a.id2=b.id2 and b.request=0 and b.block=1 and a.sid=d.sid and
d.sid=e.session_id and d.status='ACTIVE' and e.object_id=c.object_id and b.sid=f.sid order by d.last_call_et desc;
Another way to find locks:
-------------------------
set pages 50000 lines 32767
select l1.sid, ' IS BLOCKING ', l2.sid from gv$lock l1, gv$lock l2
where l1.block =1 and l2.request > 0 and l1.id1=l2.id1 and l1.id2=l2.id2;
List of blocking session:
-------------------------
set pages 9999 lines 300
select
blocking_session,
sid,
serial#,
wait_class,
seconds_in_wait
from
v$session
where
blocking_session is not NULL
order by
blocking_session;
BLOCKING_SESSION SID SERIAL# WAIT_CLASS SECONDS_IN_WAIT
---------------- ---------- ---------- -------------------- ---------------
Particular user query:
----------------------
set pages 9999 lines 300
select a.sid, a.serial#, b.sql_text
from v$session a, v$sqlarea b
where a.sql_address=b.address
and a.username='EODSBTCH';
Time since last user activity
-----------------------------
set pages 9999 lines 300
set lines 100 pages 999
select username
, floor(last_call_et / 60) "Minutes"
, status
from v$session
where username is not null
order by last_call_et
/
Sessions sorted by logon time
-----------------------------
set pages 9999 lines 300
set lines 100 pages 999
col ID format a15
col osuser format a15
col login_time format a14
select username
, osuser
, sid || ',' || serial# "ID"
, status
, to_char(logon_time, 'hh24:mi dd/mm/yy') login_time
, last_call_et
from v$session
where username is not null
order by login_time
/
Show user info including os pid
-------------------------------
set pages 9999 lines 300
col "SID/SERIAL" format a10
col username format a15
col osuser format a15
col program format a40
select s.sid || ',' || s.serial# "SID/SERIAL"
, s.username
, s.osuser
, p.spid "OS PID"
, s.program
from v$session s
, v$process p
Where s.paddr = p.addr
order by to_number(p.spid)
/
Show a users current sql
------------------------
set pages 9999 lines 300
Select sql_text
from v$sqlarea
where (address, hash_value) in
(select sql_address, sql_hash_value
from v$session
where username like '&username')
/
Session status associated with the specified os process id
----------------------------------------------------------
set pages 9999 lines 300
select s.username
, s.sid
, s.serial#
, p.spid
, last_call_et
, status
from V$SESSION s
, V$PROCESS p
where s.PADDR = p.ADDR
and p.spid='&pid'
/
Longops Query using v$session_longops
Longops.sql
-----------
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 a25alter 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
/
Note:
Get the SID from v$session_longops and plug it into v$session to check the SQL command details.
Current Running SQLs
--------------------
set pages 50000 lines 32767
col program format a40
col sql_text format a130
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 and b.sid='&sid'
/
set pages 50000 lines 32767
col USERNAME for a10
col OSUSER for a10
col MACHINE for a10
select s.sid,s.serial#,p.spid,s.username,s.osuser,s.status,s.process fg_pid,s.longon_time,s.machine,p.spid bg_pid from gv
$session s,gv$process p where s.addr=p.addr and s.sid='&sid'
/
$ps -ef | grep <spid>
set pages 50000 lines 32767
SELECT INST_ID, SID, SERIAL#, SQL_ID,USERNAME, PROGRAM, MACHINE, SERVICE_NAME
FROM GV$SESSION
WHERE SID IN ('<SID_NO.1>','<SID_NO.2>')
/
Active Running SQLs
--------------------
set pages 50000 lines 32767
col SPID for a10
col PROGRAM for a15
col OSUSER for a10
col ACTION for a10
col EVENT for a25
col SQL_TEXT for a25
col MACHINE for a10
col P1TEXT for a10
col P2TEXT for a10
col P3TEXT for a10
SELECT b.sid, b.serial#, a.spid, b.sql_id, b.program, b.osuser, b.machine, b.type,
b.event, b.action, b.p1text, b.p2text, b.p3text, b.state, c.sql_text,b.logon_time
FROM v$process a, v$session b, v$sqltext c WHERE a.addr=b.paddr
AND b.sql_hash_value = c.hash_value
AND b.STATUS = 'ACTIVE'
AND b.sid = '&sid' ORDER BY a.spid, c.piece
/
OR - Use the below Query
Longops.sql
-----------
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 l.inst_id,l.sid, l.serial#, l.sql_id, l.opname, l.username, l.target, l.sofar, l.totalwork,
l.start_time,l.last_update_time,round(l.time_remaining/60,2) "REMAIN MINS", round(l.elapsed_seconds/60,2) "ELAPSED MINS",
round((l.time_remaining+l.elapsed_seconds)/60,2) "TOTAL MINS", ROUND(l.SOFAR/l.TOTALWORK*100,2) "%_COMPLETE",
l.message,s.sql_text
FROM gv$session_longops l
LEFT OUTER JOIN v$sql s on s.hash_value=l.sql_hash_value and s.address=l.sql_address and s.child_number=0
WHERE l.OPNAME NOT LIKE 'RMAN%' AND l.OPNAME NOT LIKE '%aggregate%' AND l.TOTALWORK != 0 AND l.sofar<>l.totalwork AND
l.time_remaining > 0
/
No comments:
Post a Comment