Linux oracle 自动 扩,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 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

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 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