To check the long operation
set verify off
col opname format a40 trunc
col pctdone format 999
col mintogo format 9,999.90
accept trgtsid number default 0 prompt 'Limit to which SID : '
select s.sid,o.opname,s.sql_hash_value,o.sofar,o.totalwork,o.elapsed_seconds,
round(o.time_remaining/60,2) mintogo,
round(o.sofar/o.totalwork * 100,0) pctdone, o.message
from v$session_longops o, v$session s
where o.sid = s.sid
and sofar < totalwork
and (o.sid = &trgtsid or &trgtsid = 0)
/
-- sid generated archives
col value for 999999999999
select *
from ( select a.username,a.sid,b.value
from v$session a,v$sesstat b,v$statname c
where a.sid=b.sid
and b.statistic#=c.statistic#
and c.name='redo size'
order by b.value desc )
where rownum < 11;
SID EVENT
+++++++
set verify off
col event format a35
col total_waits format 999999999
col time_waited format 999999.90
accept trgtsid number default 0 prompt 'What is the SID:'
prompt Note - time is in seconds
select event,
e.total_waits,
e.time_waited
from v$session_event e
where e.total_waits > 0
and (e.sid = &trgtsid and &trgtsid > 0)
and e.event not like '%time%'
and e.event not like 'SQL*Net%client'
and e.event not like 'pipe%'
and e.event not like '%ipc%'
/
/* sidtime.sql
check sid for last call et
*/
col osuser format a10 trunc
col LastCallET format a11
col sid format 9999
col username format a10 trunc
col uprogram format a25 trunc
set linesize 132
set verify off
accept trgtsid number default 0 prompt 'What is the SID : '
select to_char(s.logon_time, 'mm/dd hh:mi:ssAM') loggedon,
s.sid, s.status,
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) "LastCallET",
s.username, s.osuser,
p.spid, s.module || ' - ' || s.program uprogram, s.sql_hash_value
from v$session s, v$process p
where p.addr = s.paddr
and s.sid = &trgtsid
and &trgtsid > 0;
/* top10active.sql
shows the top 10 longest-active user sessions
*/
col osuser format a10 trunc
col LastCallET format 99,999
col username format a10 trunc
col uprogram format a25 trunc
col machine format a10 trunc
set linesize 132 pages 20
set verify off
accept trgtuser char default ALL prompt 'Limit to what userid <ALL> : '
select * from (
select to_char(s.logon_time, 'mm/dd hh:mi:ssAM') loggedon,
s.sid, s.status,
floor(last_call_et/60) "LastCallET",
s.username, s.osuser,
p.spid, s.module || ' - ' || s.program uprogram,
s.machine, s.sql_hash_value
from v$session s, v$process p
where p.addr = s.paddr
and s.type = 'USER'
and s.username is not null
and s.status = 'ACTIVE'
and (s.username = upper('&trgtuser') or upper('&trgtuser') = 'ALL')
order by 4 desc)
where rownum < 31;
To check single tablespace details
++++++++++++++++++++++++
column file_name format a55
set linesize 132
set pages 200
column tablespace_name format a11
set verify off
accept tbs prompt 'What is the Tablespace Name: '
SELECT dts.tablespace_name,
NVL(ddf.bytes / 1024 / 1024, 0) avail,
NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024 used,
NVL(dfs.bytes / 1024 / 1024, 0) free,
TO_CHAR(NVL((ddf.bytes - NVL(dfs.bytes, 0)) / ddf.bytes * 100, 0), '990.00')
"Used %" ,
TO_CHAR(NVL((ddf.bytes - NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)) / ddf.bytes
* 100, 0), '990.00') free_pct,
decode(sign(
(NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024)/0.85 - NVL(ddf.bytes / 1024 /
1024, 0)),-1,0,(NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024)/0.85 - NVL(ddf.bytes / 1024 / 1024, 0)) "Required MB"
FROM
sys.dba_tablespaces dts,
(select tablespace_name, sum(bytes) bytes
from dba_data_files group by tablespace_name) ddf,
(select tablespace_name, sum(bytes) bytes
from dba_free_space group by tablespace_name) dfs
WHERE
dts.tablespace_name = ddf.tablespace_name(+)
AND dts.tablespace_name = dfs.tablespace_name(+)
and dts.tablespace_name = '&tbs';
select file_name,bytes/1024/1024 from dba_data_files
where tablespace_name = '&tbs';
Check Hung update queries
+++++++++++++++++
column LOCKED_OBJECT format A35 wrapped
column ORACLE_USERNAME format A17 wrapped
column OS_USER_NAME format A12 wrapped
column SESSION_ID format 999999 wrapped
column SINCE format A21
select o.OWNER || '.' || o.OBJECT_NAME as LOCKED_OBJECT,
lo.ORACLE_USERNAME,
lo.OS_USER_NAME,
lo.SESSION_ID,
lo.PROCESS,
case lo.LOCKED_MODE
when 0 then 'none'
when 1 then 'null (NULL)'
when 2 then 'row-S (SS)'
when 3 then 'row-X (SX)'
when 4 then 'share (S)'
when 5 then 'S/Row-X (SSX)'
when 6 then 'exclusive (X)'
end as LOCKED_MODE,
cast(sysdate-(CTIME/(24*60*60)) as timestamp(0)) as SINCE
from DBA_OBJECTS o,
V$LOCKED_OBJECT lo,
V$LOCK l
where o.OBJECT_ID = lo.OBJECT_ID
and lo.OBJECT_ID = l.ID1 and lo.SESSION_ID = l.SID
order by LOCKED_OBJECT, ORACLE_USERNAME, OS_USER_NAME, SESSION_ID;
-- Description
: print details for user sid and cpu consumption per sid.
set lines 180
select nvl(ss.USERNAME,'ORACLE PROC') username,
se.SID,ss.program ,
VALUE cpu_usage
from v$session ss,
v$sesstat se,
v$statname sn
where se.STATISTIC# = sn.STATISTIC#
and NAME like '%CPU used by this session%'
and se.SID = ss.SID
and ss.SID = &SID
order by VALUE desc;
---- sid details ---
set verify off pages 10 linesize 132
col sid format 99999
col machine format a10
col program format a25 trunc
col username format a10
col logontime format a15
col osuser format a10 trunc
col proginfo format a30 trunc
accept trgtsid number default 0 prompt 'What is the SID : '
select to_char(s.logon_time,'mm/dd hh24:mi:ss') logontime,
s.sid,s.serial#,s.status,s.type,s.username,s.osuser,s.machine,
s.module || ' - ' || s.program proginfo,
s.process,p.spid, s.sql_hash_value, s.action
from v$session s, v$process p
where sid = &trgtsid
and p.addr = s.paddr;
-- description : Check whats going on tablespace who is eating up the space.
SELECT s.sid, s.status, sq.address, sq.sql_id, sq.sql_text
FROM v$session s, v$sql sq
WHERE s.status = 'ACTIVE'
and s.sql_hash_value = sq.hash_value
and s.sql_address = sq.address
and s.sid in (Select sid from v$session
where program not like '%oracle%' and username in (select username from dba_users where default_tablespace='&tbspace_name'));
SID TIME
+++++++++++
col osuser format a10 trunc
col LastCallET format a11
col sid format 9999
col username format a10 trunc
col uprogram format a25 trunc
set linesize 132
set verify off
accept trgtsid number default 0 prompt 'What is the SID : '
select to_char(s.logon_time, 'mm/dd hh:mi:ssAM') loggedon,
s.sid, s.status,
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) "LastCallET",
s.username, s.osuser,
p.spid, s.module || ' - ' || s.program uprogram, s.sql_hash_value
from v$session s, v$process p
where p.addr = s.paddr
and s.sid = &trgtsid
and &trgtsid > 0;
--------Undo usage----------
Select status,sum(bytes)/1048576 "Bytes in MB"
from dba_undo_extents
where tablespace_name=(select VALUE from v$parameter where name = 'undo_tablespace')
group by status;
Note: Make sure your putty software and the created .bat files (env files) should in the same location.
