Anyone have script to show tablespace > 90% used

[Tison]

I am trying to write a database check that will send me an email message if there is any tablespace > 90% used.
The email is not the problem. The problem is that I am trying to return either a TRUE flag or a count of tablespaces where % used > 90.
Anyone got such a thing ?

 

[SantaMufasa]

Tison,

Have you considered putting your tablespace data files in AUTOEXTEND mode, and allocating several such files to your tablespace? There is certainly no penalty to pay for such a strategy and your space allocations are on a "just-in-time" basis, thus making best use of your disk space. As long as your Operating-system disk allocations stay ahead of your overall Oracle disk consumption, then you should be in good shape.

If you wish to monitor your tablespace consumption, you can run my script (in Section 2, below) that produces the output, below, in Section 1 (I've widened the screen display to display all output for the same line on the same:

Section 1 -- Sample Tablespace-file-consumption display:

                                                                                         % Free
                                                                                             of
                                Total            Total                                     Pot.
Tablespace                  Potential          Current                         Potential  Total Auto
Name               #        File Size        File Size       Bytes Used       Bytes Free  Bytes Ext. Filename
--------------- ---- ---------------- ---------------- ---------------- ---------------- ------ ---- ---------------------------------------------------------
CERT               4    2,097,152,000    2,097,152,000    2,097,152,000                0      0 Yes  '/dbsrv/oracle/data1/oradata/SAV9204/SAV9204cert01.dbf'
CERT               5    2,097,152,000    2,097,152,000    2,097,152,000                0      0 Yes  '/dbsrv/oracle/data1/oradata/SAV9204/SAV9204cert02.dbf'
CERT               6    2,097,152,000    2,097,152,000    2,097,152,000                0      0 Yes  '/dbsrv/oracle/data1/oradata/SAV9204/SAV9204cert03.dbf'
CERT               7    2,097,152,000    2,097,152,000    2,097,152,000                0      0 Yes  '/dbsrv/oracle/data1/oradata/SAV9204/SAV9204cert04.dbf'
CERT               8    2,097,152,000      534,773,760      534,249,472    1,562,902,528     74 Yes  '/dbsrv/oracle/data1/oradata/SAV9204/SAV9204cert05.dbf'
CERT               9    2,097,152,000      419,430,400      419,430,400    1,677,721,600     80 Yes  '/dbsrv/oracle/data1/oradata/SAV9204/SAV9204cert06.dbf'
CERT              10    2,097,152,000      377,487,360      377,487,360    1,719,664,640     82 Yes  '/dbsrv/oracle/data1/oradata/SAV9204/SAV9204cert07.dbf'
RBS                2    2,097,152,000      325,058,560       62,980,096    2,034,171,904     96 Yes  '/dbsrv/oracle/data1/oradata/SAV9204/SAV9204rbs01.dbf'
SYSTEM             1    2,097,152,000      220,200,960      212,238,336    1,884,913,664     89 Yes  '/dbsrv/oracle/data1/oradata/SAV9204/SAV9204system01.dbf'
TEMP               3    2,097,152,000       52,428,800       46,514,176    2,050,637,824     97 Yes  '/dbsrv/oracle/data1/oradata/SAV9204/SAV9204temp01.dbf'
                     ---------------- ---------------- ---------------- ----------------
sum                    20,971,520,000   10,317,987,840   10,041,507,840   10,930,012,160

Note: Physical disk limitations may prevent an AUTOEXTEND-ing file from reaching its potential

Wrote spool file "TablespaceUsage.txt".
**************************************************************************************************************************************************************

Section 2 -- Code to produce above output:
[coce]
set echo on
REM **************************************************************
REM David L. Hunt (file author) distributes this and other
REM files/scripts for educational purposes only, to illustrate the
REM use or application of various computing techniques. Neither the
REM author nor Dasages, LLC, makes any warranty regarding this
REM script's fitness for any industrial application or purpose nor is
REM there any claim that this or any similarly-distributed scripts
REM are error free or should be used for any purpose other than
REM illustration.
REM **************************************************************
set echo off
set linesize 165
set pagesize 40
set heading on
col fname heading "Filename" format a60
col fnum heading "#" format 999
col ts heading "Tablespace|Name" format a15
col tb heading "Total|Potential|File Size" format 999,999,999,999
col cb heading "Total|Current|File Size" like tb
col used heading "Bytes Used" like tb
col free heading "Potential|Bytes Free" like tb
col autoext heading "Auto|Ext." format a4
col percentfree heading "% Free|of|Pot.|Total|Bytes" format 999
break on report
compute sum of tb cb used free on report
spool TablespaceUsage.txt
select substr(tablespace_name,1,15) ts
,d.file_id fnum
,decode(e.file#,null,d.bytes,(e.maxextend * blksize)) tb
,d.bytes cb
,decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)) used
,decode(e.file#,null,d.bytes,(e.maxextend * blksize))
-decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)) free
,trunc(((decode(e.file#,null,d.bytes,(e.maxextend * blksize))
-decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)))/
decode(e.file#,null,d.bytes,(e.maxextend * blksize))
),2) * 100 percentfree
,decode(e.file#,null,'No','Yes') autoext
,''''||substr(file_name,1,55)||'''' fname
from sys.dba_data_files d
,(select file_id,sum(bytes) freebytes
from sys.dba_free_space
group by file_id) f
,sys.filext$ e
,v$datafile v
,(select value blksize from v$parameter
where name = 'db_block_size') b
where d.file_id=f.file_id(+)
and d.file_id=e.file#(+)
and v.file#=d.file_id
order by tablespace_name,creation_time
/
spool off
prompt
prompt Note: Physical disk limitations may prevent an AUTOEXTEND-ing file from reaching its potential
prompt
prompt Wrote spool file "TablespaceUsage.txt".
prompt
[/code]

Let us know if this strategy contributes to a resolution for your need.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 08:09 (08Dec04) UTC (aka "GMT" and "Zulu"),
@ 01:09 (08Dec04) Mountain Time

 

[Tison]

Thanks for the reply. Great script and if you don't mind I will make use of it
BUT it does not solve my problem.
I am setting up a batch job that must tell me if any tablespaces are heading for problem.
It is an automated script so I must get either a boolean reply or a count of objects.

 

[JvdK]

Simple view, last column shows the space used per tablespace

SELECT d.status
, d.tablespace_name
, d.contents
, d.extent_management "Extent Management"
, TO_CHAR(NVL(a.bytes / 1024 / 1024
, 0)
,'99G999G990D900')
, TO_CHAR(NVL(a.bytes - NVL(f.bytes
, 0)
, 0)/1024/1024
,'99G999G990D900')
, TO_CHAR(NVL((a.bytes - NVL(f.bytes
, 0)) / a.bytes * 100
, 0)
, '990D00')
FROM sys.dba_tablespaces d
, (
SELECT tablespace_name
, SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) a
, (
SELECT tablespace_name
, SUM(bytes) bytes
FROM dba_free_space
GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND NOT (d.extent_management LIKE 'LOCAL'
AND d.contents LIKE 'TEMPORARY')
UNION ALL
SELECT d.status
, d.tablespace_name
, d.contents
, d.extent_management
, TO_CHAR(NVL(a.bytes / 1024 / 1024
, 0)
,'99G999G990D900')
, NVL(t.bytes
, 0)/1024/1024 ||'/'||NVL(a.bytes/1024/1024
, 0)
, TO_CHAR(NVL(t.bytes / a.bytes * 100
, 0)
, '990D00')
FROM sys.dba_tablespaces d
, (
SELECT tablespace_name
, SUM(bytes) bytes
FROM dba_temp_files
GROUP BY tablespace_name) a
, (
SELECT tablespace_name
, SUM(bytes_cached) bytes
FROM v$temp_extent_pool
GROUP BY tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management LIKE 'LOCAL'
AND d.contents LIKE 'TEMPORARY'

 

[macswell]

The following Unix shell script invokes a SQL script which lists all tables using over a specified percentage; you should be able to modify it to invoke one of the previous posts. I use cron to run this weekly, monitoring for tables that are filling up (I never use PCTINCREASE > 0 for tables/indexes, or AUTOEXTEND for tablespaces.) It eMails me the results so I can take action as necessary.

Have fun -

#!/bin/sh
#
# File : monitor_table_extents.sh
# Purpose: Finds all tables that have allocated more than a given percentage
# of their extents, and e-mails that list to the DBA for action.
# Expects an Oracle SID and the percentage to check as parameters.
# e.g. "monitor_table_extents.sh PROD 70"

. ~oracle/.profile

if [ $# -ne 2 ]; then
echo "\n$0: Expected two parameters, received $#.\n"
echo " Usage: monitor_table_extents.sh <ORACLE_SID> <Percentage>\n"
exit
fi

DBA="<your eMail address>"
MAIL=/usr/bin/mailx

SID=`echo $1 | tr 'a-z' 'A-Z'`
ORACLE_SID=${SID}; export ORACLE_SID
ORAENV_ASK=NO
. oraenv

PCT=`echo $2`

DATE=`date +%Y_%m_%d`
RPT_DIR=~oracle/DBA/reports/weekly/${ORACLE_SID}
SCRIPT_DIR=~oracle/DBA/scripts
SPOOLFILE=${RPT_DIR}/${DATE}_table_extents.txt

sqlplus -silent /nolog << EOI
connect / as sysdba
spool ${SPOOLFILE}
@${SCRIPT_DIR}/monitor_table_extents.sql "${PCT}"
quit
EOI

$MAIL -s ${SID}' table extents' $DBA < ${SPOOLFILE}