在线重定义表是从 oracle9i 开始提供的一个功能, 能在线完成对一个表结构或存储的重定义, 并且不影响当前应用的使用, 是 oracle 高可用性中的一个很有用的特性.
一, 介绍
「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, 构建测试数据创建测试表空间和用户:
- sqlplus / as sysdba
- create tablespace PAR;
- create user par identified by par;
- grant dba to par;
创建测试表:
- sqlplus par/par
- create table lucifer(
- id number(8) PRIMARY KEY,
- name varchar2(20) not null,
- par_date date)
- tablespace PAR;
- comment on table lucifer is 'lucifer 表';
- comment on column lucifer.name is '姓名';
- comment on column lucifer.par_date is '分区日期';
- create index id_name on lucifer(name) tablespace par;
插入测试数据:
- sqlplus par/par
- begin
- for i in 0 .. 24 loop
- insert into lucifer values
- (i,
- 'lcuifer_' || i,
- add_months(to_date('2021-1-1', 'yyyy-mm-dd'), i));
- end loop;
- commit;
- end;
- /
可以看到, 测试数据已经构建完成, 接下来开始实战操作.
2, 查看是否能够重定义
需提前确认表是否有主键, 表空间是否足够:
- sqlplus / as sysdba
- ## 查看主键
- select cu.* from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name = 'LUCIFER';
确认是否可以重定义, 没有主键用 rowid:
- sqlplus / as sysdba
- exec dbms_redefinition.can_redef_table('PAR', 'LUCIFER');
执行没有报错代表可以进行表的在线重定义.
3, 创建中间表 (分区表结构)
通过 PL/SQL 包一键生成分区表结构:
- sqlplus par/par
- BEGIN
- ctas_par(p_tab => 'lucifer',
- p_part_colum => 'par_date',
- p_part_nums => 24,
- p_tablespace => 'par');
- END;
- /
创建中间分区表 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;
如上, 唯一索引和约束不加, 会自动复制, 分区表结构的中间表已经生成.
4, 检查中间表是否开启行迁移
- select row_movement from dba_tables where table_name='LUCIFER' and owner='PAR';
- select row_movement from dba_tables where table_name='LUCIFER_PAR' and owner='PAR';
5, 收集表统计信息
为了确保数据准确, 开始前进行统计信息收集:
- sqlplus / as sysdba
- exec dbms_stats.gather_table_stats(ownname => 'PAR',tabname => 'LUCIFER',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE,degree => '8') ;
- exec dbms_stats.gather_table_stats(ownname => 'PAR',tabname => 'LUCIFER_PAR',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE,degree => '8') ;
6, 开始在线重定义
- sqlplus / as sysdba
- EXEC DBMS_REDEFINITION.START_REDEF_TABLE('PAR','LUCIFER','LUCIFER_PAR');
7, 复制表属性, 排除索引
选择自动复制表属性, 手动创建本地索引 (local):
优点: 只需要关注索引是否遗漏, 无需关注触发器, 权限, 约束等依赖.
缺点: 需要手动创建索引, 并且结束后手动 rename 索引.
- sqlplus par/par
- SET SERVEROUTPUT ON
- DECLARE
- l_errors NUMBER;
- BEGIN
- DBMS_REDEFINITION.copy_table_dependents(
- uname => USER,
- orig_table => 'LUCIFER',
- int_table => 'LUCIFER_PAR',
- copy_indexes => 0,
- copy_triggers => TRUE,
- copy_constraints => TRUE,
- copy_privileges => TRUE,
- ignore_errors => FALSE,
- num_errors => l_errors,
- copy_statistics => FALSE,
- copy_mvlog => FALSE);
- DBMS_OUTPUT.put_line('Errors=' || l_errors);
- END;
- /
执行过程没有任何报错, 代表正常.
8, 中间表创建本地索引
中间表 LUCIFER_PAR 创建索引:
create index ID_NAME_PAR on LUCIFER_PAR(NAME) tablespace PAR local parallel 8;
注意: 索引名称需要与原索引名称不一致.
9, 取消索引并行度
如果创建索引时, 开启并行创建, 则需要取消索引并行度:
- sqlplus / as sysdba
- select 'alter index'||owner||'.'||index_name||'noparallel;'
- from dba_indexes
- where table_name = 'LUCIFER_PAR' and owner= 'PAR';
10, 同步数据 (可以减少结束重定义过程的锁表时间)
- sqlplus / as sysdba
- BEGIN
- dbms_redefinition.sync_interim_table(
- uname => 'PAR',
- orig_table => 'LUCIFER',
- int_table => 'LUCIFER_PAR');
- END;
- /
注意: 这一步操作是为了在结束重定义的时候, 减少锁表的时间.
11, 收集中间表统计信息
为了下面同步数据做准备, 收集中间表统计信息:
- sqlplus / as sysdba
- exec dbms_stats.gather_table_stats(ownname => 'PAR',tabname => 'LUCIFER_PAR',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE,degree => '8') ;
12, 结束重定义 (结束重定义需要锁表, 具体时间根据表的大小决定)
- sqlplus / as sysdba
- BEGIN
- dbms_redefinition.finish_redef_table(
- uname => 'PAR',
- orig_table => 'LUCIFER',
- int_table => 'LUCIFER_PAR');
- END;
- /
13, 查看分区表是否已转换
- sqlplus par/par
- select owner,table_name,partitioned from user_tables where table_name in ('LUCIFER','LUCIFER_PAR');
如上, LUCIFER 表已经在线重定义为分区表结构.
14, 手动修改重命名索引
此时, 原表名的表已经转换为中间表, 需要先将原表的索引, rename 到其他名字, 本次是 BAK, 需要注意索引名称长度不能过长
- sqlplus / as sysdba
- ALTER index PAR.ID_NAME RENAME TO ID_NAME_BAK;
rename 新分区表索引, 由于新分区表的索引名称还是中间表的索引名称, 所以需要手动 rename:
- sqlplus / as sysdba
- ALTER index PAR.ID_NAME_PAR RENAME TO ID_NAME;
15, 查看是否存在无效索引
- sqlplus / as sysdba
- SELECT owner index_owner, index_name, index_type,'N/A' partition_name,status,table_name,tablespace_name,
- 'alter index'||owner||'.'||index_name||'rebuild;' rebuild_index
- FROM dba_indexes
- WHERE status = 'UNUSABLE'
- UNION ALL
- SELECT a.index_owner,a.index_name,b.index_type,a.partition_name,a.status,b.table_name,a.tablespace_name,
- 'alter index'||a.index_owner||'.'||a.index_name||'rebuild partition'||a.partition_name||';' rebuild_index
- FROM dba_ind_partitions a, dba_indexes b
- WHERE a.index_name = b.index_name
- AND a.index_owner = b.owner
- AND a.status = 'UNUSABLE'
- UNION ALL
- SELECT owner index_owner,a.index_name,b.index_type,'N/A' partition_name,a.status,b.table_name,NULL,
- 'alter index'||a.index_owner||'.'||a.index_name||'rebuild subpartition'||a.subpartition_name||';' rebuild_index
- FROM dba_ind_subpartitions a, dba_indexes b
- WHERE a.index_name = b.index_name
- AND a.index_owner = b.owner
- AND a.status = 'UNUSABLE';
16, 检查切换后是否开启 row_movement
- sqlplus / as sysdba
- select owner,table_name,row_movement from dba_tables where table_name in ('LUCIFER','LUCIFER_PAR') and owner='PAR';
17, 检查无效对象
- ## 无效对象编译
- sqlplus / as sysdba
- @?/rdbms/admin/utlrp.sql
- select 'alter'||object_type||''||owner||'.'||object_name||' compile;'
- from dba_objects t
- where t.status = 'INVALID' order by 1;
18, 收集统计信息
- sqlplus / as sysdba
- exec dbms_stats.gather_table_stats(ownname => 'PAR',tabname => 'LUCIFER',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE,degree => '8') ;
19, 插入测试数据
- sqlplus par/par
- begin
- for i in 100 .. 124 loop
- insert into lucifer values
- (i,
- 'lcuifer_' || i,
- add_months(to_date('2021-5-1', 'yyyy-mm-dd'), i));
- end loop;
- commit;
- end;
- /
20, 查询分区表数据分布
- sqlplus par/par
- SELECT COUNT(*) FROM LUCIFER;
- SELECT * FROM LUCIFER PARTITION(LUCIFER_P202101);
- SELECT * FROM LUCIFER PARTITION(LUCIFER_P202201);
- SELECT * FROM LUCIFER PARTITION(LUCIFER_MAX);
可以发现, 数据已经根据日期均匀分布在不同的子分区中.
至此, 在线重定义已经完成, 分区表已成功转换.
「参考 MOS 文档:」
How To Partition Existing Table Using DBMS_REDEFINITION (Doc ID 472449.1)
来源: http://database.51cto.com/art/202201/698654.htm