#!/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 |