[Oracle] Table과 Index의 Tablespace 옮기기

1. 옮길 Tablespace의 용량 확인

• tablespace 여유공간이 부족하면 진행하다 에러 발생

select sum(bytes)/1024/1024/1024 
  from dba_segments 
 where tablespace_name = '테이블스페이스명';

 

• 만약 tablespace가 없다면 만들고, 용량 부족하면 늘리기

-- tablespace 신규 생성
create tablespace 테이블스페이스명 datafile '/경로/테이블스페이스파일명.dbf'
       size 초기용량(100m,1g 등) autoextend [on/off] maxsize 용량;

-- tablespace 용량 추가
alter database datafile '특정파일' autoextend [on/off] maxsize 용량;

 

 

---

 

2. Table 옮기기

• 우선 파티션이 있는지, 압축이 되었는지 확인해야함.

 

  2-1. 파티션X + 압축X

SELECT 'ALTER TABLE ' || OWNER ||'.'|| TABLE_NAME || ' MOVE TABLESPACE 테이블스페이스명 NOLOGGING PARALLEL 16;'
  FROM dba_tables
 WHERE compression <> 'ENABLED'
   AND partitioned = 'NO';

 

  2-2. 파티션O + 압축X

 

-- PARTITION 있는 경우
SELECT 'ALTER TABLE ' || OWNER ||'.'|| TABLE_NAME || ' MOVE PARTITION ' || PARTITION_NAME || ' TABLESPACE 테이블스페이스명 NOLOGGING PARALLEL 16;'
  FROM dba_tab_partitions 
 WHERE SUBPARTITION_COUNT = 0;

 -- SUBPARTITION 있는 경우
SELECT 'ALTER TABLE ' || OWNER ||'.'|| TABLE_NAME || ' MOVE SUBPARTITION ' || PARTITION_NAME || ' TABLESPACE 테이블스페이스명 NOLOGGING PARALLEL 16;'
  FROM dba_tab_subpartitions;

 

 

  2-3. 파티션O + 압축O

-- PARTITION 있는 경우
SELECT 'ALTER TABLE ' || OWNER ||'.'|| TABLE_NAME || ' MOVE PARTITION ' || PARTITION_NAME || ' COMPRESS FOR ' || COMPRESS_FOR ||' TABLESPACE 테이블스페이스명 NOLOGGING PARALLEL 16;'
  FROM dba_tab_partitions a
 WHERE compression = 'ENABLED'
   AND table_name in (select table_name from dba_part_tables c 
                      where a.owner=c.owner and a.table_name=c.table_name and c.subpartitioning_type='NONE');
   
 -- SUBPARTITION 있는 경우
SELECT 'ALTER TABLE ' || OWNER ||'.'|| TABLE_NAME || ' MOVE SUBPARTITION ' || PARTITION_NAME || ' COMPRESS FOR ' || COMPRESS_FOR ||' TABLESPACE 테이블스페이스명 NOLOGGING PARALLEL 16;'
  FROM dba_tab_subpartitions;

 

 

---

 

3. Lobs 옮기기

  3-1. 파티션X

SELECT 'ALTER TABLE ' || OWNER ||'.'|| TABLE_NAME || ' MOVE TABLESPACE ' || TABLESPACE_NAME || ' LOB(' || COLUMN_NAME || ') STORE AS (TABLESPACE 옮길테이블스페이스명) PARALLEL 16;'
  FROM dba_lobs
 WHERE compression <> 'ENABLED'
   AND partitioned = 'NO';

 

  3-2. 파티션O

-- PARTITION 있는 경우
SELECT 'ALTER TABLE ' || OWNER ||'.'|| TABLE_NAME || ' MOVE PARTITION ' || PARTITION_NAME || ' TABLESPACE || TABLESPACE_NAME || LOB(' || COLUMN_NAME || ') STORE AS (TABLESPACE 옮길테이블스페이스명) PARALLEL 16;'
  FROM dba_lob_partitions
 WHERE SUBPARTITION_COUNT = 0;

 -- SUBPARTITION 있는 경우
SELECT 'ALTER TABLE ' || OWNER ||'.'|| TABLE_NAME || ' MOVE SUBPARTITION ' || SUBPARTITION_NAME || ' TABLESPACE || TABLESPACE_NAME || LOB(' || COLUMN_NAME || ') STORE AS (TABLESPACE 옮길테이블스페이스명) PARALLEL 16;'
  FROM dba_lob_subpartitions;

 

 

---

 

4. Index 옮기기

 4-1. 파티션X

SELECT 'ALTER INDEX ' || INDEX_OWNER ||'.'|| INDEX_NAME || ' REBUILD TABLESPACE 옮길테이블스페이스명 NOLOGGING PARALLEL 16;'
  FROM dba_indexes
 WHERE partitioned = 'NO'
   AND index_name not like 'SYS_IL%';

 

  4-2. 파티션O

-- PARTITION 있는 경우
SELECT 'ALTER INDEX ' || A.INDEX_OWNER ||'.'|| A.INDEX_NAME || ' REBUILD PARTITION ' || A.PARTITION_NAME || ' TABLESPACE 옮길테이블스페이스명 NOLOGGING PARALLEL 16;'
  FROM dba_ind_partition A, dba_indexes C 
 WHERE A.index_name = C.index_name
   AND A.index_owner = C.owner
   AND A.subpartition_count = 0 
   AND A.index_name NOT LIKE 'SYS_IL%';


 -- SUBPARTITION 있는 경우
SELECT 'ALTER INDEX ' || A.INDEX_OWNER ||'.'|| A.INDEX_NAME || ' REBUILD SUBPARTITION ' || A.SUBPARTITION_NAME || ' TABLESPACE 옮길테이블스페이스명 NOLOGGING PARALLEL 16;'
  FROM dba_ind_subpartition A, dba_indexes C 
 WHERE A.index_name = C.index_name
   AND A.index_owner = C.owner
   AND A.index_name NOT LIKE 'SYS_IL%';

 

 

---

 

5. NOLOGGING+PARALLEL을 LOGGING+NOPARALLEL로 바꾸기

  5-1. Table

SELECT 'ALTER TABLE ' || OWNER ||'.'|| TABLE_NAME || ' LOGGING NOPARALLEL;'
  FROM dba_tables;

 

  5-2. Lobs

SELECT 'ALTER TABLE ' || OWNER ||'.'|| TABLE_NAME || ' MODIFY LOB(' || COLUMN_NAME || ') (NOCACHE LOGGING) NOPARALLEL;'
  FROM dba_lobs;

 

  5-3. Index

SELECT 'ALTER INDEX ' || INDEX_OWNER ||'.'|| TABLE_NAME || ' LOGGING NOPARALLEL;'
  FROM dba_indexes;

 

 

---

 

6. Default Tablespace 확인

  6-1. Table 

-- PARTITION 없는 경우
SELECT 'ALTER TABLE ' || OWNER ||'.'|| TABLE_NAME || ' MODIFY DEFUALT ATTRIBUTES FOR PARTITION ' || PARTITION_NAME || ' TABLESPACE 옮길테이블스페이스명;'
  FROM DBA_PART_TABLES;
  
  
-- PARTITION 있는 경우(하위 세그먼트가 있는 경우만 진행 -> SUBPARTITION은 하위 세그먼트 없어서 안함 )
SELECT 'ALTER TABLE ' || OWNER ||'.'|| TABLE_NAME || ' MODIFY DEFUALT ATTRIBUTES TABLESPACE 옮길테이블스페이스명;'
  FROM DBA_TAB_PARTITIONS
 WHERE A.SUBPARTITION_COUNT = 0;
 
 
 -- SUBPARTITION TEMPLATE 있음

 

  6-2 Lobs

-- PARTITION 없는 경우
SELECT 'ALTER TABLE ' || OWNER ||'.'|| TABLE_NAME || ' MODIFY DEFUALT ATTRIBUTES LOB(' || COLUMN_NAME || ') ( TABLESPACE 옮길테이블스페이스명);'
  FROM DBA_LOBS;


-- PARTITION 있는 경우
SELECT 'ALTER TABLE ' || OWNER ||'.'|| TABLE_NAME || ' MODIFY DEFUALT ATTRIBUTES FOR PARTITION ' || PARTITION_NAME || ' LOB(' || COLUMN_NAME || ') ( TABLESPACE 옮길테이블스페이스명);'
  FROM DBA_LOB_PARTITIONS;

 

 

  6-3. Index

-- PARTITION 없는 경우
SELECT 'ALTER INDEX ' || INDEX_OWNER ||'.'|| INDEX_NAME || ' MODIFY DEFUALT ATTRIBUTES TABLESPACE 옮길테이블스페이스명;'
  FROM DBA_PART_INDEXES;


-- PARTITION 있는 경우
SELECT 'ALTER INDEX ' || INDEX_OWNER ||'.'|| INDEX_NAME || ' MODIFY DEFUALT ATTRIBUTES FOR PARTITION ' || PARTITION_NAME || ' TABLESPACE 옮길테이블스페이스명;'
  FROM DBA_IND_PARTITIONS;

 

 

---

 

7. Quota 확인

SELECT 'GRANT UNLIMITED TABLESPACE TO ' || USERNAME || ';' || CHR(10) ||
       'ALTER USER ' || USERNAME || ' QUOTA 0 ON 새로옮긴테이블스페이스명;'
  FROM DBA_TS_QUOTAS;