首页 » 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 —
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。