Oracle批量创建表空间数据文件

Oracle批量建表空间数据文件脚本

一、单机批量创建数据文件

#!/bin/bash




datetime=$(date +%Y-%m-%d_%H%M)

export ORACLE_SID=aidb
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export PATH=/u01/app/oracle/product/11.2.0/db_1/bin:$PATH

export schema=hie
export data_path=/data/oracle/aidb
export dbfile_num=25
export tablespace=${schema}_data 
export temp_tablespace=${schema}_hie_temp 


sqlplus / as sysdba <<EOF


create tablespace $tablespace datafile '$data_path/${tablespace}01.dbf' size 10g autoextend on next 100M;
create temporary tablespace $temp_tablespace tempfile '$data_path/${temp_tablespace}01.dbf' size 10g autoextend on next 100M;

set serveroutput on
declare
    num_str varchar(1000);
begin
  for i in 2 .. $dbfile_num loop
      num_str := CASE  WHEN i < 10 THEN '0' || TO_CHAR(i)  ELSE TO_CHAR(i) END;
      DBMS_OUTPUT.PUT_LINE(num_str);
      execute immediate 'alter tablespace $tablespace add datafile ''$data_path/${tablespace}'||num_str||'.dbf'' size 10g autoextend on next 100M';
  end loop;
end;
/

exit;
EOF

二、RAC批量建数据文件

#!/bin/bash




datetime=$(date +%Y-%m-%d_%H%M)

export ORACLE_SID=gzdb1
export ORACLE_HOME=/home/app/oracle/product/11.2.0/db_1
export PATH=/home/app/oracle/product/11.2.0/db_1/bin:$PATH

export schema=idc_user
export data_path='+DATA'
export dbfile_num=60
export tablespace=${schema}_data 
export temp_tablespace=${schema}_temp 


sqlplus / as sysdba <<EOF


create tablespace $tablespace datafile '$data_path' size 10g autoextend on next 100M;
create temporary tablespace $temp_tablespace tempfile '$data_path' size 10g autoextend on next 100M;

set serveroutput on
declare
    num_str varchar(1000);
begin
  for i in 2 .. $dbfile_num loop
      execute immediate 'alter tablespace $tablespace add datafile ''$data_path'' size 10g autoextend on next 100M';
  end loop;
end;
/

exit;
EOF