TABLESPACE삭제 후 재생성시 이용할 TABLESPACE생성 script
==========================================

Purpose
---------------

경우에 따라 tablespace를 drop 후 재생성할 필요가 있다.
이때 기존 tablespace에 대한 storage및 크기 관련 parameter를 기억해 두었다가
다시 생성시 이용하는데 미리 dictionary 정보로 부터 그러한 option들을
참고하여 생성 script를 만들어 둔 후 다시 생성하면 유용할 수 있다.

삭제 전에 아래 자료를 이용하여 생성 script를 받아 둔 후
삭제하고 다시 생성시 이 script를 이용하면 된다.

Explanation
--------------------------

DBA권한이 있는 db user상에서 다음 script를 수행한다.
이 script를 수행하면 create_tablespaces.sql 이라는 화일이 sqlplus login한
directory에 생성되므로
이후 tablespace를 재생성시 이용하면 된다.

Script:


SET ECHO off
REM NAME: TFSCSTBS.SQL
REM USAGE:"@path/tfscstbs"
REM ------------------------------------------------------------------------
REM REQUIREMENTS:
REM DBA privs
REM ------------------------------------------------------------------------

REM PURPOSE:
REM Running this script will in turn create a script to build
REM all the tablespaces in the database. This created script,
REM tfscstbs.sql, can be run by any user with the DBA role or
REM with the 'CREATE TABLESPACE' system privilege.
REM --------------------------------------------------------------------
REM ------------------------------------------------------------------------
REM Main text of script follows:


set verify off;
set termout off;
set feedback off;
set pagesize 0;

set termout on;
select 'Creating tablespace build script...' from dual;
set termout off;

create table ts_temp (lineno number, ts_name varchar2(30),
text varchar2(800));

DECLARE
CURSOR ts_cursor IS select tablespace_name,
initial_extent,
next_extent,
min_extents,
max_extents,
pct_increase,
status
from sys.dba_tablespaces
where tablespace_name != 'SYSTEM'
and status != 'INVALID'
order by tablespace_name;
CURSOR df_cursor (c_ts VARCHAR2) IS select file_name,
bytes
from sys.dba_data_files
where tablespace_name = c_ts
and tablespace_name != 'SYSTEM'
order by file_name;
lv_tablespace_name sys.dba_tablespaces.tablespace_name%TYPE;
lv_initial_extent sys.dba_tablespaces.initial_extent%TYPE;
lv_next_extent sys.dba_tablespaces.next_extent%TYPE;
lv_min_extents sys.dba_tablespaces.min_extents%TYPE;
lv_max_extents sys.dba_tablespaces.max_extents%TYPE;
lv_pct_increase sys.dba_tablespaces.pct_increase%TYPE;
lv_status sys.dba_tablespaces.status%TYPE;
lv_file_name sys.dba_data_files.file_name%TYPE;
lv_bytes sys.dba_data_files.bytes%TYPE;
lv_first_rec BOOLEAN;
lv_string VARCHAR2(800);
lv_lineno number := 0;

procedure write_out(p_line INTEGER, p_name VARCHAR2,
p_string VARCHAR2) is
begin
insert into ts_temp (lineno, ts_name, text) values
(p_line, p_name, p_string);
end;

BEGIN
OPEN ts_cursor;
LOOP
FETCH ts_cursor INTO lv_tablespace_name,
lv_initial_extent,
lv_next_extent,
lv_min_extents,
lv_max_extents,
lv_pct_increase,
lv_status;
EXIT WHEN ts_cursor%NOTFOUND;
lv_lineno := 1;
lv_string := ('CREATE TABLESPACE '||lower(lv_tablespace_name));
lv_first_rec := TRUE;
write_out(lv_lineno, lv_tablespace_name, lv_string);
OPEN df_cursor(lv_tablespace_name);
LOOP
FETCH df_cursor INTO lv_file_name,
lv_bytes;
EXIT WHEN df_cursor%NOTFOUND;
if (lv_first_rec) then
lv_first_rec := FALSE;
lv_string := 'DATAFILE ';
else
lv_string := lv_string || ',';
end if;
lv_string:=lv_string||''''||lv_file_name||''''||
' SIZE '||to_char(lv_bytes) || ' REUSE';
END LOOP;
CLOSE df_cursor;
lv_lineno := lv_lineno + 1;
write_out(lv_lineno, lv_tablespace_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := (' DEFAULT STORAGE (INITIAL ' ||
to_char(lv_initial_extent) ||
' NEXT ' || lv_next_extent);
write_out(lv_lineno, lv_tablespace_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := (' MINEXTENTS ' ||
lv_min_extents ||
' MAXEXTENTS ' || lv_max_extents);
write_out(lv_lineno, lv_tablespace_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := (' PCTINCREASE ' ||
lv_pct_increase || ')');
write_out(lv_lineno, lv_tablespace_name, lv_string);
lv_string := (' '||lv_status);
write_out(lv_lineno, lv_tablespace_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string:='/';
write_out(lv_lineno, lv_tablespace_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string:=' ';
write_out(lv_lineno, lv_tablespace_name, lv_string);
END LOOP;
CLOSE ts_cursor;
END;
/

spool create_tablespaces.sql
set heading off
set recsep off
col text format a80 word_wrap


select text
from ts_temp
order by ts_name, lineno;

spool off;

drop table ts_temp;




Examples
---------

create_tablespaces.sql 에 포함될 내용의 예는 다음과 같다.

CREATE TABLESPACE rbs
DATAFILE '/u02/oracle/V7.1.6/dbs/rbs2V716.dbf' SIZE 52428800
REUSE,'/u02/oracle/V7.1.6/dbs/rbsV716.dbf' SIZE 8388608 REUSE
DEFAULT STORAGE (INITIAL 131072 NEXT 131072
MINEXTENTS 2 MAXEXTENTS 121
PCTINCREASE 0)
ONLINE
/

CREATE TABLESPACE temp
DATAFILE '/u02/oracle/V7.1.6/dbs/tempV716.dbf' SIZE 563200 REUSE
DEFAULT STORAGE (INITIAL 131072 NEXT 131072
MINEXTENTS 1 MAXEXTENTS 121
PCTINCREASE 0)
ONLINE
/

CREATE TABLESPACE users
DATAFILE '/u02/oracle/V7.1.6/dbs/usrV716.dbf' SIZE 1048576 REUSE
DEFAULT STORAGE (INITIAL 10240 NEXT 10240
MINEXTENTS 1 MAXEXTENTS 121
PCTINCREASE 50)
ONLINE
/


reference
--------------
Script to Generate CREATE TABLESPACE