移动lob对象段

移动普通表时语法:
  ALTER TABLE table_name move [tablespace tablespace_name] LOB (lob_item) STORE AS [lob_segment]
      (
        TABLESPACE tablespace_name
                   (STORAGE.....)
           ENABLE|DISABLE STORAGE IN ROW
           CHUNK integer
           PCTVERSION integer
           RETENTION
           FREEPOOLS integer
           CACHE|NOCACHE|CACHE READS
           INDEX lobindexname
       );
移动分区表时语法:
  alter table move partition lob () store as ( tablespace );

SQL> create table tclob(
  2    id number,
  3    name clob
  4  )tablespace users
  5  lob(name) store as tc_name(tablespace users );

表已创建。

已用时间:  00: 00: 00.04
SQL> select segment_name, segment_type, tablespace_name
  2    from user_segments
  3   where segment_type like 'LOB%'
  4      or segment_name = 'TCLOB';

SEGMENT_NAME              SEGMENT_TYPE                   TABLESPACE
------------------------- ------------------------------ ----------
SYS_IL0000011838C00002$$  LOBINDEX                       USERS
TC_NAME                   LOBSEGMENT                     USERS
TCLOB                     TABLE                          USERS

已用时间:  00: 00: 00.01

SQL> select table_name, column_name, segment_name, tablespAce_name, index_name
  2    from user_lobs
  3     where table_name ='TCLOB';

TABLE_NAME COLUMN_NAM SEGMENT_NAME    TABLESPACE INDEX_NAME
---------- ---------- --------------- ---------- ------------------------------
TCLOB      NAME       TC_NAME         USERS      SYS_IL0000011838C00002$$

已用时间:  00: 00: 00.00

SQL> select object_name, object_id, data_object_id
  2    from user_objects
  3   where object_name = 'TCLOB';

OBJECT_NAM  OBJECT_ID DATA_OBJECT_ID
---------- ---------- --------------
TCLOB           11838          11838

已用时间:  00: 00: 00.00

SQL> insert into tclob(id,name)values(1,'a') ;

已创建 1 行。

已用时间:  00: 00: 00.00
SQL> commit;

提交完成。

已用时间:  00: 00: 00.00

SQL> select rowid from tclob;

ROWID
------------------
AAAC4+AAEAAAAGjAAA

已用时间:  00: 00: 00.00

SQL> alter table tclob move lob(name) store as (tablespace rman);

表已更改。

已用时间:  00: 00: 00.07

SQL> select segment_name, segment_type, tablespace_name
  2    from user_segments
  3   where segment_type like 'LOB%'
  4      or segment_name = 'TCLOB';

SEGMENT_NAME              SEGMENT_TYPE                   TABLESPACE
------------------------- ------------------------------ ----------
TCLOB                     TABLE                          USERS
SYS_IL0000011838C00002$$  LOBINDEX                       RMAN
TC_NAME                   LOBSEGMENT                     RMAN

已用时间:  00: 00: 00.03

SQL> select table_name, column_name, segment_name, tablespAce_name, index_name
  2    from user_lobs
  3     where table_name ='TCLOB';

TABLE_NAME COLUMN_NAM SEGMENT_NA TABLESPACE INDEX_NAME
---------- ---------- ---------- ---------- ------------------------------
TCLOB      NAME       TC_NAME    RMAN       SYS_IL0000011838C00002$$

SQL> select object_name, object_id, data_object_id
  2    from user_objects
  3   where object_name = 'TCLOB';

OBJECT_NAM  OBJECT_ID DATA_OBJECT_ID
---------- ---------- --------------
TCLOB           11838          11841

已用时间:  00: 00: 00.00

SQL> select rowid from tclob;

ROWID
------------------
AAAC5BAAEAAAAQNAAA

已用时间:  00: 00: 00.01

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11134237/viewspace-631299/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11134237/viewspace-631299/