Tablespace monitoring

 set linesize 180

select b.db_unique_name, a.tablespace_name

               ,sum(a.tots)/1024/1024/1024 "Total Size(GB)"

               ,sum(a.sumb)/1024/1024/1024 "Total Free(GB)"

           ,sum(a.sumb)*100/sum(a.tots) "% Free"

                    ,sum(a.tots-a.sumb)*100/sum(a.tots) "% Used"

          ,sum(a.largest)/1024/1024/1024 "Max Free"

           from

          (

            select tablespace_name

               ,0 tots

               ,sum(bytes) sumb

              ,max(bytes) largest

        from dba_free_space a

      group by tablespace_name

      union

      select tablespace_name

            ,sum(bytes) tots

            ,0

            ,0

       from dba_data_files

      group by tablespace_name) a , v$database b , v$instance c

 where a.tablespace_name NOT like 'UNDO%' and c.INSTANCE_NUMBER=1

        group by a.tablespace_name,b.db_unique_name,c.INSTANCE_NUMBER

having sum(a.sumb)*100/sum(a.tots) > 50

and sum(a.tots)/1024/1024/1024 > 100

order by sum(a.tots)/1024/1024/1024 desc

     ;

Note: use gv$instance if it is a rac

2. tablespace_monitoring.sh

HOST=`hostname | cut -b1-14`

dt=`date +%d%m%Y`

BASE_DIR=/u01/app/oracle/scripts/tablespace_monitoring

LOG_DIR=/u01/app/oracle/scripts/tablespace_monitoring/LOG

SPOOL_FILE=${LOG_DIR}/Tablespace_status_${dt}.html

ps -ef|grep smon|grep -v grep |grep -v ASM |awk '{print $8}'|cut -b10-20 > ${BASE_DIR}/NONPROD_DATABASE_LIST.txt

while read line

do

export ORACLE_SID=$line

export ORACLE_HOME=`grep -w "$line" /etc/oratab | awk -F":" '{print $2}'`

echo $line

#$ORACLE_HOME/bin/sqlplus -s / as sysdba  << EOF > ${TEMP_SPOOL}

$ORACLE_HOME/bin/sqlplus -s -M "HTML ON" "/ as sysdba"<<EOF >> ${SPOOL_FILE}

set space 0

set feedback off

set echo off

set serveroutput off

@'$BASE_DIR/datafile_monitoring.sql';

exit

EOF

done <${BASE_DIR}/NONPROD_DATABASE_LIST.txt

fline=`wc -l ${SPOOL_FILE}|awk '{print $1}'`

if [ $fline -gt 0 ]

then

${BASE_DIR}/mail.sh ${SPOOL_FILE} ${HOST}

fi

Testing data

1.CREATE TABLE HR.RTABLE ( RID NUMBER PRIMARY KEY, NOTES VARCHAR2(20));

2.create a procedure that will populate the table.

CREATE PROCEDURE HR.POPULATE_RTABLE ( N NUMBER ) 
IS 
/* this procedure inserts N records into RTABLE */ 
V INTEGER; 
BEGIN 
-- get the maximum ID from the table 
SELECT MAX(RID) INTO V FROM RTABLE; 
FOR I IN 1..N LOOP 
INSERT INTO RTABLE (RID, NOTES) VALUES(I, 'record ' || TO_CHAR(I)); 
-- end the transactions every 5 inserts 
IF MOD(I,5)=0 THEN 
COMMIT; 
END IF; 
END LOOP; 
COMMIT; 
END POPULATE_RTABLE; 
/  

13. Populate the testing table with a few thousands rows. 

SQL> exec HR.POPULATE_RTABLE ( 10000 ) 

SQL> SELECT COUNT(*) FROM HR.RTABLE ;