Oracle monitors tablespaces and automatically adds data file scripts

Oracle monitors the tablespace and automatically adds the SQL code of the Data File Script --- create view --- percentage create view tablespace_used_percent as select useage from (select. tablespace_name,. file_name,. total "Total (MB)", round (. total-b.Free_Space) "Used (MB)", round (. total-b.Free_Space)/. total) * 100,2) as useage,. auto_extend from (select FILE_ID, tablespace_name, file_name, bytes/(1024*1024) Total, AUTOEXTENSIBLE auto_extend from dba_data_files Ddf) a, (select file_id, sum (bytes)/(1024*1024) Free_Space from dba_free_space group by file_id) B where. file_id = B. file_id) where tablespace_name = 'your TABLESPACE name'; --- deails create view TABLESPACE_USAGE as select. tablespace_name,. file_name,. total "Total (MB)", round (. total-b.Free_Space) "Used (MB)", round (. total-b.Free_Space)/. total) * 100,2) "Used (%)",. auto_extend from (sele Ct FILE_ID, tablespace_name, file_name, bytes/(1024*1024) Total, AUTOEXTENSIBLE auto_extend from dba_data_files ddf) a, (select file_id, sum (bytes)/(1024*1024) free_Space from dba_free_space group by file_id) B where. file_id = B. file_id; linux script Java code #################################### ################################### checkTabsp. sh #### This Script will add the new datafile if Tablespace's Data # file, which is greater than the 80% of one datafiles size ############################## ########################################! /Bin/bash usedPercentNO = ('sqlplus-S'/as sysdb' <\ eof set heading OFF; SET verify OFF; SELECT * FROM tablespace_used_percent; EOF ') # get the length of array len =$ {# usedPercentNO [*]} echo "The array has $ len members. "I = 0 while [$ I-lt $ len]; do echo" $ I: $ {usedPercentNO [$ I]} "arrNo = 'echo" $ {usedPercentNO [$ I]} "| awk-F. '{print $1}' if [-z $ arrNo] then arrNo = 1 fi # if usedPercentNo> = 80 t Login we add new data file, which will have 8G size if [$ arrNo-gt 80] then let sigNo = $ I + 1 sqlplus-s "/as sysdba" <EOF ALTER TABLESPACE DB_TABLESPACE ADD DATAFILE '/ opt/oracle/oradata/DB/DB_DATA $ sigNo. dbf 'size 2g autoextend on maxsize 8G; EOF # we need send email to report the tablespace stats info sqlplus-s "/as sysdba" <\ EOF col tablespace_name for a30 col file_name for a60 col auto_extend For a12 col tablespace_name justify center col file_name justify center col autoextend justify right set linesize 200 set pagesize 500 SPOOL tablespace. alert SELECT * FROM TABLESPACE_USAGE; spool off; exit eof fi let I ++ done # we needn't send email from there the crontab will do if ['cat tablespace. alert | wc-l'-gt 0] then cat tablespace. alert> tablespace. tmp mailx-s "tablespace alert for DB" E MAIL-ADDRESS <tablespace. the script above alert fi will cause the script to keep adding data files if there is more than 80% files .... after the modification, the SQL statement is replaced by a text file instead of the previously used view Java code #! /Bin/bash # Managed by Puppet ################################ ####################################### checkTabsp. sh #### This Script will add the new datafile if BOCC Tablespace's data # file, which is greater than the 80% of one datafiles size ############################## ######################################## avoid have the script run if already running source/opt/app/inc/some_functions.sh pkgfile =/tmp/checkTabsp. pgrp check_if_running # end source/home/oracle /. profile usedDatafileNO = ('sqlplus-S'/as sysdba '<\ eof set heading OFF; SET verify OFF; @/opt/app/SQL/chktabspused. SQL EOF ') # check whether it needs add data file if [$ usedDatafileNO-eq 0] then usedDatNO = ('sqlplus-S'/as sysdb' <\ eof set heading OFF; SET verify OFF; @/opt/app/SQL/chkdatno. SQL EOF ') let sigNO = $ usedDatNO + 1 sigNO = 'printf "% 03d" $ signo' sqlplus-s "/as sysdba" <eof alter tablespace DB_TABLESPACE add datafile '/opt/oracle/ oradata/DB/DB_DATA $ sigNO. dbf 'size 500 m autoextend on next 50 M; EOF # we need send email to report the tablespace stats info to check whether add data file successful sqlplus-s "/as sysdba" <\ EOF col tablespace_name for a30 col file_name for a60 col auto_extend for a12 col tablespace_name justify center col file_name justify center col autoextend justify right set linesize 200 set pagesize 500 @/opt/bocc/SQL/chktabspstats. SQL EXIT EOF # out put the disk space useage df-h fi # we will don't send email from there the crontab will do # if ['cat tablespace. alert | wc-l'-gt 0] # then # cat tablespace. alert> tablespace. tmp # mailx-s "tablespace alert for DB" YOUR_EMAIL_ADDRESS t <tablespace. alert # fi