Monitor Tablespace space usage

1.Resize the data files into 1x and auto extend up to 2x.

2.Enter the data file size and auto extend size into a table.
SQL> desc tablespace_space_usage;
Name Null? Type
----------------------------------------- -------- ----------------------------
INSTANCE_NAME NOT NULL VARCHAR2(10)
TABLESPACE_NAME NOT NULL VARCHAR2(30)
CURRENT_SPACE NUMBER
MAX_AUTO_EXTEND_SPACE NUMBER

3. Setup a cron job to Monitor the tablespace space usage.
monitor_tbs_space.sh
#!/usr/bin/ksh
####################################################################
# This shell script is used to monitor the tablespace space usage.
# This shell script calls the monitor_tbs_space.sql and monitors the tbs space usage.
# $1 is the db link to the source db.
# $2 is the db name where the table is created.
# $3 is the db to be monitered.
#####################################################################
. /oracle/.102boraenv
opwd=`cat $HOME/space/.infodb.pw`
export opwd
dba_team='anbus@1234.com'
dtstamp=`date +%m-%d-%Y:%H:%M:%S`
FILE_DIR=$HOME/space
sqlplus -s < $FILE_DIR/monitor_$3_tbs_$dtstamp.lst 2>&1
$opwd@$2
set echo off
set feedback off
set verify off
start $FILE_DIR/monitor_tbs_space.sql $1
END1
/usr/bin/cat $FILE_DIR/monitor_$3_tbs_$dtstamp.lst grep IMPORTANT > /dev/null 2>&1
if [ $? = 0 ]
then
mailx -s "!!!!!!!!!!!!-IMPORTANT TABLESPACE SPACE PROBLEM IN $3 on $dtstamp." $dba_team< $FILE_DIR/monitor_$3_tbs_$dtstamp.lst
###else###mailx -s "COOL $3 TBS SPACE ARE FINE." $dba_team< $FILE_DIR/monitor_$3_tbs_$dtstamp.lst
fi
rm $FILE_DIR/monitor_$3_tbs_$dtstamp.lst
exit

monitor_tbs_space.sql
set serveroutput on size 1000000
define db_link = &1;
DECLARE
CURSOR tbs_space_cur is
SELECT tablespace_name, sum(bytes)/1024/1024 "USED"
FROM DBA_DATA_FILES@&&db_link
WHERE tablespace_name not in ('TEMP','TEMP1','UNDOTBS1')
GROUP BY tablespace_name
ORDER BY tablespace_name;
v_tbs_space_rec tbs_space_cur%rowtype;
v_sqlerrm varchar2(200) := null;
v_sqlcode number := 0;
v_instance_name varchar2(50):= null;
v_max_space number:= 0;
v_used_percent number:= 0;
v_used_percent1 number:= 0;
BEGIN
IF tbs_space_cur%isopen
then
CLOSE tbs_space_cur;
END IF;
OPEN tbs_space_cur;
LOOP
FETCH tbs_space_cur into v_tbs_space_rec;
EXIT when tbs_space_cur%notfound;
SELECT upper(instance_name) into v_instance_name from v$instance@&&db_link;
SELECT max_auto_extend_space into v_max_space
FROM TABLESPACE_SPACE_USAGE
WHERE upper(instance_name) = upper(v_instance_name)
AND upper(tablespace_name) = upper(v_tbs_space_rec.tablespace_name);
v_used_percent := ((v_tbs_space_rec.Used)/ (v_max_space))*100 ;
v_used_percent := round(v_used_percent);
IF v_used_percent > 90
then
dbms_output.put_line ( '!!!!!!!! IMPORTANT THE TABLESPACE SPACE IS NEARING FULL.'); dbms_output.put_line (v_used_percent '% used in ' v_tbs_space_rec.tablespace_name' TABLESPACE of ' v_instance_name);
dbms_output.put_line('-----------------------------------------------------------------------');
ELSE
dbms_output.put_line ( 'THE TABLESPACES ARE FINE IN ' v_instance_name ); dbms_output.put_line (v_used_percent '% used in ' v_tbs_space_rec.tablespace_name' TABLESPACE of ' v_instance_name);
dbms_output.put_line('------------------------------------------------------------------------');
END IF;
END LOOP;
CLOSE tbs_space_cur;
EXCEPTION
WHEN OTHERS THEN
V_SQLERRM := SUBSTR(SQLERRM,1,200);
V_SQLCODE := SQLCODE;
DBMS_OUTPUT.PUT_LINE ('ERROR 'V_SQLERRM V_SQLCODE);
END;
/