Linux 自動增加oracle 表空間

[oracle@zdtdbatest testtablespace]$ cat  /home/oracle/check/testtablespace/scrip.sh

. $HOME/.bash_profile

export ORACLE_SID=zdtdbat1
if [ $ORACLE_SID != 'zdtdbat1' ];
then
        exit
fi
/u01/app/oracle/product/10.2.0/db_1/bin/sqlplus -s "/as sysdba" <        set echo on
        set head on
        spool /home/oracle/check/testtablespace/checksb
        set linesize 120 pagesize 10000
        col name format a50
select aa.tablespace_name a1, aa.megs_alloc a2,aa.megs_free a3, aa.megs_used a4, aa.pct_free a5, aa.pct_used a6,aa.max a7, round(Pct_used/
Max*100,2) a3 from (select a.tablespace_name,round(a.bytes_alloc/ 1024 / 1024, 2) megs_alloc, round(nvl(b.bytes_free, 0) / 1024 / 1024
, 2) megs_free,round((a.bytes_alloc - nvl(b.bytes_free, 0))/ 1024/ 1024, 2) megs_used,round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100, 2)
 Pct_Free,100 - round((nvl(b.bytes_free, 0)/ a.bytes_alloc) * 100, 2) Pct_used,round(maxbytes / 1048576, 2) Max from (select f.tablespace_na
me, sum(f.bytes) bytes_alloc,sum(decode(f.autoextensible,'YES',f.maxbytes,'NO',f.bytes)) maxbytes from dba_data_files f group by tablespace_
name) a,(select f.tablespace_name, sum(f.bytes) bytes_free from dba_free_space f group by tablespace_name) b where a.tablespace_name = b.tab
lespace_name(+) and a.tablespace_name not in ('SYSTEM', 'SYSAUX', 'EXAMPLE', 'USERS', 'UNDOTBS1', 'TEMP')  union all select h.tablespace_nam
e,round(sum(h.bytes_free + h.bytes_used) / 1048576, 2) megs_alloc,round(sum((h.bytes_free + h.bytes_used) -nvl(p.
bytes_used, 0)) / 1048576,2) megs_free, round(sum(nvl(p.bytes_used, 0))/ 1048576, 2) megs_used,round((sum((h.bytes_free + h.bytes_used) -
nvl(p.bytes_used, 0))/ sum(h.bytes_used + h.bytes_free)) * 100, 2) Pct_Free, 100 - round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_us
ed, 0)) / sum(h.bytes_used + h.bytes_free)) * 100, 2) pct_used, round(f.maxbytes / 1048576, 2) max  from sys.v_\$TEMP_SPACE_HEADER h,
sys.v_\$Temp_extent_pool  p, dba_temp_files  f where p.file_id(+) = h.file_id and p.tablespace_name(+) = h.tablespace_name  and f.file_id =
 h.file_id and f.tablespace_name = h.tablespace_name and h.tablespace_name not in ('SYSTEM', 'SYSAUX', 'EXAMPLE', 'USERS', 'UNDOTBS
1', 'TEMP')  group by h.tablespace_name, f.maxbytes ORDER BY 1)   aa where round(Pct_used /Max *100)< 70;
       spool off
        exit
EOF
## get count and path #####
sh  tabcount.sh

### deal file ####
path="/home/oracle/check/testtablespace"
declare -i num=$(cat checkck.lst|head -4|sed '1,3d'|sed 's/[ \t]*$//')
declare -i num2=$(($num+1))
cat $path/checkck.lst|sed '1,8d'|sed '/^$/d'|cut -d '/' -f 1,2,3,4 > $path/path1.txt
cat $path/checkck.lst|sed '1,8d'|sed '/^$/d'|cut -c 22-|sed 's/[ \t]*$//' > $path/oldfile1.txt
if ["$num2" -eq "10"]; then

 cat $path/oldfile1.txt|tr "tbs_aishu09.dbf" "tbs_aishu10.dbf"> $path/newfile1.txt
else

 cat $path/oldfile1.txt|tr "$num" "$num2"> $path/newfile1.txt

fi
paste -d '/'  $path/path1.txt $path/newfile1.txt|sed "s/^/\'/g"|sed "s/$/\'/g" > $path/pa_file2.txt

### create sql #####
 paste -d ' ' $path/sqlfile.txt $path/tablespce.txt > $path/sqlfile2.txt
 paste -d ' ' $path/sqlfile2.txt $path/datafile.txt  > $path/sqlfile3.txt
 paste -d ' ' $path/sqlfile3.txt $path/pa_file2.txt  > $path/sqlfile4.txt
 paste -d ' ' $path/sqlfile4.txt $path/sqlhou.txt    > $path/scprit.sql
#### run sql ######
export ORACLE_SID=zdtdbat1
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
$ORACLE_HOME/bin/sqlplus /  as sysdba <@$path/scprit.sql
exit
EOF

[oracle@zdtdbatest testtablespace]$ ls
checkck.lst  datafile.txt  oldfile1.txt  path1.txt  scprit.sql  scrip.shold   sqlfile2.txt  sqlfile4.txt  sqlhou.txt  tabcount.sh
checksb.lst  newfile1.txt  pa_file2.txt  s1         scrip.sh    scriptest.sh  sqlfile3.txt  sqlfile.txt   sql.txt     tablespce.txt
[oracle@zdtdbatest testtablespace]$ cat  tabcount.sh

#!/bin/bash
. $HOME/.bash_profile

export ORACLE_SID=zdtdbat1
if [ $ORACLE_SID != 'zdtdbat1' ];
then
        exit
fi
tablespace=$(cat /home/oracle/check/testtablespace/tablespce.txt)

/u01/app/oracle/product/10.2.0/db_1/bin/sqlplus -s "/as sysdba" <        set echo on
        set head on
        spool /home/oracle/check/testtablespace/checkck
        set linesize 120 pagesize 10000
        col name format a50
select count (distinct a.file_name) from dba_data_files a where a.tablespace_name='$tablespace';
select trim(file_name) file_name  from dba_data_files a where a.tablespace_name='$tablespace' and file_id = (select max(file_id) from dba_da
ta_files a where a.tablespace_name = 'TBS_AISHU');
       spool off
        exit
EOF

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15187685/viewspace-733099/，如需轉載，請註明出處，否則將追究法律責任。