一、背景
近期我们有一个历史数据清理的需求，由于数据量巨大，通过delete的方式进行清理可能会造成归档日志产生过多、日志切换过于频繁、undo表空间使用率高等风险，因此我们决定将需要清理的表进行分区并通过drop partition或者truncate partition的方式进行首次清理，不仅可以更加快速的完成数据清理，也可以极大程度的回收空间。
二、分区方案
在Oracle 11g中，我们可以直接通过modify的方式进行：
ALTER TABLE TABLE_NAME MODIFY
PARTITION BY RANGE(PARTITION_COLUMN)
(
PARTITION P_20220401 VALUES LESS THAN('20220501'),
PARTITION P_20220501 VALUES LESS THAN('20220601'),
PARTITION P_20220601 VALUES LESS THAN('20220701')
.....
) update index;
但是该方式会对整张表进行锁表，在生产环境中，如果该表涉及实时交易，则会给该业务造成极大影响，因此在生产上基本上不会使用该方式进行分区。
针对11g的数据库在生产环境上，我们常用的分区方式是在线重定义，在线重定义的方式操作较为方便，可以理解为oracle给我们提供的半自动化的分区手段，我们只需要调用以下几个系统过程即可完成：
DBMS_REDEFINITION.CAN_REDEF_TABLE
DBMS_REDEFINITION.START_REDEF_TABLE
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS
DBMS_REDEFINITION.SYNC_INTERIM_TABLE
DBMS_REDEFINITION.FINISH_REDEF_TABLE
期间只在进行FINISH_REDEF_TABLE的时候，会有1-3秒的锁表，对业务的影响极小。因此，该方案是在oracle数据库的生产环境中进行表分区操作的极佳方案。
而在oracle数据库12c的版本及以后，引入了很多在线操作，比如move datafile online、split partition online，alter table partition online等。这相当于oracle又给我们提供了更多的自动化操作功能，我们仅通过一条命令就可以做到很多之前需要形成复杂方案的才能完成的工作，针对分区操作，我们则可以通过以下语句完成：
ALTER TABLE TABLE_NAME MODIFY
PARTITION BY RANGE(PARTITION_COLUMN)
(
PARTITION P_20220401 VALUES LESS THAN('20220501'),
PARTITION P_20220501 VALUES LESS THAN('20220601'),
PARTITION P_20220601 VALUES LESS THAN('20220701')
.....
) online update index
(
IDX1 GROBAL,
IDX2 LOCAL,
IDX3 LOCAL
);
当然，为了更加严谨，如果在生产环境中，使用该新特性，我们更关注的是这种方式到底对生产环境中是否有影响？影响有多大？接下来，我将用常用的方式进行测试并分析，在我们做了alter操作后，oracle到底做了什么？这些操作是否会影响我们的业务运行？
三、测试及分析
首先需要提出的仍然是分析方式，想要确认在线分区操作是否会对我们的业务有影响，在数据库中，最直观的就是对相关锁的观察，在执行操作过程中，哪些对象会获取什么样的锁，该锁会对我们业务中常用的dml，甚至ddl操作会有什么影响？我们可以通过以下方式进行准实时观察：
while(true)
do
sqlplus / as sysdba <<EOF
select a.object_id,
a.locked_mode,
a.session_id,
b.object_name,
b.subobject_name
from v\$locked_object a, dba_objects b
where a.object_id = b.object_id;
select /*+rule*/ type,id1,id2,lmode from v\$lock where type in('TX','TM');
set line 999
col object_name format a30
col subobject_name format a30
select a.object_id,a.locked_mode, a.session_id,b.object_name,b.subobject_name,C.TYPE,C.LMODE,d.sql_id from v\$locked_object a, dba_objects b,v\$lock c,v\$session d where a.object_id = b.object_id and c.ID1=a.object_id and c.type in('TM') and a.session_id=d.sid;
exit;
EOF
sleep 2
done
其次，想要更细致的观察该命令执行后，数据库中会有哪些操作，则我们直接使用10046的event即可：
alter session set events '10046 trace name context forever ,level 12';
ALTER TABLE TABLE_NAME MODIFY
PARTITION BY RANGE(PARTITION_COLUMN)
(
PARTITION P_20220401 VALUES LESS THAN('20220501'),
PARTITION P_20220501 VALUES LESS THAN('20220601'),
PARTITION P_20220601 VALUES LESS THAN('20220701')
.....
) online update index
(
IDX1 GROBAL,
IDX2 LOCAL,
IDX3 LOCAL
);
alter session set events '10046 trace name context off';
--查看生成文件位置
select values from v$diag_info;
--格式化文件
tkprof xxxxxx.trc /tmp/xxxxxx.txt
确定方法后，我们则可以开始进行实际的测试,对alter table过程中产生的锁信息进行查看，可以发现如下结果：
TY ID1 ID2 LMODE
-- ---------- ---------- ----------
TM 177064 0 6
TM 177067 0 4
TM 177198 0 6
TM 18 0 3
TX 393245 55145 6
TM 75817 0 3
TX 393232 54992 6
OBJECT_ID LOCKED_MODE SESSION_ID OBJECT_NAME SUBOBJECT_NAME TY LMODE SQL_ID
---------- ----------- ---------- ------------------------------ ------------------------------ -- ---------- -------------
18 3 8 OBJ$ TM 3 1p91gwpyy3dys
75817 3 TABLE_ABCD TM 3 1p91gwpyy3dys
177067 4 8 SYS_MODPART_75817 TM 4 1p91gwpyy3dys
177198 6 8 SYS_RMTAB$$_I75817 TM 6 1p91gwpyy3dys
177064 6 8 SYS_JOURNAL_75817 TM 6 1p91gwpyy3dys
从锁查询结果上进行查看，可以发现在我们做的分区表TABLE_ABCD上oracle加了一个TM为3的锁，该锁为可以理解为一个表级共享锁，因此不会阻碍该表的DML操作。而在一些看起来像是系统对象上架了一些TM为4及TM为6的锁(SYS_MODPART_75817、SYS_RMTAB$$_I75817、SYS_JOURNAL_75817)，而1p91gwpyy3dys的sql及为我们执行的alter table modify partition online操作。所以，从这里其实也看的出，alter table modify partition online的操作并不会对我们操作的T_BUET_BUSINESSEVEN表加一些比较重的独占锁，不会影响我们的dml业务。
进一步从10046中看，该操作在数据库中做了什么操作？
SQL ID: 5ffy417qduycx Plan Hash: 0
LOCK TABLE "ABCD"."TABLE_ABCD" IN ROW EXCLUSIVE MODE NOWAIT
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 113 (recursive depth: 1)
********************************************************************************
从这里可以看到oracle对我们操作的表加了一个row exclusive mode的锁，该锁及为row exclusive 3级锁，此时是允许其他事务在相同表中并行查询、插入、更新、删除或锁定行的操作，因此不会阻塞业务，而这也是我们在锁查询中看到的针对"ABCD"."TABLE_ABCD"表的TM=3的锁。
********************************************************************************
alter table upps.TABLE_ABCD modify
PARTITION BY RANGE(WORKDATE)
(
PARTITION P_20220401 VALUES LESS THAN('20220501'),
PARTITION P_20220501 VALUES LESS THAN('20220601'),
PARTITION P_20220601 VALUES LESS THAN('20220701'),
PARTITION P_20220701 VALUES LESS THAN('20220801'),
PARTITION P_20220801 VALUES LESS THAN('20220901'),
PARTITION P_20220901 VALUES LESS THAN('20221001'),
.......
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.02 0 36 0 0
Execute 1 67.59 128.11 669957 680773 804597 9043679
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 67.60 128.13 669957 680809 804597 9043679
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 113
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD AS SELECT TABLE_ABCD (cr=679930 pr=669929 pw=704193 time=126067284 us starts=1)
9043679 9043679 9043679 OPTIMIZER STATISTICS GATHERING (cr=671036 pr=669913 pw=0 time=145506000 us starts=1 cost=182006 size=4766018833 card=9043679)
9043679 9043679 9043679 TABLE ACCESS FULL TABLE_ABCD (cr=671036 pr=669913 pw=0 time=118619532 us starts=1 cost=182006 size=4766018833 card=9043679)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
PGA memory operation 118 0.02 0.03
db file scattered read 5265 0.36 53.84
Disk file operations I/O 8 0.00 0.00
direct path write 5659 0.01 2.47
log buffer space 157 0.62 7.55
log file switch completion 6 0.03 0.10
db file sequential read 47 0.03 0.31
direct path sync 20 0.01 0.07
local write wait 256 0.04 0.49
reliable message 20 0.00 0.00
enq: RO - fast object reuse 8 0.15 0.24
enq: CR - block range reuse ckpt 10 0.02 0.08
library cache: mutex X 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 42.34 42.34
********************************************************************************
可以看到，此时oracl执行了alter table modify partition online的命令，整个过程花费了128s。
接下来在10046里面又看到了一个create语句：
SQL ID: 7qqm19zf2qk36 Plan Hash: 0
create table "UPPS"."SYS_JOURNAL_75817" (rid rowid, opcode char(1), primary
key(rid)) organization index tablespace "TS_UPPS_TAB"
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 1 3 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 1 3 0
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 113 (recursive depth: 1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
asynch descriptor resize 1 0.00 0.00
Disk file operations I/O 1 0.00 0.00
PGA memory operation 2 0.00 0.00
该语句创建了一个SYS_JOURNAL_75817表，这个表也就是我们在锁视图中查看的其中一个被加了TM=6的表，不知道该表具体做什么用途，但是从字段来看，该表作用看起来像是对我们的数据操作进行了一个日志记录。
后续该表也被创建了一个索引：
SQL ID: 9x94f4uu5801y Plan Hash: 3329425251
CREATE UNIQUE INDEX "UPPS"."SYS_IOT_TOP_177064" on
"UPPS"."SYS_JOURNAL_75817"("RID") INDEX ONLY TOPLEVEL TABLESPACE
"TS_UPPS_TAB" STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT) NOPARALLEL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 1 73 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 1 73 0
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 113 (recursive depth: 2)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 INDEX BUILD UNIQUE SYS_IOT_TOP_177064 (cr=0 pr=0 pw=0 time=31 us starts=1)(object id 0)
0 0 0 SORT CREATE INDEX (cr=0 pr=0 pw=0 time=24 us starts=1 cost=0 size=0 card=0)
0 0 0 TABLE ACCESS FULL SYS_JOURNAL_75817 (cr=0 pr=0 pw=0 time=20 us starts=1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
index (re)build lock or pin object 3 0.00 0.00
Disk file operations I/O 4 0.00 0.00
接着可以发现，oracle又创建了一个SYS_RMTAB$$_H75817表：
SQL ID: 1vymvwf4z888y Plan Hash: 0
create table "UPPS"."SYS_RMTAB$$_H75817" ( src_rowid rowid not null ,
tgt_rowid rowid not null) segment creation immediate nologging tablespace
"TS_UPPS_TAB" rowid_mapping_table
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 2 52 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 2 52 0
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 113 (recursive depth: 1)
和上表一样，从字段上来看，该表则是记录了源表的rowid和目标表的rowid，作用应该是对数据的rowid进行了映射记录。
接下来，oracle又在上面表的基础上又创建了一个表SYS_RMTAB$$_I75817：
********************************************************************************
SQL ID: 68j6ykw3ys6c8 Plan Hash: 2100153527
create table "UPPS"."SYS_RMTAB$$_I75817" (src_rowid, tgt_rowid, constraint
pk_SYS_RMTAB$$_I75817 primary key (src_rowid)) segment creation immediate
organization index tablespace "TS_UPPS_TAB" noparallel nologging
rowid_mapping_table as (select src_rowid, tgt_rowid from
"UPPS"."SYS_RMTAB$$_H75817")
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.06 0 0 0 0
Execute 1 0.00 0.00 0 1 4 9043679
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.07 0 1 4 9043679
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 113 (recursive depth: 1)
********************************************************************************
该表则是在锁视图中看到的另一张TM=6的表，且从rows列中，可以看到该表中已经有了9043679条数据(这些数据是在创建表前被插入到SYS_RMTAB$$_H75817表中的)。
之后SYS_RMTAB$$_H75817表被删除(数据已经到UPPS"."SYS_RMTAB$$_I75817)中，这里有个疑问，既然这张H表最终的内容都要放到I表里面，为什么还要创建一个H表来做中转
********************************************************************************
SQL ID: gd2bdvp94368v Plan Hash: 0
drop table "UPPS"."SYS_RMTAB$$_H75817" purge
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.03 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.02 0.04 0 0 0 0
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 113 (recursive depth: 1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Compression analysis 12 0.00 0.00
********************************************************************************
继续往下，则可以看到以下sql：
********************************************************************************
SQL ID: 1gzft87ajsv21 Plan Hash: 1283867455
CREATE INDEX "UPPS"."I_SYS_177067_IDX_BUSINESSEVENT_1" ON
"UPPS"."SYS_MODPART_75817"("MSGID") PCTFREE 10 INITRANS 2 STORAGE( INITIAL
65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) LOCAL
NOPARALLEL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.04 0 160 15 0
Execute 1 17.74 72.52 670573 672305 54028 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 17.75 72.56 670573 672465 54043 0
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 113 (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
130 130 130 PARTITION RANGE ALL PARTITION: 1 130 (cr=672296 pr=670573 pw=25196 time=349271339 us starts=1)
130 130 130 INDEX BUILD NON UNIQUE (LOCAL) I_SYS_177067_IDX_BUSINESSEVENT_1 PARTITION: 1 130 (cr=672296 pr=670573 pw=25196 time=72398460 us starts=130)(object id 0)
9043679 9043679 9043679 SORT CREATE INDEX (cr=671128 pr=670573 pw=0 time=67018509 us starts=130)
9043679 9043679 9043679 TABLE ACCESS FULL SYS_MODPART_75817 PARTITION: 1 130 (cr=671128 pr=670573 pw=0 time=54989506 us starts=130 cost=184165 size=2866426628 card=55123589)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
index (re)build lock or pin object 4 0.00 0.00
db file scattered read 5504 0.40 55.86
direct path write 6520 0.03 3.39
PGA memory operation 1 0.00 0.00
********************************************************************************
.....
即在表SYS_MODPART_75817上创建索引,表SYS_MODPART_75817看起来像是一个中间表，而它也是我们一直看到的tm=6的表。
接着在进行了一系列对基表及相关物化视图的操作后，数据库又做了如下操作：
********************************************************************************
SQL ID: 20dd3g7h4zjw9 Plan Hash: 0
DROP TABLE "UPPS"."SYS_MODPART_75817" PURGE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.01 0 0 1 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.01 0 0 1 0
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 113 (recursive depth: 1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Compression analysis 12 0.00 0.00
********************************************************************************
此时，可以看到他已经把SYS_MODPART_75817删除掉了(此时，应该是完成了在表定义上和原表的交换操作)。
后续的操作，则是在基表中对一些记录信息进行删除和清理。
四、结论
Oracle 12C后的在线分区操作可以认为是在oracle在进行表分区时提供给运维人员的一个全自动化工具。我们只需要通过一条SQL语句即可实现原来通过在线重定义的方式才能完成的操作，大大提升了分区表创建及维护的效率。经过分析，它实现的原理也类似于我们手动进行在线重定义，通过一些中间表、物化视图、定义转换等操作来完成。从锁视图及10046的执行情况来看，针对我们需要进行分区的原表并没有非常重的锁表操作。因此，该操作不会影响我们的正常交易业务，是一种快速稳定的操作online分区方式。
最后修改时间：2023-06-13 09:27:04
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
