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에 대해서는
적용이 되지 않는다.

샘플 테이블 생성
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 ;

col1 라는 clob 에 대한 저장소로 Users 를 지정,

col2 라는 clob 에 대한 저장소로 Users 를 지정

나머지 Table 은 User 로 저장

생성결과 조회
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

 

 

테이블을 Move [ 순수 테이블만 ]

alter table lob_tab move tablespace tools ;

 

Move 결과 조회

select segment_name, segment_type, bytes , tablespace_name 
from user_segments
where segment_name = 'LOB_TAB' or
segment_name in ( select segment_name from user_lobs

where table_name='LOB_TAB') or 
segment_name in ( select index_name from user_lobs

where table_name='LOB_TAB')  ;

 

SEGMENT_NAME                 SEGMENT_TYPE    SIZE      TABLESPACE
SYS_IL0000067809C00001$$    LOBINDEX             131072      USERS
SYS_IL0000067809C00002$$    LOBINDEX             131072      USERS
LOB_TAB_COL2_LOB            LOBSEGMENT        2097152    USERS
LOB_TAB_COL1_LOB            LOBSEGMENT        2097152    USERS
LOB_TAB                             TABLE                   2097152    TOOLS

 

LOB Type column 의 Move

     alter table lob_tab move
     lob(col1,col2) store as (tablespace tools)
     tablespace tools ;

LOB Type column 의 Move 결과 확인

      select segment_name, segment_type, bytes , tablespace_name
      from user_segments
      where segment_name = 'LOB_TAB' or
      segment_name in ( select segment_name from user_lobs

      where table_name='LOB_TAB') or
      segment_name in ( select index_name from user_lobs

      where table_name='LOB_TAB')  ;

 

SEGMENT_NAME                 SEGMENT_TYPE    SIZE      TABLESPACE
SYS_IL0000067809C00001$$    LOBINDEX             131072      TOOLS
SYS_IL0000067809C00002$$    LOBINDEX             131072      TOOLS
LOB_TAB_COL2_LOB            LOBSEGMENT        2097152    TOOLS
LOB_TAB_COL1_LOB            LOBSEGMENT        2097152    TOOLS
LOB_TAB                             TABLE                   2097152    TOOLS

 

!! LOB Type 을 Move 하면 Lob type Column 에 대한 Index 도 자동으로

  이동 된다. !!

 

LOB INDEX 명 지정 및 LOB COLUMN 과 LOB COLUMN INDEX TABLESPACE 를 각각 지정

LOB INDEX 명의 지정은 가능하나, LOB INDEX 의 TABLESPACE 를 LOB COLUMN 의

TABLESPACE 와 별개로 지정 하기는 불가능하다.

 

 

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

 

CREATE TABLE test_lobtable (
id NUMBER ,
xml_file CLOB ,
image BLOB
)
LOB (xml_file) STORE AS
xml_file_lob_seg ( TABLESPACE USERS
STORAGE (MINEXTENTS 2)
INDEX xml_file_lob_idx ( TABLESPACE TOOLS ) )
LOB (image) STORE AS
image_lob_seg ( TABLESPACE USERS
STORAGE (MINEXTENTS 2)
INDEX image_lob_idx ( TABLESPACE TOOLS ) )
tablespace USERS ;

LOB COLUMN 의 TABLESPACE 와 LOB COLUMN 의 INDEX 의 TABLESPACE 를 다르게 지정 하였음

결과 확인 - 의미 없음 - LOB TYPE COLUMN INDEX 는 LOB TYPE 을 따라간다.

SEGMENT_NAME      SEGMENT_TYPE  SIZE    TABLESPACE_NAME
IMAGE_LOB_IDX        LOBINDEX         65536       USERS
XML_FILE_LOB_IDX    LOBINDEX         65536       USERS
IMAGE_LOB_SEG        LOBSEGMENT   2097152   USERS
XML_FILE_LOB_SEG    LOBSEGMENT  2097152    USERS
TEST_LOBTABLE        TABLE              65536        USERS

## LOB Type Column, Tob Type Column Index 의 TABLESPACE 를 동일 하게 지정

CREATE TABLE test_lobtable (
id NUMBER ,
xml_file CLOB ,
image BLOB
)
LOB (xml_file) STORE AS
xml_file_lob_seg ( TABLESPACE TOOLS
STORAGE (MINEXTENTS 2)
INDEX xml_file_lob_idx ( TABLESPACE TOOLS ) )
LOB (image) STORE AS
image_lob_seg ( TABLESPACE TOOLS
STORAGE (MINEXTENTS 2)
INDEX image_lob_idx ( TABLESPACE TOOLS ) )
tablespace USERS ;

SEGMENT_NAME      SEGMENT_TYPE  SIZE        TABLESPACE_NAME
IMAGE_LOB_IDX        LOBINDEX            65536       TOOLS
XML_FILE_LOB_IDX    LOBINDEX           65536        TOOLS
IMAGE_LOB_SEG        LOBSEGMENT    2097152     TOOLS
XML_FILE_LOB_SEG    LOBSEGMENT   2097152     TOOLS
TEST_LOBTABLE        TABLE                65536       USERS

###   Checking TG

--    create table check_clob ( a1 number, a2 number ) tablespace users ;
--   
--    alter table check_clob add ( a3 clob )

--     lob ( a3 ) store as check_clob_a3 ( tablespace users ) ;
--
--    select segment_name, segment_type, bytes , tablespace_name
--    from user_segments
--    where segment_name = 'CHECK_CLOB' or
--    segment_name in ( select segment_name from user_lobs

--    where table_name='CHECK_CLOB') or
--    segment_name in ( select index_name from user_lobs

--    where table_name='CHECK_CLOB')
--   
--    SYS_IL0000072013C00003$$ LOBINDEX 65536 USERS
--    CHECK_CLOB_A3             LOBSEGMENT 65536 USERS
--    CHECK_CLOB             TABLE     65536 USERS   

--    alter table check_clob add ( a4 clob )

--    lob ( a4 ) store as check_clob_a4 ( tablespace TOOLS ) ;

--    alter table check_clob add ( a5 clob ) ;

--    select segment_name, segment_type, bytes , tablespace_name
--    from user_segments
--    where segment_name = 'CHECK_CLOB' or
--    segment_name in ( select segment_name from user_lobs

--    where table_name='CHECK_CLOB') or
--    segment_name in ( select index_name from user_lobs

--    where table_name='CHECK_CLOB')
--    ORDER BY SEGMENT_TYPE

-- LOB TYPE 을 추가하면 STORAGE OPTION 을 지정 하던지 하지 않던지
-- LOBSEGMENT 와 LOBINDEX 가 추가 된다. 단지 유저가 지정 하는것과 하지

-- 않는 거의 차이가 있다.
-- 기본적으로 LOB TYPE 을 관리 하기 위해서 ORACLE 에서 Default 로
-- LOBSEGMENT 와 LOBINDEX 를 자동으로 생성 및 관리 하는 것으로 보여진다.

--    alter table CHECK_CLOB move
--    lob( a5 ) store as ( tablespace TS_HR_D )
--    tablespace TS_HR_D ;
--
--    select segment_name, segment_type, bytes , tablespace_name
--    from user_segments
--    where segment_name = 'CHECK_CLOB' or
--    segment_name in ( select segment_name from user_lobs

--    where table_name='CHECK_CLOB') or
--    segment_name in ( select index_name from user_lobs

--    where table_name='CHECK_CLOB')
--    ORDER BY SEGMENT_TYPE

--    SYS_IL0000072013C00003$$ LOBINDEX 65536 USERS
--    SYS_IL0000072013C00005$$ LOBINDEX 1048576 TS_HR_D
--    SYS_IL0000072013C00004$$ LOBINDEX 1048576 TOOLS
--    CHECK_CLOB_A3             LOBSEGMENT 65536 USERS
--    CHECK_CLOB_A4             LOBSEGMENT 1048576 TOOLS
--    SYS_LOB0000072013C00005$$ LOBSEGMENT 1048576 TS_HR_D
--    CHECK_CLOB             TABLE     1048576 TS_HR_D

--     alter table CHECK_CLOB move
--     lob(a3,a4) store as ( tablespace TS_HR_D  ) ;

--    select segment_name, segment_type, bytes , tablespace_name
--    from user_segments
--    where segment_name = 'CHECK_CLOB' or
--    segment_name in ( select segment_name from user_lobs

--     where table_name='CHECK_CLOB') or
--    segment_name in ( select index_name from user_lobs

--    where table_name='CHECK_CLOB')
--    ORDER BY SEGMENT_TYPE
--   
--    SYS_IL0000072013C00004$$ LOBINDEX   1048576 TS_HR_D
--    SYS_IL0000072013C00003$$ LOBINDEX   1048576 TS_HR_D
--    SYS_IL0000072013C00005$$ LOBINDEX   1048576 TS_HR_D
--    CHECK_CLOB_A4             LOBSEGMENT 1048576 TS_HR_D
--    SYS_LOB0000072013C00005$$ LOBSEGMENT 1048576 TS_HR_D
--    CHECK_CLOB_A3             LOBSEGMENT 1048576 TS_HR_D
--    CHECK_CLOB               TABLE       1048576 TS_HR_D