在12c之前，move table属于一个离线操作，由于TM锁不兼容会阻塞dml操作，并且move之后表上索引会变成unusable，这对于7*24小时的业务场景是不可接受的，如果想实现online move table必须要使用在线重定义来完成。而在线重定义操作步骤还是相对比较繁琐的。
为了简化online move table功能，oracle在12.1推出了move table partitions和sub-partitions online功能，并且在12.2引入了online move table去替代12c之前繁琐的在线重定义，但并不是在线重定义的所有使用场景都能替代，毕竟在线重定义功能非常强大，使用场景也非常多，比如：在线修改字段名、在线增删字段、按某个字段列排序重组表(多半是为了降低某些索引的聚簇因子)等等，online move table就无法实现。
老样子，本文也是通过测试验证来深入解析oracle是如何实现online move table的。
测试环境与对象：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SQL> select BANNER from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production PL/SQL Release 12.2.0.1.0 - Production CORE 12.2.0.1.0 Production TNS for Linux: Version 12.2.0.1.0 - Production NLSRTL Version 12.2.0.1.0 - Production SQL> select a.owner,a.object_name,object_type,b.object_id,b.data_object_id,a.status,c.header_file,c.header_block from 2 (select owner,index_name object_name,table_name,status from dba_indexes 3 union all 4 select owner,table_name,table_name,status from dba_tables) a,dba_objects b,dba_segments c 5 where a.owner=b.owner and a.object_name=b.object_name and a.owner=c.owner and a.object_name=c.segment_name and a.table_name='T1' and a.owner='TEST'; OWNER OBJECT_NAM OBJECT_TYPE OBJECT_ID DATA_OBJECT_ID STATUS HEADER_FILE HEADER_BLOCK ---------- ---------- ----------------------- ---------- -------------- -------- ----------- ------------ TEST T1 TABLE 73501 74080 VALID 12 162 TEST IDX_1 INDEX 73933 74086 VALID 12 154 |
第一步发起online move命令表上会立刻持有2号TM锁,之后调用ctcsoo_setup_online_op开始online操作会将表锁模式改为3号TM锁。
|
1 2 |
2021-12-01 12:30:23.165*:ksq.c@9033:ksqgtlctx(): *** TM-00011F1D-00000000-1C84D575-00000000 mode=2 flags=0x401 why=167 timeout=0 *** 2021-12-01 12:30:46.760*:ksq.c@7192:ksqcmi(): TM-00011F1D-00000000-1C84D575-00000000 mode=3 timeout=0 |
第二步调用ctccjt_create_journal_table函数创建JOURNAL表， JOURNAL表是一个IOT表，之后调用kkzuRmtCreate函数创建一个RMTAB_H表，该表是一个堆表，创建完成两个内部表之后，开始move表数据，并且产生表的临时段。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 |
===================== PARSING IN CURSOR #139664876325488 len=123 dep=1 uid=0 oct=1 lid=0 tim=19811466150 hv=1920156367 ad='75ab4fc0' sqlid='0nxf92pt76hqg' create table "TEST"."SYS_JOURNAL_73501" (rid rowid, opcode char(1), primary key(rid)) organization index tablespace "USERS" ===================== PARSING IN CURSOR #139664881126800 len=208 dep=2 uid=0 oct=9 lid=0 tim=19811536518 hv=3693441280 ad='77126740' sqlid='gctgcqrf2aw80' CREATE UNIQUE INDEX "TEST"."SYS_IOT_TOP_74088" on "TEST"."SYS_JOURNAL_73501"("RID") INDEX ONLY TOPLEVEL TABLESPACE "USERS" STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) NOPARALLEL ===================== PARSING IN CURSOR #139664876325488 len=175 dep=1 uid=0 oct=1 lid=0 tim=20544963909 hv=2976783715 ad='7688e588' sqlid='2krctffsqw7b3' create table "TEST"."SYS_RMTAB$$_H73501" ( src_rowid rowid not null , tgt_rowid rowid not null) segment creation immediate nologging tablespace "USERS" rowid_mapping_table WAIT #139664882148128: nam='db file sequential read' ela= 2 file#=12 block#=162 blocks=1 obj#=73501 tim=21591254175 WAIT #139664882148128: nam='db file scattered read' ela= 8 file#=12 block#=163 blocks=5 obj#=73501 tim=21591254211 WAIT #139664882148128: nam='db file scattered read' ela= 10 file#=12 block#=168 blocks=8 obj#=73501 tim=21591254435 WAIT #139664882148128: nam='db file scattered read' ela= 12 file#=12 block#=177 blocks=7 obj#=73501 tim=21591254776 WAIT #139664882148128: nam='db file scattered read' ela= 18 file#=12 block#=192 blocks=8 obj#=73501 tim=21591255029 WAIT #139664882148128: nam='db file scattered read' ela= 10 file#=12 block#=209 blocks=7 obj#=73501 tim=21591255326 WAIT #139664882148128: nam='db file scattered read' ela= 10 file#=12 block#=216 blocks=8 obj#=73501 tim=21591255565 WAIT #139664882148128: nam='db file scattered read' ela= 11 file#=12 block#=225 blocks=7 obj#=73501 tim=21591255930 WAIT #139664882148128: nam='db file scattered read' ela= 10 file#=12 block#=232 blocks=8 obj#=73501 tim=21591256254 WAIT #139664882148128: nam='db file scattered read' ela= 10 file#=12 block#=249 blocks=7 obj#=73501 tim=21591256531 WAIT #139664882148128: nam='db file scattered read' ela= 55 file#=12 block#=256 blocks=8 obj#=73501 tim=21591257085 WAIT #139664882148128: nam='db file sequential read' ela= 12 file#=12 block#=162 blocks=1 obj#=73501 tim=21591257689 WAIT #139664882148128: nam='db file sequential read' ela= 26 file#=12 block#=162 blocks=1 obj#=73501 tim=21591257761 WAIT #139664882148128: nam='db file scattered read' ela= 66 file#=12 block#=5521 blocks=7 obj#=73501 tim=21591257951 WAIT #139664882148128: nam='db file scattered read' ela= 86 file#=12 block#=5528 blocks=8 obj#=73501 tim=21591258550 WAIT #139664882148128: nam='db file scattered read' ela= 54 file#=12 block#=5545 blocks=7 obj#=73501 tim=21591259305 WAIT #139664882148128: nam='db file scattered read' ela= 47 file#=12 block#=5888 blocks=8 obj#=73501 tim=21591259785 WAIT #139664882148128: nam='db file scattered read' ela= 88 file#=12 block#=5897 blocks=7 obj#=73501 tim=21591261852 WAIT #139664882148128: nam='PGA memory operation' ela= 65 p1=65536 p2=1 p3=0 obj#=73501 tim=21591262562 WAIT #139664882148128: nam='db file scattered read' ela= 37 file#=12 block#=5928 blocks=8 obj#=73501 tim=21591263700 WAIT #139664882148128: nam='PGA memory operation' ela= 6 p1=65536 p2=1 p3=0 obj#=73501 tim=21591263912 WAIT #139664882148128: nam='PGA memory operation' ela= 5 p1=65536 p2=1 p3=0 obj#=73501 tim=21591264350 WAIT #139664882148128: nam='db file scattered read' ela= 186 file#=12 block#=386 blocks=68 obj#=73501 tim=21591264900 WAIT #139664882148128: nam='PGA memory operation' ela= 6 p1=65536 p2=1 p3=0 obj#=73501 tim=21591265486 WAIT #139664882148128: nam='PGA memory operation' ela= 5 p1=65536 p2=1 p3=0 obj#=73501 tim=21591265791 WAIT #139664882148128: nam='PGA memory operation' ela= 5 p1=65536 p2=1 p3=0 obj#=73501 tim=21591266071 WAIT #139664882148128: nam='PGA memory operation' ela= 5 p1=65536 p2=1 p3=0 obj#=73501 tim=21591266437 WAIT #139664882148128: nam='PGA memory operation' ela= 18 p1=1114112 p2=1 p3=0 obj#=73501 tim=21591275803 WAIT #139664882148128: nam='PGA memory operation' ela= 8 p1=1114112 p2=1 p3=0 obj#=73501 tim=21591276442 WAIT #139664882148128: nam='direct path write' ela= 19 file number=12 first dba=187 block cnt=5 obj#=73501 tim=21591280899 WAIT #139664882148128: nam='PGA memory operation' ela= 8 p1=1114112 p2=1 p3=0 obj#=73501 tim=21591280937 WAIT #139664882148128: nam='direct path write' ela= 19 file number=12 first dba=240 block cnt=8 obj#=73501 tim=21591283433 WAIT #139664882148128: nam='direct path write' ela= 16 file number=12 first dba=147 block cnt=5 obj#=73501 tim=21591293815 WAIT #139664882148128: nam='PGA memory operation' ela= 73 p1=1114112 p2=2 p3=0 obj#=73501 tim=21591293930 WAIT #139664882148128: nam='direct path write' ela= 66 file number=12 first dba=5137 block cnt=15 obj#=73501 tim=21591296286 WAIT #139664882148128: nam='direct path write' ela= 29 file number=12 first dba=5161 block cnt=15 obj#=73501 tim=21591300075 WAIT #139664882148128: nam='direct path write' ela= 73 file number=12 first dba=5177 block cnt=15 obj#=73501 tim=21591304351 WAIT #139664882148128: nam='direct path write' ela= 17 file number=12 first dba=5152 block cnt=8 obj#=73501 tim=21591309769 WAIT #139664882148128: nam='direct path write' ela= 32 file number=12 first dba=5193 block cnt=15 obj#=73501 tim=21591312628 WAIT #139664882148128: nam='direct path write' ela= 25 file number=12 first dba=5217 block cnt=15 obj#=73501 tim=21591318565 WAIT #139664882148128: nam='direct path write' ela= 25 file number=12 first dba=5233 block cnt=15 obj#=73501 tim=21591321883 WAIT #139664882148128: nam='direct path write' ela= 44 file number=12 first dba=5209 block cnt=7 obj#=73501 tim=21591323367 WAIT #139664882148128: nam='direct path write' ela= 16 file number=12 first dba=5505 block cnt=7 obj#=73501 tim=21591324726 WAIT #139664882148128: nam='direct path write' ela= 18 file number=12 first dba=5536 block cnt=8 obj#=73501 tim=21591326165 WAIT #139664882148128: nam='direct path write' ela= 43 file number=12 first dba=5512 block cnt=8 obj#=73501 tim=21591329462 WAIT #139664882148128: nam='db file scattered read' ela= 536 file#=12 block#=454 blocks=58 obj#=73501 tim=21591335335 WAIT #139664882148128: nam='direct path write' ela= 33 file number=12 first dba=5553 block cnt=15 obj#=73501 tim=21591338483 WAIT #139664882148128: nam='db file scattered read' ela= 177 file#=12 block#=5634 blocks=68 obj#=73501 tim=21591339545 WAIT #139664882148128: nam='direct path write' ela= 290 file number=12 first dba=514 block cnt=126 obj#=73501 tim=21591341701 WAIT #139664882148128: nam='direct path write' ela= 27 file number=12 first dba=5569 block cnt=15 obj#=73501 tim=21591346682 WAIT #139664882148128: nam='db file scattered read' ela= 787 file#=12 block#=5702 blocks=58 obj#=73501 tim=21591350263 WAIT #139664882148128: nam='direct path write' ela= 29 file number=12 first dba=5585 block cnt=15 obj#=73501 tim=21591357386 WAIT #139664882148128: nam='db file scattered read' ela= 122 file#=12 block#=6018 blocks=68 obj#=73501 tim=21591357808 WAIT #139664882148128: nam='direct path write' ela= 206 file number=12 first dba=642 block cnt=126 obj#=73501 tim=21591359873 WAIT #139664882148128: nam='db file scattered read' ela= 82 file#=12 block#=6086 blocks=58 obj#=73501 tim=21591364268 WAIT #139664882148128: nam='direct path write' ela= 110 file number=12 first dba=5601 block cnt=15 obj#=73501 tim=21591366599 WAIT #139664882148128: nam='db file scattered read' ela= 138 file#=12 block#=6146 blocks=68 obj#=73501 tim=21591372997 WAIT #139664882148128: nam='direct path write' ela= 207 file number=12 first dba=770 block cnt=126 obj#=73501 tim=21591374951 WAIT #139664882148128: nam='direct path write' ela= 24 file number=12 first dba=5617 block cnt=15 obj#=73501 tim=21591377199 WAIT #139664882148128: nam='db file scattered read' ela= 131 file#=12 block#=6214 blocks=58 obj#=73501 tim=21591380924 WAIT #139664882148128: nam='direct path write' ela= 99 file number=12 first dba=5905 block cnt=15 obj#=73501 tim=21591383455 WAIT #139664882148128: nam='db file scattered read' ela= 339 file#=12 block#=6530 blocks=68 obj#=73501 tim=21591386267 WAIT #139664882148128: nam='direct path write' ela= 203 file number=12 first dba=898 block cnt=126 obj#=73501 tim=21591388587 WAIT #139664882148128: nam='db file scattered read' ela= 81 file#=12 block#=6598 blocks=58 obj#=73501 tim=21591391704 WAIT #139664882148128: nam='db file scattered read' ela= 66 file#=12 block#=6658 blocks=68 obj#=73501 tim=21591394068 WAIT #139664882148128: nam='direct path write' ela= 193 file number=12 first dba=1154 block cnt=126 obj#=73501 tim=21591395856 WAIT #139664882148128: nam='db file scattered read' ela= 62 file#=12 block#=6726 blocks=58 obj#=73501 tim=21591398412 WAIT #139664882148128: nam='db file scattered read' ela= 90 file#=12 block#=6786 blocks=68 obj#=73501 tim=21591400671 WAIT #139664882148128: nam='direct path write' ela= 192 file number=12 first dba=1282 block cnt=126 obj#=73501 tim=21591402495 WAIT #139664882148128: nam='db file scattered read' ela= 65 file#=12 block#=6854 blocks=58 obj#=73501 tim=21591405135 WAIT #139664882148128: nam='db file scattered read' ela= 67 file#=12 block#=6914 blocks=68 obj#=73501 tim=21591407414 WAIT #139664882148128: nam='direct path write' ela= 212 file number=12 first dba=1410 block cnt=126 obj#=73501 tim=21591409245 WAIT #139664882148128: nam='db file scattered read' ela= 64 file#=12 block#=6982 blocks=58 obj#=73501 tim=21591411867 WAIT #139664882148128: nam='db file scattered read' ela= 62 file#=12 block#=7042 blocks=68 obj#=73501 tim=21591414258 WAIT #139664882148128: nam='direct path write' ela= 209 file number=12 first dba=1538 block cnt=126 obj#=73501 tim=21591416216 WAIT #139664882148128: nam='db file scattered read' ela= 61 file#=12 block#=7110 blocks=58 obj#=73501 tim=21591419000 WAIT #139664882148128: nam='db file scattered read' ela= 67 file#=12 block#=7170 blocks=68 obj#=73501 tim=21591421413 WAIT #139664882148128: nam='direct path write' ela= 186 file number=12 first dba=1666 block cnt=126 obj#=73501 tim=21591423282 WAIT #139664882148128: nam='direct path write' ela= 171 file number=12 first dba=1026 block cnt=126 obj#=73501 tim=21591425713 WAIT #139664882148128: nam='db file scattered read' ela= 66 file#=12 block#=7238 blocks=58 obj#=73501 tim=21591427835 WAIT #139664882148128: nam='db file scattered read' ela= 42 file#=12 block#=7426 blocks=38 obj#=73501 tim=21591430107 WAIT #139664882148128: nam='direct path write' ela= 157 file number=12 first dba=5378 block cnt=126 obj#=73501 tim=21591431760 WAIT #139664882148128: nam='direct path write' ela= 53 file number=12 first dba=7298 block cnt=38 obj#=73501 tim=21591433227 WAIT #139664882148128: nam='direct path sync' ela= 15001 File number=12 Flags=0 p3=0 obj#=73501 tim=21591448348 WAIT #139664882148128: nam='direct path write' ela= 37 file number=12 first dba=5762 block cnt=27 obj#=73501 tim=21591449198 WAIT #139664882148128: nam='direct path sync' ela= 1235 File number=12 Flags=0 p3=0 obj#=73501 tim=21591450603 SQL> select owner,segment_name,header_file,header_block from dba_segments where segment_type='TEMPORARY'; OWNER SEGMENT_NAME HEADER_FILE HEADER_BLOCK ---------- ------------------------------ ----------- ------------ TEST 12.186 12 186 |
其中 JOURNAL表用于记录表的dml变更记录， RMTAB_H表用于记录原表与目标表记录的rowid对应关系。在后续merge JOURNAL表记录时将起到一个非常重要的作用。
在move期间测试插入9条记录删除1条数据，发现该阶段并不会构建rowid对应关系，但是会记录在 JOURNAL表中。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
SQL> insert into test.t1 select * from dba_objects where rownum<10; 9 rows created. SQL> commit; Commit complete. SQL> delete from test.t1 where rownum<=1; 1 row deleted. SQL> commit; Commit complete. SQL> select * from "TEST"."SYS_JOURNAL_73501" ; RID O ------------------ - AAASFgAAMAAAACjAAA D AAASFgAAMAAAB0uAAA I AAASFgAAMAAAB0uAAB I AAASFgAAMAAAB0uAAC I AAASFgAAMAAAB0uAAD I AAASFgAAMAAAB0uAAE I AAASFgAAMAAAB0uAAF I AAASFgAAMAAAB0uAAG I AAASFgAAMAAAB0uAAH I AAASFgAAMAAAB0uAAI I 10 rows selected. SQL> select count(*) from "TEST"."SYS_RMTAB$$_H73501" ; COUNT(*) ---------- 0 |
第三步move完成之后会调用ctcmerge函数构建rowid关联关系，RMTAB_H会比实际表少8条，因为我们刚才插入了9条数据删除了1条。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SQL> select count(*) from "TEST"."SYS_RMTAB$$_H73501" ; COUNT(*) ---------- 72654 SQL> select count(*) from "TEST"."SYS_RMTAB$$_H73501" ; COUNT(*) ---------- 72654 SQL> select count(*) from test.t1; COUNT(*) ---------- 72662 |
第四步调用kkzuRmtCreateINT函数创建RMTAB_I表，这是一个IOT表，其实就是从 SYS_RMTAB$$_H 中CTAS来的，这个表的作用是为了处理索引的。
|
1 2 |
PARSING IN CURSOR #139664881169800 len=302 dep=1 uid=0 oct=1 lid=0 tim=31174918657 hv=3869322410 ad='7836a6f0' sqlid='7hsunxbma2b5a' create table "TEST"."SYS_RMTAB$$_I73501" (src_rowid, tgt_rowid, constraint pk_SYS_RMTAB$$_I73501 primary key (src_rowid)) segment creation immediate organization index tablespace "USERS" noparallel nologging rowid_mapping_table as (select src_rowid, tgt_rowid from "TEST"."SYS_RMTAB$$_H73501" ) |
第五步调用kkzuRmtDrop函数删除 SYS_RMTAB$$_H，它的使命已经完成。
|
1 2 |
PARSING IN CURSOR #139664881568576 len=45 dep=1 uid=0 oct=12 lid=0 tim=32387658447 hv=2455444282 ad='7f0644ed2d00' sqlid='5ma092u95q6tu' drop table "TEST"."SYS_RMTAB$$_H73501" purge |
第六步调用kkpocim_create_indexes_modpart，创建一个不可见索引，从10046跟踪来看，读取的是新表的segment，这是在同步表上的索引了。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 |
===================== PARSING IN CURSOR #139664881111664 len=106 dep=1 uid=0 oct=9 lid=0 tim=32388088602 hv=1894036906 ad='769460d0' sqlid='262n8mpsf9dda' CREATE INDEX "TEST"."IDX_1_SYS_73501" ON "TEST"."T1"("OBJECT_ID") TABLESPACE "USERS" INVISIBLE NOPARALLEL WAIT #139664881111664: nam='db file scattered read' ela= 14 file#=12 block#=187 blocks=5 obj#=73501 tim=32388157492 WAIT #139664881111664: nam='db file scattered read' ela= 10 file#=12 block#=240 blocks=8 obj#=73501 tim=32388157559 WAIT #139664881111664: nam='db file scattered read' ela= 9 file#=12 block#=5137 blocks=7 obj#=73501 tim=32388157629 WAIT #139664881111664: nam='db file scattered read' ela= 9 file#=12 block#=5144 blocks=8 obj#=73501 tim=32388157690 WAIT #139664881111664: nam='db file scattered read' ela= 9 file#=12 block#=5161 blocks=7 obj#=73501 tim=32388157750 WAIT #139664881111664: nam='db file scattered read' ela= 9 file#=12 block#=5168 blocks=8 obj#=73501 tim=32388157808 WAIT #139664881111664: nam='db file scattered read' ela= 8 file#=12 block#=5177 blocks=7 obj#=73501 tim=32388157874 WAIT #139664881111664: nam='db file scattered read' ela= 9 file#=12 block#=5184 blocks=8 obj#=73501 tim=32388157933 WAIT #139664881111664: nam='db file scattered read' ela= 12 file#=12 block#=5193 blocks=7 obj#=73501 tim=32388158031 WAIT #139664881111664: nam='db file scattered read' ela= 10 file#=12 block#=5200 blocks=8 obj#=73501 tim=32388158094 WAIT #139664881111664: nam='db file scattered read' ela= 9 file#=12 block#=5217 blocks=7 obj#=73501 tim=32388158163 WAIT #139664881111664: nam='db file scattered read' ela= 9 file#=12 block#=5224 blocks=8 obj#=73501 tim=32388158223 WAIT #139664881111664: nam='db file scattered read' ela= 8 file#=12 block#=5233 blocks=7 obj#=73501 tim=32388158284 WAIT #139664881111664: nam='db file scattered read' ela= 9 file#=12 block#=5240 blocks=8 obj#=73501 tim=32388158341 WAIT #139664881111664: nam='db file scattered read' ela= 8 file#=12 block#=5505 blocks=7 obj#=73501 tim=32388158396 WAIT #139664881111664: nam='db file scattered read' ela= 9 file#=12 block#=5536 blocks=8 obj#=73501 tim=32388158455 WAIT #139664881111664: nam='db file scattered read' ela= 79 file#=12 block#=514 blocks=68 obj#=73501 tim=32388158636 WAIT #139664881111664: nam='db file scattered read' ela= 150 file#=12 block#=582 blocks=58 obj#=73501 tim=32388159145 WAIT #139664881111664: nam='db file scattered read' ela= 63 file#=12 block#=642 blocks=68 obj#=73501 tim=32388159539 WAIT #139664881111664: nam='db file scattered read' ela= 55 file#=12 block#=710 blocks=58 obj#=73501 tim=32388159966 WAIT #139664881111664: nam='db file scattered read' ela= 64 file#=12 block#=770 blocks=68 obj#=73501 tim=32388160418 WAIT #139664881111664: nam='db file scattered read' ela= 53 file#=12 block#=838 blocks=58 obj#=73501 tim=32388160872 WAIT #139664881111664: nam='db file scattered read' ela= 65 file#=12 block#=898 blocks=68 obj#=73501 tim=32388161367 WAIT #139664881111664: nam='db file scattered read' ela= 53 file#=12 block#=966 blocks=58 obj#=73501 tim=32388161824 WAIT #139664881111664: nam='db file scattered read' ela= 82 file#=12 block#=1154 blocks=68 obj#=73501 tim=32388162257 WAIT #139664881111664: nam='db file scattered read' ela= 52 file#=12 block#=1222 blocks=58 obj#=73501 tim=32388162705 WAIT #139664881111664: nam='db file scattered read' ela= 60 file#=12 block#=1282 blocks=68 obj#=73501 tim=32388163093 WAIT #139664881111664: nam='db file scattered read' ela= 52 file#=12 block#=1350 blocks=58 obj#=73501 tim=32388163599 WAIT #139664881111664: nam='db file scattered read' ela= 59 file#=12 block#=1410 blocks=68 obj#=73501 tim=32388163989 WAIT #139664881111664: nam='db file scattered read' ela= 51 file#=12 block#=1478 blocks=58 obj#=73501 tim=32388164434 WAIT #139664881111664: nam='db file scattered read' ela= 58 file#=12 block#=1538 blocks=68 obj#=73501 tim=32388164834 WAIT #139664881111664: nam='db file scattered read' ela= 50 file#=12 block#=1606 blocks=58 obj#=73501 tim=32388165247 WAIT #139664881111664: nam='db file scattered read' ela= 60 file#=12 block#=1666 blocks=68 obj#=73501 tim=32388165739 WAIT #139664881111664: nam='db file scattered read' ela= 52 file#=12 block#=1734 blocks=58 obj#=73501 tim=32388166176 WAIT #139664881111664: nam='db file scattered read' ela= 60 file#=12 block#=5378 blocks=68 obj#=73501 tim=32388166603 WAIT #139664881111664: nam='db file scattered read' ela= 51 file#=12 block#=5446 blocks=58 obj#=73501 tim=32388167027 WAIT #139664881111664: nam='db file scattered read' ela= 35 file#=12 block#=7298 blocks=38 obj#=73501 tim=32388167381 WAIT #139664881111664: nam='direct path write' ela= 10919 file number=12 first dba=148 block cnt=4 obj#=-1 tim=32934522810 WAIT #139664881111664: nam='direct path write' ela= 1605 file number=12 first dba=5152 block cnt=4 obj#=-1 tim=32934525053 WAIT #139664881111664: nam='direct path write' ela= 10255 file number=12 first dba=5156 block cnt=4 obj#=-1 tim=33235133502 WAIT #139664881111664: nam='direct path write' ela= 1534 file number=12 first dba=5209 block cnt=3 obj#=-1 tim=33235135738 WAIT #139664881111664: nam='direct path write' ela= 10047 file number=12 first dba=5212 block cnt=4 obj#=-1 tim=33236070132 WAIT #139664881111664: nam='direct path write' ela= 789 file number=12 first dba=5512 block cnt=4 obj#=-1 tim=33236071461 WAIT #139664881111664: nam='direct path write' ela= 9134 file number=12 first dba=5516 block cnt=4 obj#=-1 tim=33236802219 WAIT #139664881111664: nam='direct path write' ela= 1167 file number=12 first dba=5553 block cnt=3 obj#=-1 tim=33236804123 WAIT #139664881111664: nam='direct path write' ela= 10608 file number=12 first dba=5556 block cnt=4 obj#=-1 tim=33237356560 WAIT #139664881111664: nam='direct path write' ela= 851 file number=12 first dba=5560 block cnt=4 obj#=-1 tim=33237357946 WAIT #139664881111664: nam='direct path write' ela= 10336 file number=12 first dba=5564 block cnt=4 obj#=-1 tim=33238338875 WAIT #139664881111664: nam='direct path write' ela= 3125 file number=12 first dba=5569 block cnt=3 obj#=-1 tim=33238342550 WAIT #139664881111664: nam='direct path write' ela= 4871 file number=12 first dba=5572 block cnt=4 obj#=-1 tim=33239044051 WAIT #139664881111664: nam='direct path write' ela= 608 file number=12 first dba=5576 block cnt=4 obj#=-1 tim=33239045364 WAIT #139664881111664: nam='direct path write' ela= 4324 file number=12 first dba=5580 block cnt=4 obj#=-1 tim=33239712827 WAIT #139664881111664: nam='direct path write' ela= 1311 file number=12 first dba=5585 block cnt=3 obj#=-1 tim=33239714604 WAIT #139664881111664: nam='direct path write' ela= 10632 file number=12 first dba=5588 block cnt=4 obj#=-1 tim=33240382051 WAIT #139664881111664: nam='direct path write' ela= 1165 file number=12 first dba=5592 block cnt=4 obj#=-1 tim=33240384262 WAIT #139664881111664: nam='direct path write' ela= 10993 file number=12 first dba=5596 block cnt=4 obj#=-1 tim=33241197061 WAIT #139664881111664: nam='direct path write' ela= 1073 file number=12 first dba=5601 block cnt=3 obj#=-1 tim=33241198622 WAIT #139664881111664: nam='direct path write' ela= 3750 file number=12 first dba=5604 block cnt=4 obj#=-1 tim=33241966299 WAIT #139664881111664: nam='direct path write' ela= 814 file number=12 first dba=5608 block cnt=4 obj#=-1 tim=33241967674 WAIT #139664881111664: nam='direct path write' ela= 11866 file number=12 first dba=5612 block cnt=4 obj#=-1 tim=33242958004 WAIT #139664881111664: nam='direct path write' ela= 850 file number=12 first dba=5617 block cnt=3 obj#=-1 tim=33242959491 WAIT #139664881111664: nam='direct path write' ela= 10146 file number=12 first dba=5620 block cnt=4 obj#=-1 tim=33243789090 WAIT #139664881111664: nam='direct path write' ela= 533 file number=12 first dba=5624 block cnt=4 obj#=-1 tim=33243791174 WAIT #139664881111664: nam='direct path write' ela= 10012 file number=12 first dba=5628 block cnt=4 obj#=-1 tim=33244658682 WAIT #139664881111664: nam='direct path write' ela= 873 file number=12 first dba=5905 block cnt=3 obj#=-1 tim=33244660281 WAIT #139664881111664: nam='direct path write' ela= 10403 file number=12 first dba=5908 block cnt=4 obj#=-1 tim=33246088698 WAIT #139664881111664: nam='direct path write' ela= 809 file number=12 first dba=5912 block cnt=4 obj#=-1 tim=33246090242 WAIT #139664881111664: nam='direct path write' ela= 10016 file number=12 first dba=5916 block cnt=4 obj#=-1 tim=33246835637 WAIT #139664881111664: nam='direct path write' ela= 1226 file number=12 first dba=1026 block cnt=2 obj#=-1 tim=33246837398 WAIT #139664881111664: nam='direct path write' ela= 732 file number=12 first dba=1028 block cnt=4 obj#=-1 tim=33246838812 WAIT #139664881111664: nam='direct path write' ela= 432 file number=12 first dba=1032 block cnt=4 obj#=-1 tim=33246839615 WAIT #139664881111664: nam='direct path write' ela= 555 file number=12 first dba=1036 block cnt=4 obj#=-1 tim=33246840557 WAIT #139664881111664: nam='direct path write' ela= 556 file number=12 first dba=1040 block cnt=4 obj#=-1 tim=33246841583 WAIT #139664881111664: nam='direct path write' ela= 600 file number=12 first dba=1044 block cnt=4 obj#=-1 tim=33246842577 WAIT #139664881111664: nam='direct path write' ela= 554 file number=12 first dba=1048 block cnt=4 obj#=-1 tim=33246843549 WAIT #139664881111664: nam='direct path write' ela= 557 file number=12 first dba=1052 block cnt=4 obj#=-1 tim=33246844525 WAIT #139664881111664: nam='direct path write' ela= 624 file number=12 first dba=1056 block cnt=4 obj#=-1 tim=33246845523 WAIT #139664881111664: nam='direct path write' ela= 403 file number=12 first dba=1060 block cnt=4 obj#=-1 tim=33246846276 WAIT #139664881111664: nam='direct path write' ela= 401 file number=12 first dba=1064 block cnt=4 obj#=-1 tim=33246847045 WAIT #139664881111664: nam='direct path write' ela= 695 file number=12 first dba=1068 block cnt=2 obj#=-1 tim=33246847933 |
第七步调用ctcmj_merge_journal开始进行merge操作。merge完成之后，修改数据字典切换表和索引，并删除老的索引和JOURNAL表
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SQL> select a.owner,a.object_name,object_type,b.object_id,b.data_object_id,a.status,c.header_file,c.header_block from 2 (select owner,index_name object_name,table_name,status from dba_indexes 3 union all 4 select owner,table_name,table_name,status from dba_tables) a,dba_objects b,dba_segments c 5 where a.owner=b.owner and a.object_name=b.object_name and a.owner=c.owner and a.object_name=c.segment_name and a.table_name='T1' and a.owner='TEST'; OWNER OBJECT_NAM OBJECT_TYPE OBJECT_ID DATA_OBJECT_ID STATUS HEADER_FILE HEADER_BLOCK ---------- ---------- ----------------------- ---------- -------------- -------- ----------- ------------ TEST T1 TABLE 73501 74093 VALID 12 186 TEST IDX_1 INDEX 73933 74099 VALID 12 146 ===================== PARSING IN CURSOR #139664876988896 len=35 dep=1 uid=0 oct=10 lid=0 tim=33563392811 hv=1691844912 ad='7f0644ed2d00' sqlid='546mf2jkdg09h' DROP INDEX "TEST"."IDX_1_SYS_73501" ===================== PARSING IN CURSOR #139664900111264 len=43 dep=1 uid=0 oct=12 lid=0 tim=33563629478 hv=1200399735 ad='7f0644ed2d00' sqlid='918qw5x3st9br' drop table "TEST"."SYS_JOURNAL_73501" purge |
可以看到切换后索引状态正常因为是基于新的segment创建的。最后再次调用kkzuRmtDrop函数删除 RMTAB_I表
|
1 2 3 |
===================== PARSING IN CURSOR #139664902206528 len=45 dep=1 uid=0 oct=12 lid=0 tim=34102565216 hv=4001287474 ad='7f0644ed2d00' sqlid='dq56ja7r7xm9k' drop table "TEST"."SYS_RMTAB$$_I73501" purge |
至此online move table过程结束。