一、介绍

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：不注册依赖的对象，如索引、约束、触发器等；

二、实战

安装
测试环境
可以使用博主编写的 Oracle 一键安装
脚本
，同时支持单机和 RAC 集群模式！

开源项目：Install Oracle Database By Scripts！

更多更详细的脚本使用方式可以订阅专栏：Oracle一键安装脚本。

1、构建 测试数据

创建测试表空间和用户：

1
2
3
4
5
6
7
8
9
10
11
sqlplus / as sysdba
create tablespace PAR;
create user par identified by par;
grant dba to par;

AI写代码bash

* 1
* 2
* 3
* 4

创建测试表：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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;

AI写代码bash

* 1
* 2
* 3
* 4
* 5
* 6
* 7
* 8
* 9
* 10

插入测试数据：

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
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;
/

AI写代码bash

* 1
* 2
* 3
* 4
* 5
* 6
* 7
* 8
* 9
* 10
* 11

在这里插入图片描述
可以看到，测试数据已经构建完成，接下来开始实战操作。

2、查看是否能够重定义

需提前确认表是否有主键，表空间是否足够：

1
2
3
4
5
6
7
8
9
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';

AI写代码bash

* 1
* 2
* 3

确认是否可以重定义，没有主键用 rowid：

1
2
3
4
5
6
7
sqlplus / as sysdba
exec dbms_redefinition.can_redef_table('PAR', 'LUCIFER');

AI写代码bash

* 1
* 2

执行没有报错代表可以进行表的在线重定义。

3、创建中间表（分区表结构）

分区表脚本使用和获取方式可以参考文章：

Oracle 通过脚本一键生成按月分区表

通过PL/SQL包一键生成分区表结构：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
sqlplus par/par
BEGIN
  ctas_par(p_tab        => 'lucifer',
           p_part_colum => 'par_date',
           p_part_nums  => 24,
           p_tablespace => 'par');
END;
/

AI写代码bash

* 1
* 2
* 3
* 4
* 5
* 6
* 7
* 8

在这里插入图片描述
📢 注意： PL/SQL包可参考：Oracle普通表按月转分区表，通过PLSQL包一键生成分区表

创建中间分区表 lucifer_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
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
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;

AI写代码bash

* 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、检查中间表是否开启行迁移

1
2
3
4
5
6
7
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';

AI写代码bash

* 1
* 2

5、收集表统计信息

为了确保数据准确，开始前进行统计信息收集：

1
2
3
4
5
6
7
8
9
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') ;

AI写代码bash

* 1
* 2
* 3

在这里插入图片描述

6、开始在线重定义

1
2
3
4
5
6
7
sqlplus / as sysdba
EXEC DBMS_REDEFINITION.START_REDEF_TABLE('PAR','LUCIFER','LUCIFER_PAR');

AI写代码bash

* 1
* 2

在这里插入图片描述

7、复制表属性，排除索引

选择自动复制表属性，手动创建本地索引（local）：

  • 优点：只需要关注索引是否遗漏，无需关注触发器，权限，约束等依赖。
  • 缺点：需要手动创建索引，并且结束后手动rename索引。
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
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;
/

AI写代码bash

* 1
* 2
* 3
* 4
* 5
* 6
* 7
* 8
* 9
* 10
* 11
* 12
* 13
* 14
* 15
* 16
* 17
* 18
* 19
* 20
* 21

在这里插入图片描述
执行过程没有任何报错，代表正常。

8、中间表创建本地索引

中间表LUCIFER_PAR创建索引：

1
2
3
4
5
create index ID_NAME_PAR on LUCIFER_PAR(NAME) tablespace PAR local parallel 8;

AI写代码bash

* 1

注意：索引名称需要与原索引名称不一致。

9、取消索引并行度

如果创建索引时，开启并行创建，则需要取消索引并行度：

1
2
3
4
5
6
7
8
9
10
11
sqlplus / as sysdba
select 'alter index '||owner||'.'||index_name||' noparallel;'
from dba_indexes 
where table_name = 'LUCIFER_PAR' and owner= 'PAR';

AI写代码bash

* 1
* 2
* 3
* 4

10、同步数据（可以减少结束重定义过程的锁表时间）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
sqlplus / as sysdba
BEGIN
dbms_redefinition.sync_interim_table(
uname => 'PAR',
orig_table => 'LUCIFER',
int_table => 'LUCIFER_PAR');
END;
/

AI写代码bash

* 1
* 2
* 3
* 4
* 5
* 6
* 7
* 8

📢 注意： 这一步操作是为了在结束重定义的时候，减少锁表的时间。

11、收集中间表统计信息

为了下面同步数据做准备，收集中间表统计信息：

1
2
3
4
5
6
7
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') ;

AI写代码bash

* 1
* 2

在这里插入图片描述

12、结束重定义（结束重定义需要锁表，具体时间根据表的大小决定）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
sqlplus / as sysdba
BEGIN
dbms_redefinition.finish_redef_table(
uname => 'PAR',
orig_table => 'LUCIFER',
int_table => 'LUCIFER_PAR');
END;
/

AI写代码bash

* 1
* 2
* 3
* 4
* 5
* 6
* 7
* 8

在这里插入图片描述

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

1
2
3
4
5
6
7
sqlplus par/par
select owner,table_name,partitioned from user_tables where table_name in ('LUCIFER','LUCIFER_PAR');

AI写代码bash

* 1
* 2

在这里插入图片描述
如上，LUCIFER表已经在线重定义为分区表结构。

14、手动修改重命名索引

此时，原表名的表已经转换为中间表，需要先将原表的索引，rename到其他名字，本次是BAK，需要注意索引名称长度不能过长

1
2
3
4
5
6
7
sqlplus / as sysdba
ALTER index PAR.ID_NAME RENAME TO ID_NAME_BAK;

AI写代码bash

* 1
* 2

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

1
2
3
4
5
6
7
sqlplus / as sysdba
ALTER index PAR.ID_NAME_PAR RENAME TO ID_NAME;

AI写代码bash

* 1
* 2

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

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
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';

AI写代码bash

* 1
* 2
* 3
* 4
* 5
* 6
* 7
* 8
* 9
* 10
* 11
* 12
* 13
* 14
* 15
* 16
* 17
* 18
* 19

在这里插入图片描述

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

1
2
3
4
5
6
7
sqlplus / as sysdba
select owner,table_name,row_movement from dba_tables where table_name in ('LUCIFER','LUCIFER_PAR') and owner='PAR';

AI写代码bash

* 1
* 2

在这里插入图片描述

17、检查无效对象

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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;

AI写代码bash

* 1
* 2
* 3
* 4
* 5
* 6
* 7

在这里插入图片描述

18、收集统计信息

1
2
3
4
5
6
7
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') ;

AI写代码bash

* 1
* 2

19、插入测试数据

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
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;
/

AI写代码bash

* 1
* 2
* 3
* 4
* 5
* 6
* 7
* 8
* 9
* 10
* 11

20、查询分区表数据分布

1
2
3
4
5
6
7
8
9
10
11
12
13
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);

AI写代码bash

* 1
* 2
* 3
* 4
* 5

在这里插入图片描述
在这里插入图片描述
可以发现，数据已经根据日期均匀分布在不同的子分区中。至此，在线重定义已经完成，分区表已成功转换。

参考MOS文档：

  • How To Partition Existing Table Using DBMS_REDEFINITION (Doc ID 472449.1)