Oracle Tablespace 재생성 Script (이관 작업용)

1. datafile 경로 및 크기 지정

아래 WITH 절 v 의 경우, dir은 datafile 경로, umb는 최대 단위 datafile 크기를 기재하면 됩니다.

smallfile 인 경우, datafile의 크기는 32GB까지이나, 8KB 블럭은 실제 (131072x-1)*8192 byte만 할당 가능합니다.

이를 byte로 표기하기에는 숫자가 너무 커지므로 간단히 32767(32GB*1024-1)MB로 하였습니다.

2. datafile 은 01, 02와 같은 형태의 순번을 붙였습니다.

3. SYSTEM, SYSAUX, UNDOTBS1, USERS 는 제외하였습니다.

4. Autoextend 값은 10GB 이상 시 1GB, 1GB 이상 시 100MB, 그 아래듣 10MB 로 지정하였습니다.

WITH v AS (
    SELECT '/oradata/' dir, 32767 umb FROM DUAL
), t AS ( 
    SELECT  LOWER(tablespace_name) tsn,
            CEIL(SUM(bytes)/1024/1024) mb
    FROM    dba_data_files
    WHERE   tablespace_name NOT IN ('SYSTEM','SYSAUX','UNDOTBS1','USERS')
    GROUP BY tablespace_name
), c AS (
    SELECT (LEVEL-1)*v.umb lvl FROM DUAL, v CONNECT BY LEVEL <= (SELECT CEIL(MAX(t.mb)/v.umb) FROM t, v)
)
SELECT  CASE WHEN c.lvl=0 THEN 'CREATE TABLESPACE ' || t.tsn || ' DATAFILE ''' ELSE '    ' END ||
            v.dir || LOWER(t.tsn) || TO_CHAR(c.lvl/v.umb+1, 'FM09') || '.dbf'' SIZE ' || CASE WHEN t.mb-c.lvl>=v.umb THEN v.umb ELSE t.mb-c.lvl END || 'M AUTOEXTEND ON NEXT ' ||
            CASE WHEN t.mb >= 10240 THEN '1G' WHEN t.mb >= 1024 THEN '100M' ELSE '10M' END || ' MAXSIZE UNLIMITED' ||
            CASE WHEN lvl+(CASE WHEN t.mb-c.lvl>=v.umb THEN v.umb ELSE t.mb-c.lvl END)=t.mb THEN ';' ELSE ',' END sql
FROM t, c, v
WHERE t.mb > c.lvl
ORDER BY t.tsn, c.lvl;