普通表转分区表(交换分区、在线重定义)
将普通表转换成分区表有 4 种方法：
1. Export/import method
2. Insert with a subquery method
3. Partition exchange method
4. DBMS_REDEFINITION
下面我们来讨论：交换分区 Partition exchange method 与 在线重定义 DBMS_REDEFINITION
理论背景：
交换分区 Partition exchange method：对数据字典中分区和表的定义进行了修改，没有数据的修改或复制，效率最高。
适用于包含大数据量的表转到分区表中的一个分区的操作。尽量在闲时进行操作。
交换分区的操作步骤如下：
1. 创建分区表，假设有 2 个分区，P1，P2.
2. 创建表 A 存放 P1 规则的数据。
3. 创建表 B 存放 P2 规则的数据。
4. 用表 A 和 P1 分区交换。 把表 A 的数据放到到 P1 分区
5. 用表 B 和 p2 分区交换。 把表 B 的数据存放到 P2 分区。
1. 用户授权
SQL> grant EXECUTE_CATALOG_ROLE,CREATE ANY TABLE,ALTER ANY TABLE,DROP ANY TABLE,LOCK ANY TABLE,SELECT ANY TABLE to scott;
2. 创建分区表：
CREATE TABLE interval_p_dba (id NUMBER primary key, time DATE)
PARTITION BY RANGE (time)
INTERVAL ( NUMTOYMINTERVAL (1, 'MONTH') )
(PARTITION part1 VALUES LESS THAN (TO_DATE('2015-03-01', 'yyyy-mm-dd')),
PARTITION part2 VALUES LESS THAN (TO_DATE('2015-04-01', 'yyyy-mm-dd'))
);
3. 创建 2 个分别对应分区的基表与基表数据：
SQL> CREATE TABLE andy_p1 (id NUMBER primary key, time DATE);
SQL> insert into andy_p1 values(1,to_date('2015-02-02','yyyy-mm-dd'));
SQL> insert into andy_p1 values(2,to_date('2015-02-03','yyyy-mm-dd'));
SQL> CREATE TABLE andy_p2 (id NUMBER primary key, time DATE);
SQL> insert into andy_p2 values(3,to_date('2015-03-02','yyyy-mm-dd'));
SQL> insert into andy_p2 values(4,to_date('2015-03-03','yyyy-mm-dd'));
SQL> select count(*) from andy_p1;
COUNT(*)
----------
2
SQL> select count(*) from andy_p2;
COUNT(*)
----------
2
4. 将 2 个基表与 2 个分区进行交换：
SQL> alter table interval_p_dba exchange partition part1 with table andy_p1 ; [ 忽略非法，加上 without validation ]
Table altered.
SQL> alter table interval_p_dba exchange partition part2 with table andy_p2 ;
Table altered.
查询 2 个分区：
SQL> select count(*) from interval_p_dba partition(part1);
COUNT(*)
----------
2
SQL> select count(*) from interval_p_dba partition(part2);
COUNT(*)
----------
2
注意：数据和之前的基表一致。
查询原来的 2 个基表：
SQL> select count(*) from andy_p2;
COUNT(*)
----------
0
SQL> select count(*) from andy_p1;
COUNT(*)
----------
0
注意： 2 个基表的数据变成成 0。
至此 Partition exchange method 操作结束。
——————————————————————————————————————
理论背景：
线重定义 DBMS_REDEFINITION ：
在线重定义能保证数据的一致性，在大部分时间内，表都可以正常进行 DML
操作。只在切换的瞬间锁表，具有很高的可用性。这种方法具有很强的灵活性，
对各种不同的需要都能满足。而且，可以在切换前进行相应的授权并建立各种约
束，可以做到切换完成后不再需要任何额外的管理操作
在线重定义表具有以下功能：
（1）修改表的存储参数；
（2）将表转移到其他表空间；
（3）增加并行查询选项；
（4）增加或删除分区；
（5）重建表以减少碎片；
（6）将堆表改为索引组织表或相反的操作；
（7）增加或删除一个列。
使用在线重定义的一些限制条件：
（1） There must be enough space to hold two copies of the table.
（2） Primary key columns cannot be modified.
（3） Tables must have primary keys.
（4） Redefinition must be done within the same schema.
（5） New columns added cannot be made NOT NULL until after the redefinition
operation.
（6） Tables cannot contain LONGs, BFILEs or User Defined Types.
（7） Clustered tables cannot be redefined.
（8） Tables in the SYS or SYSTEM schema cannot be redefined.
（9） Tables with materialized view logs or materialized views defined on them
cannot be redefined.
（10） Horizontal sub setting of data cannot be performed during the redefinition.
在 Oracle 10.2.0.4 和 11.1.0.7 版本下，在线重定义可能会遇到如下 bug：
Bug 7007594 - ORA-600 [12261]
在线重定义的大致操作流程如下：
（1）创建基础表 A，如果存在，就不需要操作。
（2）创建临时的分区表 B。
（3）开始重定义，将基表 A 的数据导入临时分区表 B。
（4）结束重定义，此时在 DB 的 Name Directory 里，已经将 2 个表进行了
交换。即此时基表 A 成了分区表，我们创建的临时分区表 B 成了普通表。 此
时我们可以删除我们创建的临时表 B。它已经是普通表。
下面看一个示例：
1. 创建基本表和索引
sqlplus scott/oracle@10.100.25.13:1521/orcl
sql>
create table un_andy (
id number(10) primary key,
create_date date not null
);
sql> insert into un_andy select rownum, created from dba_objects;
sql> create index un_andy_ind on un_andy(create_date);
sql> commit;
-- 查询基表的索引与约束情况
SQL> set linesize 400
SQL> col COLUMN_NAME for a30
SQL> SELECT index_name, column_name, descend FROM user_ind_columns WHERE table_name = 'UN_ANDY';
INDEX_NAME COLUMN_NAME DESC
------------------------------ ------------------------------ ----
UN_ANDY_IND CREATE_DATE ASC
SYS_C0025748 ID ASC
SQL> select CONSTRAINT_NAME,COLUMN_NAME,TABLE_NAME from user_cons_columns where TABLE_NAME='UN_ANDY';
CONSTRAINT_NAME TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------ ------------------------------
SYS_C0025747 UN_ANDY CREATE_DATE
SYS_C0025748 UN_ANDY ID
2. 收集表的统计信息
sql> exec dbms_stats.gather_table_stats('scott', 'un_andy', cascade => true);
3. 创建临时分区表
SQL>
CREATE TABLE par_table (id NUMBER(10) primary key, create_date DATE not null)
PARTITION BY RANGE (create_date)
INTERVAL ( NUMTOYMINTERVAL (1, 'MONTH') )
(PARTITION part1 VALUES LESS THAN (TO_DATE('2015-03-01', 'yyyy-mm-dd')),
PARTITION part2 VALUES LESS THAN (TO_DATE('2015-04-01', 'yyyy-mm-dd'))
);
4. 进行重定义操作
4.1 检查重定义的合理性
SQL> exec dbms_redefinition.can_redef_table('scott', 'un_andy');
PL/SQL procedure successfully completed.
4.2 如果 4.1 没有问题，开始重定义，这个过程可能要等一会。
这里要注意：如果分区表和原表列名相同，可以用如下方式进行：
SQL>
BEGIN
DBMS_REDEFINITION.start_redef_table(
uname => 'SCOTT',
orig_table => 'un_andy',
int_table => 'par_table');
END;
/
如果分区表的列名和原表不一致，那么在开始重定义的时候，需要重新指定
映射关系：
SQL>
EXEC DBMS_REDEFINITION.START_REDEF_TABLE(
'SCOTT',
'un_andy',
'par_table',
'ID ID, create_date TIME', -- 在这里指定新的映射关系
DBMS_REDEFINITION.CONS_USE_PK);
这一步操作结束后，数据就已经同步到这个临时的分区表里来了。
4.3 同步新表 （ sync_interim_table的目的是为了缩短finish时锁定表的时间 ）
-- 模拟在线重定义过程中，基表依然进行 DML 操作。
SQL> select count(*) from un_andy;
COUNT(*)
----------
88770
SQL> select count(*) from par_table;
COUNT(*)
----------
88770
SQL> insert into un_andy values(333333,to_date('2015-03-02','yyyy-mm-dd'));
1 row created.
SQL> select count(*) from un_andy;
COUNT(*)
----------
88771
SQL> select count(*) from par_table;
COUNT(*)
----------
88770
-- 刷新
SQL>
BEGIN
dbms_redefinition.sync_interim_table(
uname => 'SCOTT',
orig_table => 'un_andy',
int_table => 'par_table');
END;
/
说明：执行 EXEC DBMS_REDEFINITION.START_REDEF_TABLE 之后系统会将旧表的数据刷新到新表，
同时生成一张名为 M$LOG_XXOLD 的物化视图。此后，对新表的更新都会记录在这个视图里，
选择执行一次或多次 DBMS_REDEFINITION.sync_interim_table 或者EXEC DBMS_REDEFINITION.finish_redef_table
后会自动将物化视图的记录更新到新表中。
-- 检查是否刷新，的确刷新了。
SQL> select count(*) from par_table;
COUNT(*)
----------
88771
4.4 创建索引，在线重定义只重定义数据，索引还需要单独建立。
sql> create index create_date_ind2 on par_table(create_date);
索引已创建。
4.5 收集新表的统计信息
sql> exec dbms_stats.gather_table_stats('scott', 'par_table', cascade => true);
4.6 结束重定义
SQL>
BEGIN
dbms_redefinition.finish_redef_table(
uname => 'SCOTT',
orig_table => 'un_andy',
int_table => 'par_table');
END;
/
结束重定义的意义：
基表 unpar_table 和临时分区表 par_table 进行了交换。 此时临时分区表
par_table 成了普通表，我们的基表 unpar_table 成了分区表。
我们在重定义的时候，基表 unpar_table 是可以进行 DML 操作的。 只有在 2
个表进行切换的时候会有短暂的锁表。
5. 删除临时表
SQL> DROP TABLE par_table;
表已删除。
6. 索引、约束、表重命名
SQL> ALTER INDEX create_date_ind2 RENAME TO un_andy_ind;
Index altered.
SQL> ALTER INDEX SYS_C0025750 RENAME TO SYS_C0025748;
Index altered.
SQL> alter table UN_ANDY rename constraint "SYS_C0025750" to SYS_C0025748;
Table altered.
SQL> alter table UN_ANDY rename constraint "SYS_C0025749" to SYS_C0025747;
Table altered.
SQL> alter table UN_ANDY rename to par_table;
Table altered.
7. 验证
sql> select partitioned from user_tables where table_name = 'UN_ANDY';
par
---
yes
sql> select partition_name from user_tab_partitions where table_name ='UN_ANDY';
partition_name
------------------------------
p1
p2
p3
p4
SQL> select count(*) from par_table;
COUNT(*)
----------
88771
