tablespace,datafile 생성 SQL (마이그레이션시 참고용)

SELECT CASE WHEN LAG(TABLESPACE_NAME) OVER (ORDER BY RN)= TABLESPACE_NAME
            THEN 'ALTER TABLESPACE '||TABLESPACE_NAME||' ADD DATAFILE '''||FILE_NAME||''' SIZE '||SIZEMB||'M '||
                 CASE WHEN LEAD(TABLESPACE_NAME) OVER (ORDER BY RN) = TABLESPACE_NAME OR TABLESPACE_NAME LIKE '%UNDO%'
                      THEN 'AUTOEXTEND OFF;'
                      ELSE 'AUTOEXTEND ON;'
                      END
            ELSE 'CREATE TABLESPACE '||TABLESPACE_NAME||' DATAFILE '''||FILE_NAME||''' SIZE '|| SIZEMB||'M '||
                 CASE WHEN LEAD(TABLESPACE_NAME) OVER (ORDER BY RN) = TABLESPACE_NAME OR TABLESPACE_NAME LIKE '%UNDO%'
                      THEN 'AUTOEXTEND OFF;'
                      ELSE 'AUTOEXTEND ON;'
                      END
            END
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY A.TABLESPACE_NAME,B.FILE_ID) RN,A.TABLESPACE_NAME,B.FILE_NAME,B.BYTES/1024/1024 SIZEMB
FROM DBA_TABLESPACES A,DBA_DATA_FILES B
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME
)

※ UNDO는 AUTOEXTEND OFF 설정

※ 템프파일은 따로 생성 및 크기 조절할것.