首页 » ORACLE 9i-23ai » 如何Onine Move LOB段到其它表空间在Oracle 12c+ ?

如何Onine Move LOB段到其它表空间在Oracle 12c+ ?

在Oracle 12c中你可以使用 “ALTER TABLE…MOVE ONLINE”, 在线移动LOB段 (Large Object)到其它表空间,而不会影响在线业务. 在移动空间或整理表空间碎片场景提供了遍历,此方法适用与CLOB和BLOB。 下面演示一下使用方法。

测试

SYS@orcl1>create user anbob identified by anbob;
User created.

SYS@orcl1>grant dba to anbob;
Grant succeeded.

SYS@orcl1>create table anbob.test_clob(
   id1 number,
   clob_col clob);

Table created.

insert into anbob.test_clob values (1,rpad('a',32760,'a'));

SYS@orcl1>select length(clob_col) from anbob.test_clob;

LENGTH(CLOB_COL)
----------------
            4000

SYS@orcl1>declare
  large_string clob := rpad('c',32760,'c');
  begin
  update anbob.test_clob set clob_col = nvl(large_string,clob_col) where id1 = 1;
  commit;
  end;
  / 

SYS@orcl1>select length(clob_col) from anbob.test_clob;

LENGTH(CLOB_COL)
----------------
           32760

SYS@orcl1>declare
i number:=0;
begin
while i<10 loop
 insert into anbob.test_clob select * from anbob.test_clob ; 
i:=i+1; 
end loop; 
end; / 
SYS@orcl1>@seg anbob.%

    SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                BLOCKS     HDRFIL     HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------ ---------- ---------- ----------
         0 ANBOB                SYS_IL0000074007C00002$$                                      LOBINDEX             USERS                                   8         14        186
         0 ANBOB                TEST_CLOB                                                     TABLE                USERS                                  16         14        162
        88 ANBOB                SYS_LOB0000074007C00002$$                                     LOBSEGMENT           USERS                               11288         14        169

SYS@orcl1>create index anbob.idx_test_clob on anbob.test_clob(id1);
Index created.

SYS@orcl1>@ind anbob.%
Display indexes where table or index name matches %anbob.%%...

TABLE_OWNER          TABLE_NAME                     INDEX_NAME                     POS# COLUMN_NAME                    DSC
-------------------- ------------------------------ ------------------------------ ---- ------------------------------ ----
ANBOB                TEST_CLOB                      IDX_TEST_CLOB                     1 ID1


INDEX_OWNER          TABLE_NAME                     INDEX_NAME                     IDXTYPE    UNIQ STATUS                   PART TEMP  H     LFBLKS           NDK   NUM_ROWS       CLUF LAST_ANALYZED       DEGREE VISIBILITY
-------------------- ------------------------------ ------------------------------ ---------- ---- ------------------------ ---- ---- -- ---------- ------------- ---------- ---------- ------------------- ------ ---------------------------
ANBOB                TEST_CLOB                      SYS_IL0000074007C00002$$       LOB        YES  VALID                    NO   N                                                                          0      VISIBLE
ANBOB                TEST_CLOB                      IDX_TEST_CLOB                  NORMAL     NO   VALID                    NO   N     2          2             1       1024          7 2024-01-13 20:21:12 1      VISIBLE

SYS@orcl1>create tablespace  newusers datafile '+FRA' size 200m;
Tablespace created.

Note:
创建了一个test_clob表,clob_col列为大字段, 为了演示对索引的影响,我们在id1列(与LOB无关)创建了个索引。 默认都在users表空间. 提前已创建好了newusers表空间用于移动。

Use ALTER TABLE…MOVE Command: Use the following command to move the LOB segment to the new tablespace:

ALTER TABLE table_name MOVE LOB(lob_column) STORE AS (TABLESPACE new_tablespace_name) [parallel] [online];

12c 后支持了Online选项。

ALTER TABLE MOVE LOB without nothing

SYS@orcl1>ALTER TABLE ANBOB.TEST_CLOB MOVE LOB( CLOB_COL) STORE AS (TABLESPACE NEWUSERS);
Table altered.

SYS@orcl1>@seg anbob.%

    SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                BLOCKS     HDRFIL     HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------ ---------- ---------- ----------
         0 ANBOB                SYS_IL0000074007C00002$$                                      LOBINDEX             NEWUSERS                                8         23        146
         0 ANBOB                TEST_CLOB                                                     TABLE                USERS                                  32         22       9476
        88 ANBOB                SYS_LOB0000074007C00002$$                                     LOBSEGMENT           NEWUSERS                            11280         23        129

3 rows selected.

SYS@orcl1>@ind anbob.%
Display indexes where table or index name matches %anbob.%%...

TABLE_OWNER          TABLE_NAME                     INDEX_NAME                     POS# COLUMN_NAME                    DSC
-------------------- ------------------------------ ------------------------------ ---- ------------------------------ ----
ANBOB                TEST_CLOB                      IDX_TEST_CLOB                     1 ID1


INDEX_OWNER          TABLE_NAME                     INDEX_NAME                     IDXTYPE    UNIQ STATUS                   PART TEMP  H     LFBLKS           NDK   NUM_ROWS       CLUF LAST_ANALYZED       DEGREE VISIBILITY
-------------------- ------------------------------ ------------------------------ ---------- ---- ------------------------ ---- ---- -- ---------- ------------- ---------- ---------- ------------------- ------ ---------------------------
ANBOB                TEST_CLOB                      SYS_IL0000074007C00002$$       LOB        YES  VALID                    NO   N                                                                          0      VISIBLE
ANBOB                TEST_CLOB                      IDX_TEST_CLOB                  NORMAL     NO   UNUSABLE                 NO   N     2          2             1       1024          7 2024-01-13 20:21:12 1      VISIBLE

SYS@orcl1>alter index anbob.IDX_TEST_CLOB rebuild;
Index altered.

Note:
什么选项都不带MOVE lob会导致LOB相关的表上的索引失效,即使索引列与LOB无直接关系。 如果有停机维护窗口可以这么做,记的重建索引使有效。

ALTER TABLE MOVE LOB update indexes

SYS@orcl1>ALTER TABLE ANBOB.TEST_CLOB MOVE LOB( CLOB_COL) STORE AS (TABLESPACE users ) update indexes;
Table altered.

SYS@orcl1>@ind anbob.%
Display indexes where table or index name matches %anbob.%%...

TABLE_OWNER          TABLE_NAME                     INDEX_NAME                     POS# COLUMN_NAME                    DSC
-------------------- ------------------------------ ------------------------------ ---- ------------------------------ ----
ANBOB                TEST_CLOB                      IDX_TEST_CLOB                     1 ID1


INDEX_OWNER          TABLE_NAME                     INDEX_NAME                     IDXTYPE    UNIQ STATUS                   PART TEMP  H     LFBLKS           NDK   NUM_ROWS       CLUF LAST_ANALYZED       DEGREE VISIBILITY
-------------------- ------------------------------ ------------------------------ ---------- ---- ------------------------ ---- ---- -- ---------- ------------- ---------- ---------- ------------------- ------ ---------------------------
ANBOB                TEST_CLOB                      IDX_TEST_CLOB                  NORMAL     NO   VALID                    NO   N     2          2             1       1024         16 2024-01-13 20:29:45 1      VISIBLE
                     TEST_CLOB                      SYS_IL0000074007C00002$$       LOB        YES  VALID                    NO   N                                                                          0      VISIBLE
SYS@orcl1>>@seg anbob.%

    SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                                                                            BLOCKS     HDRFIL     HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------------------------------------------------------------------ ---------- ---------- ----------
         0 ANBOB                SYS_IL0000074007C00002$$                                      LOBINDEX             USERS                                                                                               8         14        146
         0 ANBOB                TEST_CLOB                                                     TABLE                USERS                                                                                              32         14        388
        80 ANBOB                SYS_LOB0000074007C00002$$                                     LOBSEGMENT           USERS                                                                                           10256         14        129
         0 ANBOB                IDX_TEST_CLOB                                                 INDEX                USERS                                                                                               8         14        154

Note:
在12c之前使用update indexes可以在move table时维护索引,但是move lob的操作依旧会有可能堵塞业务。

ALTER TABLE MOVE LOB Online

SYS@orcl1>ALTER TABLE ANBOB.TEST_CLOB MOVE LOB( CLOB_COL) STORE AS (TABLESPACE newusers )  ONLINE;
Table altered.

SYS@orcl1>@seg anbob.%

    SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                                                                            BLOCKS     HDRFIL     HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------------------------------------------------------------------ ---------- ---------- ----------
         0 ANBOB                SYS_IL0000074007C00002$$                                      LOBINDEX             NEWUSERS                                                                                            8         23        146
         0 ANBOB                TEST_CLOB                                                     TABLE                USERS                                                                                              16         14        178
        80 ANBOB                SYS_LOB0000074007C00002$$                                     LOBSEGMENT           NEWUSERS                                                                                        10256         23        129
         0 ANBOB                IDX_TEST_CLOB                                                 INDEX                USERS                                                                                               8         14        170

SYS@orcl1>@ind anbob.%
Display indexes where table or index name matches %anbob.%%...

TABLE_OWNER          TABLE_NAME                     INDEX_NAME                     POS# COLUMN_NAME                    DSC
-------------------- ------------------------------ ------------------------------ ---- ------------------------------ ----
ANBOB                TEST_CLOB                      IDX_TEST_CLOB                     1 ID1


INDEX_OWNER          TABLE_NAME                     INDEX_NAME                     IDXTYPE    UNIQ STATUS                   PART TEMP  H     LFBLKS           NDK   NUM_ROWS       CLUF LAST_ANALYZED       DEGREE VISIBILITY
-------------------- ------------------------------ ------------------------------ ---------- ---- ------------------------ ---- ---- -- ---------- ------------- ---------- ---------- ------------------- ------ ---------------------------
ANBOB                TEST_CLOB                      IDX_TEST_CLOB                  NORMAL     NO   VALID                    NO   N     2          2             1       1024          7 2024-01-13 20:33:47 1      VISIBLE
                     TEST_CLOB                      SYS_IL0000074007C00002$$       LOB        YES  VALID                    NO   N                                                                          0      VISIBLE

Note:
12c 以后使用move lob online即维护了索引使一致有效,保证了业务高可用性。

ALTER TABLE MOVE LOB PARALLEL ONLINE

SYS@orcl1>ALTER TABLE ANBOB.TEST_CLOB MOVE LOB( CLOB_COL) STORE AS (TABLESPACE NEWUSERS) parallel 96 ONLINE;
-- WAIT

SYS@orcl1>@ase

                                                                                              last_call
PDB        USERNAME          SID EVENT                MACHINE    MODULE               STATUS         et SQL_ID          WAI_SECINW ROW_WAIT_OBJ# SQLTEXT                        BS          CH# OSUSER     HEX
---------- ---------- ---------- -------------------- ---------- -------------------- ------- --------- --------------- ---------- ------------- ------------------------------ ---------- ---- ---------- ----------
                              46 jobq slave wait      orclrac1                        ACTIVE          6                 0:0                   -1                                :             0 oracle
                             433 jobq slave wait      orclrac1                        ACTIVE         17                 0:0                   -1                                :             0 oracle
           SYS               301 PX Deq: Execution Ms orclrac1                        ACTIVE        112 fxvvka3adsht3   0:84                  -1 CREATE UNIQUE INDEX "ANBOB"."P :             0 oracle       1000000
           SYS               164 PX Deq: Execution Ms orclrac1                        ACTIVE        112 fxvvka3adsht3   0:111                 -1 CREATE UNIQUE INDEX "ANBOB"."P :             0 oracle       1000000
           SYS               431 PX Deq: Execution Ms orclrac1                        ACTIVE        112 fxvvka3adsht3   0:111                 -1 CREATE UNIQUE INDEX "ANBOB"."P :             0 oracle       1000000
           SYS               156 PX Deq: Execution Ms orclrac1                        ACTIVE        112 fxvvka3adsht3   0:111                 -1 CREATE UNIQUE INDEX "ANBOB"."P :             0 oracle       1000000
           SYS               154 PX Deq: Execution Ms orclrac1                        ACTIVE        112 fxvvka3adsht3   0:111                 -1 CREATE UNIQUE INDEX "ANBOB"."P :             0 oracle       1000000
           SYS                43 PX Deq: Execution Ms orclrac1                        ACTIVE        112 fxvvka3adsht3   0:111                 -1 CREATE UNIQUE INDEX "ANBOB"."P :             0 oracle       1000000
...
           SYS               421 PX Deq: Execution Ms orclrac1                        ACTIVE        112 fxvvka3adsht3   0:112                 -1 CREATE UNIQUE INDEX "ANBOB"."P :             0 oracle       1000000
           SYS               422 PX Deq: Execution Ms orclrac1                        ACTIVE        112 fxvvka3adsht3   0:112                 -1 CREATE UNIQUE INDEX "ANBOB"."P :             0 oracle       1000000
           SYS               423 PX Deq: Execution Ms orclrac1                        ACTIVE        112 fxvvka3adsht3   0:112                 -1 CREATE UNIQUE INDEX "ANBOB"."P :             0 oracle       1000000
           SYS               425 PX Deq: Execution Ms orclrac1                        ACTIVE        112 fxvvka3adsht3   0:112                 -1 CREATE UNIQUE INDEX "ANBOB"."P :             0 oracle       1000000
           SYS               426 PX Deq: Execution Ms orclrac1                        ACTIVE        112 fxvvka3adsht3   0:112                 -1 CREATE UNIQUE INDEX "ANBOB"."P :             0 oracle       1000000
           SYS               428 PX Deq: Execution Ms orclrac1                        ACTIVE        112 fxvvka3adsht3   0:112                 -1 CREATE UNIQUE INDEX "ANBOB"."P :             0 oracle       1000000
           SYS               434 PX Deq: Execution Ms orclrac1                        ACTIVE        112 fxvvka3adsht3   0:112                 -1 CREATE UNIQUE INDEX "ANBOB"."P :             0 oracle       1000000
           SYS               435 PX Deq: Execution Ms orclrac1                        ACTIVE        112 fxvvka3adsht3   0:112                 -1 CREATE UNIQUE INDEX "ANBOB"."P :             0 oracle       1000000
ANBOB      SYS               409 PX Deq: Parse Reply  orclrac1   sqlplus              ACTIVE        118 232m2hhgazrx4   0:1                   -1 ALTER TABLE ANBOB.TEST_CLOB MO 1:47          0 oracle       1000000

83 rows selected.

Note:
有时可能其它增加parallel 在move update indexes或online希望加速时,可能会有一些等待,如果长时间等待,可以不用并行。

监控move进度
当开始Move后可以查询动态性能VIEW”V$SESSION_LONGOPS”查看move进度。

批量MOVE脚本

select 'ALTER TABLE '||owner||'.'||table_name||' MOVE LOB( '||column_name||') STORE AS (TABLESPACE NEW_TABLESPACE_NAME) online;' 
from dba_lobs 
where tablespace_name='OLD_TABLESPACE_NAME';

select 'ALTER TABLE '||table_owner||'.'||table_name||' MOVE partition '||Partition_name||' lob('||column_name||')'||' STORE AS (TABLESPACE NEW_TABLESPACE_NAME) ;' 
from dba_lob_partitions 
where  tablespace_name='OLD_TABLESPACE_NAME';

— enjoy —

打赏

对不起,这篇文章暂时关闭评论。