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;