LOB 테이블 다른 Tablespace 로 이동

---

1. 이동할 Tablespace와  여유공간을 체크한다.

- LOB 테이블과 LOB 컬럼의 size 확인

select max(owner)owner ,segment_name table_name ,
       max(bytes)/1024/1024||' MB' table_size,
       sum(nvl(lbytes,0))/1024/1024||' MB' lob_size,
       (max(bytes)+sum(nvl(lbytes,0)))/1024/1024||' MB' total_size
from (
select a.owner,a.segment_name,a.bytes,c.bytes lbytes
 from dba_segments a , dba_lobs b , dba_segments c
where a.owner = 'SCOTT'
  and a.segment_type = 'TABLE'
  and a.segment_name = b.table_name(+)
  and b.segment_name = c.segment_name(+)
)
group by    segment_name
order by 2

- 테이블스페이스 사이즈 확인

select tablespace_name,sum(bytes)/power(2,20) MB from dba_Data_files group by tablespace_name;

- 테이블스페이스 여유공간 확인

select tablespace_name,sum(bytes)/power(2,20) MB from dba_free_space group by tablespace_name;

---

2. LOB Table 확인

select * from user_lobs;

---

3. LOB Table Index 조회

select b.*
from (select distinct  table_name from user_lobs) a,user_indexes b
where a.table_name =b.table_name
and index_type not in ('LOB');

---

4. LOB Table 이동

select 'ALTER TABLE '||table_name||' MOVE LOB('||column_name||') STORE AS (TABLESPACE LOBSPACE) TABLESPACE TEST;'
from user_lobs;

---

5.LOB 테이블 인덱스 rebuild

select 'alter index '||  b.index_name ||' rebuild;'
from (select distinct  table_name from user_lobs) a,user_indexes b
where a.table_name =b.table_name
and index_type not in ('LOB');

---