오라클 사용자 tablespace 변경

단순하게 물리적인 Tablespace이동이라면 파일 이동하시면 됩니다.

Step1)  Tablespace OffLine

-> ALTER TABLESPACE tablespace명 OFFLINE;

Step2) file copy

-> cp oldfile newfile

Step3) Target Tablespace 수정

-> ALTER DATABASE tablespace명 rename file 'oldFile' to 'newFile';

Step4) Tablespace OnLine

-> ALTER TABLESPACE tablespace명 ONLINE;

단, 시스템 TableSpace이라면 shutdown 하셔야합니다.

 

그런데 그게아니라 특정 테이블의 TableSpace이동이라면

수동으로 Target Table과 Constraint 등을 변경하고 인덱스 또한 Rebuild하여야 합니다.

예는 제가 간단히 스크립트 만들어 보았습니다.

활용하시면 될듯

/* 1.테이블별 Constraints(PrimaryKey, Unique) 삭제 Script */
SELECT 'ALTER TABLE '||table_name||' DROP CONSTRAINT '||index_name||';' stmt
FROM  USER_INDEXES
WHERE table_name = '테이블명'
AND   index_name like '%PK'
;

/* 2.테이블 이동 Script */
SELECT 'ALTER TABLE '||table_name||' MOVE TABLESPACE 테이블스테이스명;' stmt
FROM  USER_INDEXES
WHERE table_name = '테이블명'
;

/* 3.INDEX이동(Rebuild) Script */
SELECT 'ALTER INDEX '||index_name||' REBUILD TABLESPACE 테이블스테이스명;' stmt
FROM  USER_INDEXES
WHERE table_name = '테이블명'
;

/* 4.Index - Primary Key설정 Script */
SELECT 'ALTER TABLE '||a.table_name||
       ' ADD CONSTRAINT "'||a.index_name||'" PRIMARY KEY ("'||col_list||'")'||
       ' USING INDEX PCTFREE 10 INITRANS 1 MAXTRANS 255 COMPUTE STATISTICS'||
       ' STORAGE(INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)'||
       ' TABLESPACE 테이블스테이스명 ENABLE;' stmt
FROM  (
        SELECT a.table_name
              ,index_name
              ,max(idx_col1 )||decode(max(idx_col2 ),'','',',')||
               max(idx_col2 )||decode(max(idx_col3 ),'','',',')||
               max(idx_col3 )||decode(max(idx_col4 ),'','',',')||
               max(idx_col4 )||decode(max(idx_col5 ),'','',',')||
               max(idx_col5 )||decode(max(idx_col6 ),'','',',')||
               max(idx_col6 )||decode(max(idx_col7 ),'','',',')||
               max(idx_col7 )||decode(max(idx_col8 ),'','',',')||
               max(idx_col8 )||decode(max(idx_col9 ),'','',',')||
               max(idx_col9 )||decode(max(idx_col10),'','',',')||
               max(idx_col10)||decode(max(idx_col11),'','',',')||
               max(idx_col11)||decode(max(idx_col12),'','',',')||
               max(idx_col12)||decode(max(idx_col13),'','',',')||
               max(idx_col13)||decode(max(idx_col14),'','',',')||
               max(idx_col14)||decode(max(idx_col15),'','',',')||
               max(idx_col15) col_list
        FROM  (
                SELECT a.table_name
                      ,b.column_name
                      ,a.index_name
                      ,b.column_position
                      ,decode(b.column_position,1 ,b.column_name) idx_col1
                      ,decode(b.column_position,2 ,b.column_name) idx_col2
                      ,decode(b.column_position,3 ,b.column_name) idx_col3
                      ,decode(b.column_position,4 ,b.column_name) idx_col4
                      ,decode(b.column_position,5 ,b.column_name) idx_col5
                      ,decode(b.column_position,6 ,b.column_name) idx_col6
                      ,decode(b.column_position,7 ,b.column_name) idx_col7
                      ,decode(b.column_position,8 ,b.column_name) idx_col8
                      ,decode(b.column_position,9 ,b.column_name) idx_col9
                      ,decode(b.column_position,10,b.column_name) idx_col10
                      ,decode(b.column_position,11,b.column_name) idx_col11
                      ,decode(b.column_position,12,b.column_name) idx_col12
                      ,decode(b.column_position,13,b.column_name) idx_col13
                      ,decode(b.column_position,14,b.column_name) idx_col14
                      ,decode(b.column_position,15,b.column_name) idx_col15
                FROM  USER_INDEXES     A
                     ,USER_IND_COLUMNS B
                WHERE b.index_name = a.index_name
                AND   a.table_name = '테이블명'
                AND   a.index_name like '%PK'
              ) a
        GROUP BY a.table_name,a.index_name
      ) a
;

/* 5.Index - Unique 생성 Script */
SELECT 'CREATE UNIQUE INDEX '||a.index_name||
       ' ON '||a.table_name||'('||col_list||')'||
       ' PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS'||
       ' STORAGE(INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)'||
       ' TABLESPACE 테이블스테이스명;'
FROM  (
        SELECT a.table_name
              ,index_name
              ,max(idx_col1 )||decode(max(idx_col2 ),'','',',')||
               max(idx_col2 )||decode(max(idx_col3 ),'','',',')||
               max(idx_col3 )||decode(max(idx_col4 ),'','',',')||
               max(idx_col4 )||decode(max(idx_col5 ),'','',',')||
               max(idx_col5 )||decode(max(idx_col6 ),'','',',')||
               max(idx_col6 )||decode(max(idx_col7 ),'','',',')||
               max(idx_col7 )||decode(max(idx_col8 ),'','',',')||
               max(idx_col8 )||decode(max(idx_col9 ),'','',',')||
               max(idx_col9 )||decode(max(idx_col10),'','',',')||
               max(idx_col10)||decode(max(idx_col11),'','',',')||
               max(idx_col11)||decode(max(idx_col12),'','',',')||
               max(idx_col12)||decode(max(idx_col13),'','',',')||
               max(idx_col13)||decode(max(idx_col14),'','',',')||
               max(idx_col14)||decode(max(idx_col15),'','',',')||
               max(idx_col15) col_list
        FROM  (
                SELECT a.table_name
                      ,b.column_name
                      ,a.index_name
                      ,b.column_position
                      ,decode(b.column_position,1 ,b.column_name) idx_col1
                      ,decode(b.column_position,2 ,b.column_name) idx_col2
                      ,decode(b.column_position,3 ,b.column_name) idx_col3
                      ,decode(b.column_position,4 ,b.column_name) idx_col4
                      ,decode(b.column_position,5 ,b.column_name) idx_col5
                      ,decode(b.column_position,6 ,b.column_name) idx_col6
                      ,decode(b.column_position,7 ,b.column_name) idx_col7
                      ,decode(b.column_position,8 ,b.column_name) idx_col8
                      ,decode(b.column_position,9 ,b.column_name) idx_col9
                      ,decode(b.column_position,10,b.column_name) idx_col10
                      ,decode(b.column_position,11,b.column_name) idx_col11
                      ,decode(b.column_position,12,b.column_name) idx_col12
                      ,decode(b.column_position,13,b.column_name) idx_col13
                      ,decode(b.column_position,14,b.column_name) idx_col14
                      ,decode(b.column_position,15,b.column_name) idx_col15
                FROM  USER_INDEXES     A
                     ,USER_IND_COLUMNS B
                WHERE b.index_name = a.index_name
                AND   a.table_name = '테이블명'
                AND   a.index_name like '%PK'
              ) a
        GROUP BY a.table_name,a.index_name
      ) a
;

테이블 목록 : select TABLE_NAME FROM USER_TAB_COMMENTS

출처 : http://kin.naver.com/qna/detail.nhn?d1id=1&dirId=10205&docId=109867350&qb=VEFCTEVTUEFDReuzgOqyvQ==&enc=utf8&section=kin&rank=1&search_sort=0&spq=0&pid=gOzfAg331ylssuS3eZVssv--494528&sid=TdzSiTLG3E0AACV3LU0