作者 |Lucifer
来源 | Lucifer三思而后行(ID:Lucifer-940622)
一、介绍
- 「DBMS_REDEFINITION(在线重定义):」
- 「支持的数据库版本」:Oracle Database - Enterprise Edition - Version 9.2.0.4 and later
在线重定义是通过 「物化视图」 实现的。
「使用在线重定义的一些限制条件」:
- 必须有足够的表空间来容纳表的两倍数据量。
- 主键列不能被修改。
- 表必须有主键。
- 必须在同一个用户下进行在线重定义。
- SYS和SYSTEM用户下的表无法进行在线重定义。
- 在线重定义无法采用nologging。
- 如果中间表有新增列,则不能有NOT NULL约束
「DBMS_REDEFINITION包:」
- ABSORT_REDEF_TABLE:清理重定义的错误和中止重定义;
- CAN_REDEF_TABLE:检查表是否可以进行重定义,存储过程执行成功代表可以进行重定义;
- COPY_TABLE_DEPENDENTS:同步索引和依赖的对象(包括索引、约束、触发器、权限等);
- FINISH_REDEF_TABLE:完成在线重定义;
- REGISTER_DEPENDENTS_OBJECTS:注册依赖的对象,如索引、约束、触发器等;
- START_REDEF_TABLE:开始在线重定义;
- SYNC_INTERIM_TABLE:增量同步数据;
UNREGISTER_DEPENDENT_OBJECT:不注册依赖的对象,如索引、约束、触发器等;
二、实战
1、构建测试数据
创建测试表空间和用户:
创建测试表:

插入测试数据:
可以看到,测试数据已经构建完成,接下来开始实战操作。
2、查看是否能够重定义
需提前确认表是否有主键,表空间是否足够:
确认是否可以重定义,没有主键用 rowid:
执行没有报错代表可以进行表的在线重定义。
3、创建中间表(分区表结构)
通过PL/SQL包一键生成分区表结构:

创建中间分区表 lucifer_par:
create table lucifer_par
(
id NUMBER(8),
name VARCHAR2(20),
par_date DATE
)
partition BY RANGE(par_date)(
partition lucifer_P202101 values less than (TO_DATE(' 2021-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202102 values less than (TO_DATE(' 2021-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202103 values less than (TO_DATE(' 2021-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202104 values less than (TO_DATE(' 2021-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202105 values less than (TO_DATE(' 2021-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202106 values less than (TO_DATE(' 2021-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202107 values less than (TO_DATE(' 2021-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202108 values less than (TO_DATE(' 2021-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202109 values less than (TO_DATE(' 2021-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202110 values less than (TO_DATE(' 2021-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202111 values less than (TO_DATE(' 2021-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202112 values less than (TO_DATE(' 2022-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202201 values less than (TO_DATE(' 2022-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202202 values less than (TO_DATE(' 2022-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202203 values less than (TO_DATE(' 2022-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202204 values less than (TO_DATE(' 2022-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202205 values less than (TO_DATE(' 2022-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202206 values less than (TO_DATE(' 2022-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202207 values less than (TO_DATE(' 2022-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202208 values less than (TO_DATE(' 2022-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202209 values less than (TO_DATE(' 2022-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202210 values less than (TO_DATE(' 2022-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202211 values less than (TO_DATE(' 2022-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202212 values less than (TO_DATE(' 2023-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_MAX values less than (maxvalue) tablespace par)
enable row movement
tablespace par;
- 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.
如上,唯一索引和约束不加,会自动复制,分区表结构的中间表已经生成。
4、检查中间表是否开启行迁移

5、收集表统计信息
为了确保数据准确,开始前进行统计信息收集:

6、开始在线重定义

7、复制表属性,排除索引
选择自动复制表属性,手动创建本地索引(local):
- 优点:只需要关注索引是否遗漏,无需关注触发器,权限,约束等依赖。
- 缺点:需要手动创建索引,并且结束后手动rename索引。
执行过程没有任何报错,代表正常。
8、中间表创建本地索引
中间表LUCIFER_PAR创建索引:
📢 注意:索引名称需要与原索引名称不一致。
9、取消索引并行度
如果创建索引时,开启并行创建,则需要取消索引并行度:

10、同步数据(可以减少结束重定义过程的锁表时间)
📢 注意: 这一步操作是为了在结束重定义的时候,减少锁表的时间。
11、收集中间表统计信息
为了下面同步数据做准备,收集中间表统计信息:

12、结束重定义(结束重定义需要锁表,具体时间根据表的大小决定)

13、查看分区表是否已转换

如上,LUCIFER表已经在线重定义为分区表结构。
14、手动修改重命名索引
此时,原表名的表已经转换为中间表,需要先将原表的索引,rename到其他名字,本次是BAK,需要注意索引名称长度不能过长

rename新分区表索引,由于新分区表的索引名称还是中间表的索引名称,所以需要手动rename:

15、查看是否存在无效索引

16、检查切换后是否开启row_movement

17、检查无效对象

18、收集统计信息

19、插入测试数据

20、查询分区表数据分布
可以发现,数据已经根据日期均匀分布在不同的子分区中。
至此,在线重定义已经完成,分区表已成功转换。
「参考MOS文档:」
- How To Partition Existing Table Using DBMS_REDEFINITION (Doc ID 472449.1)