Move Lob Segment

March 2nd, 2007 | Categories: Boring | Tags:
Leave a comment | Trackback

Oracle9205中测试移动Lob Segment,是相当的慢。Parallel 选项对move lob segment不起作用，我没有从PQ的v$session_event中看到direct path read(lob)和较多的db file scatter read.只有QC在移动lob segment.

指定 Nologging 倒是可以提高move lob segment的速度。测试如下，

SQL> create table t tablespace md_data nologging as select * from TABLE_CONTAIN_LOBSEG where rownum<=19000;
Table created.
SQL> List LOB SPACE 3560M
OWNER       SEGMENT_NAME                                 SEGMENT_TYPE              size(M)
——————– —————————— —————————— —–
ORACLE     SYS_LOB0000019962C00004$$          LOBSEGMENT                  3560

SQL> set timing on
SQL> alter table t move tablespace TESTLMT nologging lob(content) store as (tablespace TESTLMT cache);
Table altered.
Elapsed: 00:07:07.75

SQL> alter table t move tablespace TESTLMT nologging lob(content) store as (tablespace TESTLMT nocache nologging);
Table altered.
Elapsed: 00:04:30.06

Notice: Move lob segment的时候，表也会被移动，相应的索引需要rebuild>>.

Del.icio.us Google书签 Digg Live Bookmark Technorati Furl Yahoo书签 Facebook 百度搜藏 新浪 ViVi 365Key 网摘 天极网摘 和讯网摘 博拉网 POCO 网摘 饭否 QQ 书签 Digbuzz 我挖网 Mister Wong
  1. yumianfeilong
    September 14th, 2009 at 21:18
    Quote | #1

    Syntax to change default parameters of a partitioned table’s lob segments.

    alter table Partitioned_table_with_lob modify default attributes lob (data_blob) (tablespace lob_data02 );