非分区表转化成分区表(一)
非分区表转化成分区表(二)
方法三：Oracle9i以上版本，利用在线重定义功能
1、检查下这张表是否可以在线重定义，无报错表示可以，报错会给出错误信息
CAN_REDEF_TABLE Procedure
This procedure determines if a given table can be redefined online. This is the first step of the online redefinition process.
If the table is not a candidate for online redefinition, an error message is raised.
Syntax
DBMS_REDEFINITION.CAN_REDEF_TABLE (
uname IN VARCHAR2,
tname IN VARCHAR2,
options_flag IN PLS_INTEGER := 1,
part_name IN VARCHAR2 := NULL);
Parameters
Table 95-4 CAN_REDEF_TABLE Procedure Parameters
Parameter Description
uname
The schema name of the table
tname
The name of the table to be re-organized
options_flag
Indicates the type of redefinition method to use.
If dbms_redefinition.cons_use_pk, the redefinition is done using primary keys or pseudo-primary keys
(unique keys with all component columns having NOT NULL constraints). The default method of redefinition is using primary keys.
If dbms_redefinition.cons_use_rowid, the redefinition is done using rowids.
part_name
The name of the partition being redefined. If redefining only a single partition of a table, specify the partition name in this parameter. NULL implies the entire table is being redefined.
HCN@hcndb>
HCN@hcndb>exec dbms_redefinition.can_redef_table('hcn', 'test1');
BEGIN dbms_redefinition.can_redef_table('hcn', 'test1'); END;
*
ERROR at line 1:
ORA-12089: cannot online redefine table "HCN"."TEST1" with no primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 137
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1479
ORA-06512: at line 1
HCN@hcndb>
HCN@hcndb>
提示没有主键
解决方法：
方法一：创建主键
alter table test1 add constraint pk_test_id primary key (object_id);
方法二：使用rowid来实现
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(user, 'T2', DBMS_REDEFINITION.cons_use_rowid);
先使用方法一：
HCN@hcndb>
HCN@hcndb>alter table test1 add constraint pk_test_id primary key (object_id);
Table altered.
HCN@hcndb>exec dbms_redefinition.can_redef_table('hcn', 'test1');
PL/SQL procedure successfully completed.
HCN@hcndb>
2、建立在线重定义需要的中间表，表结构就是要将原测试表重定义成什么样子，这里建立的是按全宗号分区的分区表
CREATE TABLE test_temp PARTITION BY RANGE (object_id)
(PARTITION P1 VALUES LESS THAN (10000),
PARTITION P2 VALUES LESS THAN (20000),
PARTITION P3 VALUES LESS THAN (30000),
PARTITION P4 VALUES LESS THAN (40000),
PARTITION P5 VALUES LESS THAN (MAXVALUE))
as select * from test1 where 1=0;
HCN@hcndb>
HCN@hcndb>CREATE TABLE test_temp PARTITION BY RANGE (object_id)
2 (PARTITION P1 VALUES LESS THAN (10000),
3 PARTITION P2 VALUES LESS THAN (20000),
4 PARTITION P3 VALUES LESS THAN (30000),
5 PARTITION P4 VALUES LESS THAN (40000),
6 PARTITION P5 VALUES LESS THAN (MAXVALUE))
7 as select * from test1 where 1=0;
Table created.
HCN@hcndb>
3、启动在线重定义
exec dbms_redefinition.start_redef_table('hcn', 'test1', 'test_temp');
这里dbms_redefinition包的start_redef_table模块有3个参数，
分别是SCHEMA名字、原表的名字、中间表的名字。
HCN@hcndb>
HCN@hcndb>exec dbms_redefinition.start_redef_table('hcn', 'test1', 'test_temp');
PL/SQL procedure successfully completed.
HCN@hcndb>
HCN@hcndb>select count(*) from test_temp;
COUNT(*)
----------
50642
HCN@hcndb>
4、由于在生成系统中，在线重定义的过程中原数据表可能会发生数据改变，从原表中删除数据模拟数据改变。
HCN@hcndb>
HCN@hcndb>delete test1 where object_id=50000;
1 row deleted.
HCN@hcndb>
HCN@hcndb>commit;
Commit complete.
HCN@hcndb>
HCN@hcndb>
HCN@hcndb>select count(*) from test_temp;
COUNT(*)
----------
50642
HCN@hcndb>
HCN@hcndb>select count(*) from test1;
COUNT(*)
----------
50641
HCN@hcndb>
中间表的数据没有一起更新
5、使用dbms_redefinition包的sync_interim_table模块刷新数据后，中间表也可以看到数据更改。（并非一定要做这步）
exec dbms_redefinition.sync_interim_table('hcn', 'test1', 'test_temp');
HCN@hcndb>
HCN@hcndb>exec dbms_redefinition.sync_interim_table('hcn', 'test1', 'test_temp');
PL/SQL procedure successfully completed.
HCN@hcndb>
HCN@hcndb>select count(*) from test_temp;
COUNT(*)
----------
50641
HCN@hcndb>
HCN@hcndb>select count(*) from test1;
COUNT(*)
----------
50641
HCN@hcndb>
HCN@hcndb>
6、结束在线重定义：
exec dbms_redefinition.finish_redef_table('hcn', 'test1', 'test_temp');
HCN@hcndb>
HCN@hcndb>exec dbms_redefinition.finish_redef_table('hcn', 'test1', 'test_temp');
PL/SQL procedure successfully completed.
HCN@hcndb>
HCN@hcndb>
7、验证数据
HCN@hcndb>
HCN@hcndb>
HCN@hcndb>select count(*) from test1;
COUNT(*)
----------
50640
HCN@hcndb>select count(*) from test_temp;
COUNT(*)
----------
50640
HCN@hcndb>
HCN@hcndb>
8、查看test1的表结构
select table_name, partition_name from user_tab_partitions where
table_name = 'TEST1';
HCN@hcndb>
HCN@hcndb>set lines 200
HCN@hcndb>set pages 200
HCN@hcndb>col table_owner for a20
HCN@hcndb>col table_name for a20
HCN@hcndb>col partition_name for a20
HCN@hcndb>
HCN@hcndb>select TABLE_OWNER,
2 TABLE_NAME,
3 PARTITION_NAME,
4 to_char(LAST_ANALYZED, 'yyyy-mm-dd hh24:mi:ss') LAST_ANALYZED
5 from dba_tab_partitions
6 where table_name = 'TEST1';
TABLE_OWNER TABLE_NAME PARTITION_NAME LAST_ANALYZED
-------------------- -------------------- -------------------- -------------------
HCN TEST1 P1 2013-04-09 22:00:10
HCN TEST1 P2 2013-04-09 22:00:10
HCN TEST1 P3 2013-04-09 22:00:10
HCN TEST1 P4 2013-04-09 22:00:10
HCN TEST1 P5 2013-04-09 22:00:10
HCN@hcndb>
HCN@hcndb>
---还自动做了表分析
HCN@hcndb>
HCN@hcndb>select t.owner,
2 t.table_name,
3 t.status,
4 to_char(t.last_analyzed, 'yyyy-mm-dd hh24:mi:ss') last_analyzed
5 from dba_tables t
6 where t.table_name = 'TEST1';
OWNER TABLE_NAME STATUS LAST_ANALYZED
------------------------------ -------------------- -------- -------------------
HCN TEST1 VALID 2013-04-09 22:00:04
HCN@hcndb>
HCN@hcndb>
HCN@hcndb>
HCN@hcndb>select OWNER, TABLE_NAME, PARTITIONING_TYPE, PARTITION_COUNT
2 from dba_part_tables
3 where table_name = 'TEST1'
4 /
OWNER TABLE_NAME PARTITI PARTITION_COUNT
------------------------------ -------------------- ------- ---------------
HCN TEST1 RANGE 5
HCN@hcndb>
HCN@hcndb>COL COLUMN_NAME FOR A30
HCN@hcndb>select OWNER, TABLE_NAME, PARTITIONING_TYPE, PARTITION_COUNT
2 from dba_part_tables
3 where table_name = 'TEST1'
OWNER NAME OBJEC COLUMN_NAME COLUMN_POSITION
------------------------------ ------------------------------ ----- ------------------------------ ---------------
HCN TEST1 TABLE OBJECT_ID 1
HCN@hcndb>
9、在线重定义后，中间表已经没有意义，删掉中间表
drop table test_temp;
HCN@hcndb>
HCN@hcndb>drop table test_temp;
Table dropped.
HCN@hcndb>
方法二：使用rowid来实现
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(user, 'test1', DBMS_REDEFINITION.cons_use_rowid);
1、先重建实验环境中的表
HCN@hcndb>
HCN@hcndb>create table test1 as select * from dba_objects;
Table created.
2、验证表是否能够重定义
HCN@hcndb>
HCN@hcndb>
HCN@hcndb>EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(user, 'test1');
BEGIN DBMS_REDEFINITION.CAN_REDEF_TABLE(user, 'test1'); END;
*
ERROR at line 1:
ORA-12089: cannot online redefine table "HCN"."TEST1" with no primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 137
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1479
ORA-06512: at line 1
HCN@hcndb>
HCN@hcndb>
HCN@hcndb>
HCN@hcndb>EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(user, 'test1', DBMS_REDEFINITION.cons_use_rowid);
PL/SQL procedure successfully completed.
HCN@hcndb>
3、创建中间表
CREATE TABLE test_temp PARTITION BY RANGE (object_id)
(PARTITION P1 VALUES LESS THAN (10000),
PARTITION P2 VALUES LESS THAN (20000),
PARTITION P3 VALUES LESS THAN (30000),
PARTITION P4 VALUES LESS THAN (40000),
PARTITION P5 VALUES LESS THAN (MAXVALUE))
as select * from test1 where 1=0;
HCN@hcndb>
HCN@hcndb>CREATE TABLE test_temp PARTITION BY RANGE (object_id) ------中间表是分区表，重定义后就是分区表，如果建的是普通表，那么重定义后就是普通表
2 (PARTITION P1 VALUES LESS THAN (10000),
3 PARTITION P2 VALUES LESS THAN (20000),
4 PARTITION P3 VALUES LESS THAN (30000),
5 PARTITION P4 VALUES LESS THAN (40000),
6 PARTITION P5 VALUES LESS THAN (MAXVALUE))
7 as select * from test1 where 1=0;
Table created.
HCN@hcndb>
HCN@hcndb>
4、执行表的在线重定义
exec dbms_redefinition.start_redef_table('hcn', 'test1', 'test_temp');
HCN@hcndb>
HCN@hcndb>exec dbms_redefinition.start_redef_table('hcn', 'test1', 'test_temp');
BEGIN dbms_redefinition.start_redef_table('hcn', 'test1', 'test_temp'); END;
*
ERROR at line 1:
ORA-12089: cannot online redefine table "HCN"."TEST1" with no primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 50
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1343
ORA-06512: at line 1
HCN@hcndb>
因为没有主键所以用上面的方法来执行就会有报错
select a.column_name || ' ' || b.column_name || ','
from dba_tab_columns a, dba_tab_columns b
where a.table_name = 'TEST1'
and a.OWNER = b.owner
and a.TABLE_NAME = b.TABLE_NAME
and a.COLUMN_NAME = b.column_name
通过下面的语句进行在线重定义
EXEC DBMS_REDEFINITION.START_REDEF_TABLE('hcn', 'test1', 'test_temp','OWNER OWNER,OBJECT_NAME OBJECT_NAME,SUBOBJECT_NAME SUBOBJECT_NAME,OBJECT_ID OBJECT_ID,DATA_OBJECT_ID DATA_OBJECT_ID,OBJECT_TYPE OBJECT_TYPE,CREATED CREATED,LAST_DDL_TIME LAST_DDL_TIME,TIMESTAMP TIMESTAMP,STATUS STATUS,TEMPORARY TEMPORARY,GENERATED GENERATED,SECONDARY SECONDARY',DBMS_REDEFINITION.cons_use_rowid);
---要放在同一行执行，不然要报错
HCN@hcndb>
HCN@hcndb>EXEC DBMS_REDEFINITION.START_REDEF_TABLE('hcn', 'test1', 'test_temp','OWNER OWNER,OBJECT_NAME OBJECT_NAME,SUBOBJECT_NAME SUBOBJECT_NAME,OBJECT_ID OBJECT_ID,DATA_OBJECT_ID DATA_OBJECT_ID,OBJECT_TYPE OBJECT_TYPE,CREATED CREATED,LAST_DDL_TIME LAST_DDL_TIME,TIMESTAMP TIMESTAMP,STATUS STATUS,TEMPORARY TEMPORARY,GENERATED GENERATED,SECONDARY SECONDARY',DBMS_REDEFINITION.cons_use_rowid);
PL/SQL procedure successfully completed.
HCN@hcndb>
HCN@hcndb>
说明：
1）采用单引号列出T2与T2_1表列的对应关系
2）列与列之间采用单引号分割，单引号后面要有空格
---执行了表的在线重定义后，基表还是一般表
select table_name, partition_name
from user_tab_partitions
where table_name IN ('TEST1','TEST_TEMP');
HCN@hcndb>
HCN@hcndb>select table_name, partition_name
2 from user_tab_partitions
3 where table_name IN ('TEST1','TEST_TEMP');
TABLE_NAME PARTITION_NAME
-------------------- --------------------
TEST_TEMP P1
TEST_TEMP P2
TEST_TEMP P3
TEST_TEMP P4
TEST_TEMP P5
HCN@hcndb>
HCN@hcndb>
HCN@hcndb>
5、exec dbms_redefinition.sync_interim_table(user, 'test1', 'test_temp');同步数据 --同步后，test1依然还是普通表
HCN@hcndb>
HCN@hcndb>select count(*) from test1;
COUNT(*)
----------
50642
HCN@hcndb>select count(*) from test_temp;
COUNT(*)
----------
50642
HCN@hcndb>
6、结束定义
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(user, 'test1', 'test_temp');
HCN@hcndb>
HCN@hcndb>EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(user, 'test1', 'test_temp');
PL/SQL procedure successfully completed.
HCN@hcndb>
HCN@hcndb>
HCN@hcndb>select table_name, partition_name
2 from user_tab_partitions
3 where table_name IN ('TEST1','TEST_TEMP');
TABLE_NAME PARTITION_NAME
-------------------- --------------------
TEST1 P1
TEST1 P2
TEST1 P3
TEST1 P4
TEST1 P5
HCN@hcndb>
HCN@hcndb>
---结束定义后，test1终于变为了分区表，此次test_temp变为了普通表
7、删除中间表
HCN@hcndb>
HCN@hcndb>drop table test_temp;
Table dropped.
HCN@hcndb>
8、处理Test1表（删除隐藏列）
select col#, name, type#
from SYS.COL$
WHERE OBJ# = (select object_id
from dba_objects
where object_name = 'TEST1'
and object_type = 'TABLE');
HCN@hcndb>
HCN@hcndb>select col#, name, type#
2 from SYS.COL$
3 WHERE OBJ# = (select object_id
4 from dba_objects
5 where object_name = 'TEST1'
6 and object_type = 'TABLE');
COL# NAME TYPE#
---------- ---------------------------------------- ----------
1 OWNER 1
2 OBJECT_NAME 1
3 SUBOBJECT_NAME 1
4 OBJECT_ID 2
5 DATA_OBJECT_ID 2
6 OBJECT_TYPE 1
7 CREATED 12
8 LAST_DDL_TIME 12
9 TIMESTAMP 1
10 STATUS 1
11 TEMPORARY 1
12 GENERATED 1
13 SECONDARY 1
0 SYS_C00014_13040922:56:08$ 1
14 rows selected.
HCN@hcndb>
--发现一个多余隐藏列SYS_C00014_13040922:56:08$，这个列是需要删除的
alter table test1 set unused ("SYS_C00014_13040922:56:08$");
alter table test1 drop unused columns;
HCN@hcndb>
HCN@hcndb>alter table test1 set unused ("SYS_C00014_13040922:56:08$");
Table altered.
HCN@hcndb>
HCN@hcndb>alter table test1 drop unused columns;
Table altered.
HCN@hcndb>
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
