USEFULL SQL FOR TABLESPACE ALERTS
NORMAL TABLESPACES
To Find % of free space left
select
(BYTES/1024)/1024 "Used Space(MB)",
total "allocated size(MB)",
maxi "maximum allowable (MB)",
maxi-(BYTES/1024)/1024 "effectivefree(MB)",
--maxi-total "free(MB)",
round(((maxi-(BYTES/1024)/1024)/maxi)*100,2) "% FREE"
from
SM$TS_USED,(select sum((BYTES/1024)/1024)
total,sum((decode(MAXBYTES,0,bytes,maxbytes)/1024)/1024) maxi from
dba_data_files where tablespace_name in ('&&tbs')) where
tablespace_name in ('&tbs');
To list all the datafiles of a given tablespace
col file_name for a40
set pagesize 100
select file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible from dba_data_files
where tablespace_name='&tablespace_name' order by file_name ;
To add a datafile to a given tablespace through script
cd /ptsadmin/common/OHSMON
add_data_file -oraclehome=$ORACLE_HOME -oraclesid=$ORACLE_SID -tablespaces=<ts1>
To add a datafile to a given tablespace manually
alter tablespace &tablespace_name add datafile '&filefullpath' size 200M autoextend on next 20M
maxsize 1800M;
To resize a datafile of a given tablespace
alter database datafile '&filefullpath' autoextend on next 20M maxsize 1800M;
TEMP TABLESPACE
To Check Percentage Usage of Temp Tablespace
select (s.tot_used_blocks/f.total_blocks)*100 as "percent used"
from (select sum(used_blocks) tot_used_blocks
from v$sort_segment where tablespace_name='TEMP') s,
(select sum(blocks) total_blocks
from dba_temp_files where tablespace_name='TEMP') f;
To check Used Extents ,Free Extents available in Temp Tablespace
SELECT tablespace_name, extent_size, total_extents, used_extents,free_extents, max_used_size
FROM v$sort_segment;
To list all tempfiles of Temp Tablespace
col file_name for a40
select file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible from dba_temp_files order by
file_name;
To add tempfile to Temp Tablespace
alter tablespace temp add tempfile '&tempfilepath' size 1800M;
To resize the tempfile in Temp Tablespace
alter tablespace TEMP resize tempfile '&tempfilepath' size 1800M;
To find Sort Segment Usage by Users
select username,sum(extents) "Extents",sum(blocks) "Block"
from v$sort_usage
group by username;
To find Sort Segment Usage by a particular User
SELECT s.username,s.sid,s.serial#,u.tablespace, u.contents, u.extents, u.blocks
FROM v$session s, v$sort_usage u
WHERE s.saddr=u.session_addr
order by u.blocks desc;
To find Total Free space in Temp Tablespace
select 'FreeSpace ' || (free_blocks*8)/1024/1024 ||' GB' from v$sort_segment where
tablespace_name='TEMP';
To find Total Space Allocated for Temp Tablespace
select 'TotalSpace ' || (sum(blocks)*8)/1024/1024 ||' GB' from dba_temp_files where
tablespace_name='TEMP';
UNDO TABLESPACE
To find Percentage Usage of Undo Tablespace which considers Expired Space
SELECT d.tablespace_name, round(((NVL(f.bytes,0) + (a.maxbytes - a.bytes))/1048576+
u.exp_space),2)
as max_free_mb, round(((a.bytes - (NVL(f.bytes,0)+ (1024*1024*u.exp_space)))*100/a.maxbytes),2)
used_pct FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes,
sum(greatest(maxbytes,bytes)) maxbytes from sys.dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes from sys.dba_free_space group by tablespace_name) f ,
(select tablespace_name , sum(blocks)*8/(1024) exp_space from
dba_undo_extents where status NOT IN ('ACTIVE','UNEXPIRED') group by tablespace_name) u
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+)
AND d.tablespace_name=u.tablespace_name AND d.contents = 'UNDO' AND u.tablespace_name =
(select UPPER(value)
from v$parameter where name = 'undo_tablespace');
To show ACTIVE/EXPIRED/UNEXPIRED Extents of Undo Tablespace
select tablespace_name,
status,
count(extent_id) "Extent Count",
sum(blocks) "Total Blocks",
sum(blocks)*8/(1024*1024) total_space
from dba_undo_extents
group by tablespace_name, status;
To show UndoRetention Value
Show parameter undo_retention;
To check space related statistics of UndoTablespace from stats$undostat of 90 days
select
UNDOBLKS,BEGIN_TIME,MAXQUERYLEN,UNXPSTEALCNT,EXPSTEALCNT,NOSPACEER
RCNT from stats$undostat where BEGIN_TIME between sysdate-90 and sysdate and
UNXPSTEALCNT > 0;
To check space related statistics of UndoTablespace from v$undostat
select
sum(ssolderrcnt) "Total ORA-1555s",
round(max(maxquerylen)/60/60) "Max Query HRS",
sum(unxpstealcnt) "UNExpired STEALS",
sum(expstealcnt) "Expired STEALS"
from v$undostat
order by begin_time
To check for Active Transactions
set head on
select usn,extents,round(rssize/1048576)
rssize,hwmsize,xacts,waits,optsize/1048576 optsize,shrinks,wraps
from v$rollstat where xacts>0
order by rssize;
To list all Datafile of UndoTablespace
select tablespace_name,file_name,file_id,autoextensible,bytes/1048576
Mbytes, maxbytes/1048576 maxMbytes
from dba_data_files
where tablespace_name like '%UNDO%'
or tablespace_name like '%RBS%'
order by tablespace_name,file_name;
To find MaxQueryLength from stats$undostat
Select Max(MAXQUERYLEN) from stats$undostat
USEFULL SQL FOR CPU ALERTS
To Find Session Information Details based on SID or SPID or CLIENTPID
col program for a15F
col machine for a15
col terminal for a15
set lines 152
select s.sid,
s.serial#,
'*'||s.process||'*' Client,
p.spid Server,
s.sql_address,
s.sql_hash_value,
s.username,
s.action,
s.program || s.module,
s.terminal,
s.machine,
s.status,
--s.last_call_et
s.last_call_et/3600
from gv$session s, gv$process p
where p.addr=s.paddr and
s.sid=nvl('&sid',s.sid) and
p.spid=nvl('&spid',p.spid) and
nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));
To Find Wait Events for a given Session
column seq# format 99999
column EVENT format a30
column p2 format 9999
column STATE format a10
column WAIT_T format 9999
select SID,SEQ#,EVENT,P1,P2,WAIT_TIME WAIT_T,SECONDS_IN_WAIT,STATE
from gv$session_wait
where sid = '&sid' ;
To find Sql Text given SQLHASH & SQLADDR
select piece,sql_text from v$sqltext where HASH_VALUE = &hash and ADDRESS ='&addr' order by
piece;
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='&sessionid';
To Find Forms User Session Details Given ClientProcess id
SELECT /*+ ORDERED FULL(fl) FULL(vp) USE_HASH(fl vp) */
( SELECT SUBSTR ( fu.user_name, 1, 20 )
FROM apps.fnd_user fu
WHERE fu.user_id = fl.user_id
) user_name,
TO_CHAR ( fl.start_time, 'DD-MON-YYYY HH24:MI' ) login_start_time,
SUBSTR ( fl.process_spid, 1, 6 ) spid,
SUBSTR ( TO_CHAR ( fl.pid ), 1, 3 ) pid,
SUBSTR ( vs.process, 1, 8 ) f60webmx,
SUBSTR ( TO_CHAR ( rf.audsid ), 1, 6 ) audsid,
SUBSTR ( TO_CHAR ( vs.sid ), 1, 3 ) sid,
SUBSTR ( TO_CHAR ( vs.serial#), 1, 7 ) serial#,
SUBSTR ( vs.module || ' - ' ||
( SELECT SUBSTR ( ft.user_form_name, 1, 40 )
FROM apps.fnd_form_tl ft
WHERE ft.application_id = rf.form_appl_id
AND ft.form_id = rf.form_id
and ft.language='US'
), 1, 40 ) form
FROM apps.fnd_logins fl,
gv$process vp,
apps.fnd_login_resp_forms rf,
gv$session vs
--fnd_form_tl ft
WHERE fl.end_time IS NULL
AND fl.start_time > sysdate - 31 /* login within last 7 days */
AND fl.login_type = 'FORM'
AND fl.process_spid = vp.spid
AND fl.pid = vp.pid
AND fl.login_id = rf.login_id
AND rf.end_time IS NULL
AND rf.audsid = vs.audsid
AND vs.process='&1'
ORDER BY
user_name,
login_start_time,
spid,
pid,
f60webmx,
sid,
serial#;
To find Concurrent Requests which are Running Normal
REM Script provides the request details corresponding to any Process-id
REM which can be acquired from the top sessions
set lines 180
set pages 1000
set verify off
undef spid
column req_id format 99999999999
column OPID format a10
column PPID format a8
column SPID format a8
column ST_CD format a1
column ph_cd format a1
column CNAME format a30
column event format a15
column user_name format a10
column program format a8
column serial# format 999999
column sid format 9999
column username format a8
select a.request_id "REQ_ID",a.oracle_process_id "OPID",a.os_process_id "PPID",
e.user_concurrent_program_name "CNAME",
f.user_name,a.status_code "ST_CD",a.phase_code "PH_CD", b.username,b.sid,
b.serial#,b.program,g.event,
to_char(a.ACTUAL_START_DATE,'MON-DD-HH-MI-SS') START_DATE,
to_char(a.ACTUAL_COMPLETION_DATE,'MON-DD-HH-MI-SS') COMPL_DATE
from apps.fnd_concurrent_requests a,(select c.username,c.sid,c.serial#,
c.program,d.spid from v$session c, v$process d
where c.paddr=d.addr) b,
apps.fnd_concurrent_programs_tl e,
apps.fnd_user f,
v$session_wait g
where a.oracle_process_id=b.spid
and a.concurrent_program_id=e.concurrent_program_id
and e.language='US'
and a.requested_by=f.user_id
and b.sid=g.sid
and a.status_code='R'
and a.phase_code='R';
To find Avg,Max,Min times of a concurrent Program with completed Normal status in Past of a
given Concurrent Program
set linesize 200
col username for a10
col status for a10
col phase for a10
col PNAME for a70
col request_id for 99999999999
col PNAME for a40
select
b.user_name username,
a.USER_CONCURRENT_PROGRAM_NAME as PNAME,
avg((nvl(ACTUAL_COMPLETION_DATE,sysdate)-a.REQUESTED_START_DATE)*24)
avg_Hrs_running,
max((nvl(ACTUAL_COMPLETION_DATE,sysdate)-a.REQUESTED_START_DATE)*24)
Max_Hrs_running,
min((nvl(ACTUAL_COMPLETION_DATE,sysdate)-a.REQUESTED_START_DATE)*24)
Min_Hrs_running
from
apps.fnd_conc_req_summary_v a,
apps.fnd_user b
where
phase_code = 'C' and status_code = 'C' and
a.REQUESTED_START_DATE > sysdate-30 and
upper(a.USER_CONCURRENT_PROGRAM_NAME) like upper('%&str%') and
a.REQUESTED_BY=b.user_id
group by b.user_name,a.USER_CONCURRENT_PROGRAM_NAME;
REFERENCE TARS
Temp Tablespace Issues
4961677.992
15846770.6
15633722.6
15643864.6
15705134.6
Undo Tablespace Issues
15617880.6
Forms Process Run away Issues
15847188.6
15607192.6
15622794.6
15628664.6
15631236.6
15642018.6
15639904.6
15648138.6
15697214.6
Java Process Run Away Issues
15734702.6
15744516.6
4959343.993
15606708.6
15606910.6
15852604.6
Oracle BackGround Process Run Away Issues
15601628.6
15602400.6
15604016.6
15607078.6
15612108.6
15620910.6
15628672.6
15629660.6
15626468.6
15633822.6
15639350.6
15641846.6
15702486.6
High CPU Utilisation Tars
15639876.6
15639818.6
15640526.6
15642328.6
15647524.6
15687940.6
15688126.6
15685098.6
15676152.6
15695108.6
15697680.6
15703244.6
