linux自動增加表空間

[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 = '$tablespace');
       spool off
        exit
EOF

----總的指令碼
[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((aa.max-

aa.megs_used)/aa.max ,2)* 100<15;
       spool off
        exit
EOF
path="/home/oracle/backupscript/addtablespace"
## get count and path #####
--獲得表空間名稱
cat $path/checksb.lst|sed '1,3d'|cut -d ' ' -f 1|sed '/^$/d' > $path/tablespce.txt
sh  tabcount.sh

### deal file ####
path="/home/oracle/check/testtablespace"
---表空間
cat $path/checksb.lst|sed '1,3d'|cut -d ' ' -f 1|sed '/^$/d' > $path/tablespce.txt
----定義變數 ，取表空間檔案總數賦值給變數
declare -i num=$(cat checkck.lst|head -4|sed '1,3d'|sed 's/[ \t]*$//')
declare -i num2=$(($num+1))  ---替代的一個變數
------取檔案路徑  sed '/^$/d' 刪除空格行      取除了 檔名稱的路徑
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
---判斷   是否檔案數量到達10
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 #####
---建立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 ######  執行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

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