[OTN] LOB DATA 를 다른 TABLESPACE로 옮기는 방법

출처  :  http://forums.oracle.com/forums/thread.jspa?threadID=472032&tstart=180




LOB DATA 를 다른 TABLESPACE로 옮기는 방법
======================================================




PURPOSE
-------------------------------------------------------------------------------------------------

다음은 move command 를 이용하여 lob segment를 가진 table을 rebuild 하는 방법을 설명한다.




Explanation
-------------------------------------------------------------------------------------------------

일반적으로 table 을 rebuild 하는 경우 export/import를 이용하지만 ORACLE 8.1.x 이상에서는
move tablespace command 를 이용하여 rebuild를 할 수 있다.
그러나 move tablespace command 는 lob segment 및 lob index에 대해서는 적용이 되지 않는다.

sample )

SQL> CREATE TABLE lob_tab (col1 CLOB, col2 CLOB)
2 STORAGE (INITIAL 2m NEXT 2m)
3 LOB (col1) STORE AS
4 lob_tab_col1_lob (TABLESPACE users
5 STORAGE (INITIAL 2m NEXT 2m)
6 NOCACHE LOGGING)
7 LOB (col2) STORE AS
8 lob_tab_col2_lob (TABLESPACE users
9 STORAGE (INITIAL 2m NEXT 2m)
10 NOCACHE LOGGING)
11 tablespace users;

Table created.

SQL>
SQL>
SQL> select segment_name, segment_type, bytes , tablespace_name
2 from user_segments
3 where segment_name = 'LOB_TAB' or
4 segment_name in ( select segment_name from user_lobs where table_name='LOB_TAB') or
5 segment_name in ( select index_name from user_lobs where table_name='LOB_TAB')
6 /

SEGMENT_NAME SEGMENT_TYPE BYTES TABLESPACE_NAME
--------------------------------------------------------------------------------
LOB_TAB TABLE 2097152 USERS
SYS_IL0000003968C00001$$ LOBINDEX 2097152 USERS
SYS_IL0000003968C00002$$ LOBINDEX 2097152 USERS
LOB_TAB_COL1_LOB LOBSEGMENT 2097152 USERS
LOB_TAB_COL2_LOB LOBSEGMENT 2097152 USERS


SQL> alter table lob_tab move storage ( initial 2m next 2m ) tablespace ckchoi ;

Table altered.

SQL>
SQL> select segment_name, segment_type, bytes , tablespace_name
2 from user_segments
3 where segment_name = 'LOB_TAB' or
4 segment_name in ( select segment_name from user_lobs where table_name='LOB_TAB') or
5 segment_name in ( select index_name from user_lobs where table_name='LOB_TAB')
6 /

SEGMENT_NAME SEGMENT_TYPE BYTES TABLESPACE_NAME
--------------------------------------------------------------------------------
LOB_TAB TABLE 2129920 CKCHOI
SYS_IL0000003968C00001$$ LOBINDEX 2097152 USERS
SYS_IL0000003968C00002$$ LOBINDEX 2097152 USERS
LOB_TAB_COL1_LOB LOBSEGMENT 2097152 USERS
LOB_TAB_COL2_LOB LOBSEGMENT 2097152 USERS

SQL>



다음과 같이 table 에 대한 tablespace 만 변경이 된다. 따라서 lob segment 와 lob index 의
tablespace 를 동시에 변경하기 위해서는 move command 다음에 lob_segment 에 대한 설정이 필요하다.

다음 예제에서 확인할 수 있다.

SAMPLE)

SQL> alter table lob_tab move
2 lob(col1,col2) store as (tablespace ckchoi
3 storage ( initial 2m next 2m) chunk 16384 nocache )
4 storage ( initial 2m next 2m)
5 tablespace ckchoi ;

Table altered.


SQL> select segment_name, segment_type, bytes , tablespace_name
2 from user_segments
3 where segment_name = 'LOB_TAB' or
4 segment_name in ( select segment_name from user_lobs where table_name='LOB_TAB') or
5 segment_name in ( select index_name from user_lobs where table_name='LOB_TAB')
6 /

SEGMENT_NAME SEGMENT_TYPE BYTES TABLESPACE_NAME
--------------------------------------------------------------------------------
LOB_TAB TABLE 2129920 CKCHOI
SYS_IL0000004019C00001$$ LOBINDEX 2129920 CKCHOI
SYS_IL0000004019C00002$$ LOBINDEX 2129920 CKCHOI
LOB_TAB_COL1_LOB LOBSEGMENT 2129920 CKCHOI
LOB_TAB_COL2_LOB LOBSEGMENT 2129920 CKCHOI




Reference Documents
-------------------------------------------------------------------------------------------------

SQL Reference Manual
NOTE <130814.1>