dba.sh 4번

[17기_윤영민]

#!/bin/bash
export ORACLE_BASE=/u01/app/oracle                                                      # 오라클의 루트 디렉토리
export ORACLE_SID=orcl2                                                                        # 오라클 인스턴스 이름 (db이름)
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1      # 오라클 홈
export PATH=$ORACLE_HOME/bin:$PATH                                                      # 오라클 홈 디렉토리가 어딘지 환경설정

 sqlplus scott/tiger <<EOF


begin
  for  a  in  (  select * from ((select   t.tablespace_name,
         ((t.total_size - f.free_size) / t.total_size) * 100 usedspace
   from (select tablespace_name, sum(bytes)/1024/1024 total_size
              from dba_data_files
               group by tablespace_name) t,
    (select tablespace_name, sum(bytes)/1024/1024 free_size
                   from dba_free_space
                  group by tablespace_name) f
           where t.tablespace_name = f.tablespace_name(+)))
    where   usedspace >= 70 )  loop

 execute immediate 'alter  tablespace ' || a.tablespace_name  ||
                   ' add datafile  size 100m' ;
end loop;
end;
/

EOF