Linux如何让lst自动对齐,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 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 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

exit

EOF