SQL Commands

SQL Script for oracle tablespace monitoring

Use the below script and alter the threshold,emailadddress and schema username and password for your systems and run it in crontab to send you alerts when the tablespace exceeds the threshold value. We know we can do this by CCMS setup but this is good to have functionality.

DBALIST=”email address”
sqlplus -s <<!
SAPschemausername/password
set feed off
set linesize 100
set pagesize 200
spool tablespace.alert
SELECT F.TABLESPACE_NAME,
TO_CHAR ((T.TOTAL_SPACE – F.FREE_SPACE),’999,999,99′) “USED(MB)”,
TO_CHAR (F.FREE_SPACE, ‘999,999’) “FREE(MB)”,
TO_CHAR (T.TOTAL_SPACE, ‘999,999,999’) “TOTAL(MB)”,
TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),’999,999′)||’ %’ PER_FREE
FROM   (
SELECT       TABLESPACE_NAME,
ROUND (SUM (BLOCKS*(SELECT VALUE/1024
FROM V\$PARAMETER
WHERE NAME = ‘db_block_size’)/1024)
) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME
) F,
(
SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES/1048576)) TOTAL_SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME
) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME
AND FREE_SPACE < (size below which it should alert,exe 5000mb)

AND T.TABLESPACE_NAME = ‘tablespacename you want to monitor’;
spool off
exit
!
if [ `cat tablespace.alert|wc -l` -gt 0 ]
then
cat tablespace.alert > tablespace.tmp
mailx -s “TABLESPACE ALERT-PLEASE TAKE ACTION ” $DBALIST < tablespace.tmp
fi

query to find free space in a tablespace

col “Tablespace” for a22
col “Used MB” for 99,999,999
col “Free MB” for 99,999,999
col “Total MB” for 99,999,999

select df.tablespace_name “Tablespace”,
totalusedspace “Used MB”,
(df.totalspace – tu.totalusedspace) “Free MB”,
df.totalspace “Total MB”,
round(100 * ( (df.totalspace – tu.totalusedspace)/ df.totalspace))
“Pct. Free”
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;