dbms_redefinition把堆表转化成分区表
1.需求:一个Oracle堆表,随着业务量数据增长,需要把此堆表,转换成分区表,实现在线转化.可以用Oracle自带包 dbms_redefinition.
2.创建环境.
Oracle dbms_redefinition包,由堆表转化为分区表,有两种方式,一种为PK主键方式,一种为ROWID方式.
如包中信息.我们先按照PK主键方式,即源表(orig_table)存在主键列,进行转化.
-- Constants for the options_flag parameter of start_redef_table
cons_use_pk CONSTANT PLS_INTEGER := 1;
cons_use_rowid CONSTANT PLS_INTEGER := 2;
2.1 创建源表(orig_table),假如源表orig_table为我们现在的堆表,随着业务增长,需要把此表转化成分区表.
2.1.1创建源表(orig_table).
CREATE TABLE orig_table
(
id NUMBER,
name VARCHAR2 (40),
c_date DATE
);
2.1.2 源表(orig_table) id 列创建主键.此处如果没有主键, 在进行DBMS_REDEFINITION.CAN_REDEF_TABLE时,会报错没有主键,不能进行转化.
ALTER TABLE orig_table ADD CONSTRAINT pk_origtable_id PRIMARY KEY (id);
JIEYU119> SELECT owner,constraint_name,constraint_type,table_name,status
FROM user_constraints
WHERE table_name LIKE 'ORIG%';
OWNER CONSTRAINT_NAME C TABLE_NAME STATUS
--------------- ------------------------------ ----------- -------------------
JIEYU119 PK_ORIGTABLE_ID P ORIG_TABLE ENABLED
2.1.3 给源表(orig_table)制造些数据.
INSERT INTO orig_table
SELECT ROWNUM,object_name,created
FROM dba_objects
WHERE ROWNUM <= 70000;
COMMIT;
2.2 创建中间表orig_table_temp
CREATE TABLE orig_table_temp
(
id NUMBER,
name VARCHAR2 (40),
c_date DATE
)
PARTITION BY RANGE
(c_date)
(
PARTITION p1 VALUES LESS THAN (TO_DATE ('2015-08-01', 'YYYY-MM-DD')),
PARTITION p2 VALUES LESS THAN (TO_DATE ('2015-09-01', 'YYYY-MM-DD')),
PARTITION p3 VALUES LESS THAN (TO_DATE ('2015-12-01', 'YYYY-MM-DD')),
PARTITION p_max VALUES LESS THAN (MAXVALUE));
EXECUTE DBMS_REDEFINITION.SYNC_INTERIM_TABLE('JIEYU119','ORIG_TABLE','ORIG_TABLE_TEMP');
这一步为源表与中间表数据,进行同步.
3.4 finish_redef_table - complete the online re-organization
EXECUTE DBMS_REDEFINITION.FINISH_REDEF_TABLE('JIEYU119','ORIG_TABLE','ORIG_TABLE_TEMP');
此步Finish完后,源表orig_table上的主键pk_origtable_id,trigger,index转移到了中间表orig_table_temp上了,如下.
JIEYU119> SELECT owner,constraint_name,constraint_type,table_name,status
FROM user_constraints
WHERE table_name LIKE 'ORIG%';
OWNER CONSTRAINT_NAME C TABLE_NAME STATUS
--------------- ------------------------------ ------------ ------------------
JIEYU119 PK_ORIGTABLE_ID P ORIG_TABLE_TEMP ENABLED
3.5 异常情况, 如果转换中出错.可以用
abort_redef_table结束.
abort_redef_table - clean up after errors or abort the online re-organization
EXECUTE DBMS_REDEFINITION.abort_redef_table('JIEYU119','ORIG_TABLE','ORIG_TABLE_TEMP');
3.6 结束.
在 DBMS_REDEFINITION.FINISH_REDEF_TABLE后,源表orig_table由堆表转化成了分区表.中间表orig_table_temp由分区表,转化成了堆表.
JIEYU119> SELECT table_name,partitioning_type,status
2 FROM user_part_tables
3 WHERE table_name LIKE 'ORIG%';
TABLE_NAME PARTITION STATUS
------------------ ------------ ---------
ORIG_TABLE RANGE VALID
JIEYU119> SELECT table_name,logging,partitioned,status
FROM user_tables
WHERE table_name LIKE 'ORIG%';
TABLE_NAME LOGGING PARTTIONED STATUS
------------------- ----------- --------------- ---------------
ORIG_TABLE YES VALID
ORIG_TABLE_TEMP YES NO VALID
1.需求:一个Oracle堆表,随着业务量数据增长,需要把此堆表,转换成分区表,实现在线转化.可以用Oracle自带包 dbms_redefinition.
2.创建环境.
Oracle dbms_redefinition包,由堆表转化为分区表,有两种方式,一种为PK主键方式,一种为ROWID方式.
如包中信息.我们先按照PK主键方式,即源表(orig_table)存在主键列,进行转化.
-- Constants for the options_flag parameter of start_redef_table
cons_use_pk CONSTANT PLS_INTEGER := 1;
cons_use_rowid CONSTANT PLS_INTEGER := 2;
2.1 创建源表(orig_table),假如源表orig_table为我们现在的堆表,随着业务增长,需要把此表转化成分区表.
2.1.1创建源表(orig_table).
CREATE TABLE orig_table
(
id NUMBER,
name VARCHAR2 (40),
c_date DATE
);
2.1.2 源表(orig_table) id 列创建主键.此处如果没有主键, 在进行DBMS_REDEFINITION.CAN_REDEF_TABLE时,会报错没有主键,不能进行转化.
ALTER TABLE orig_table ADD CONSTRAINT pk_origtable_id PRIMARY KEY (id);
JIEYU119> SELECT owner,constraint_name,constraint_type,table_name,status
FROM user_constraints
WHERE table_name LIKE 'ORIG%';
OWNER CONSTRAINT_NAME C TABLE_NAME STATUS
--------------- ------------------------------ ----------- -------------------
JIEYU119 PK_ORIGTABLE_ID P ORIG_TABLE ENABLED
2.1.3 给源表(orig_table)制造些数据.
INSERT INTO orig_table
SELECT ROWNUM,object_name,created
FROM dba_objects
WHERE ROWNUM <= 70000;
COMMIT;
2.2 创建中间表orig_table_temp
CREATE TABLE orig_table_temp
(
id NUMBER,
name VARCHAR2 (40),
c_date DATE
)
PARTITION BY RANGE
(c_date)
(
PARTITION p1 VALUES LESS THAN (TO_DATE ('2015-08-01', 'YYYY-MM-DD')),
PARTITION p2 VALUES LESS THAN (TO_DATE ('2015-09-01', 'YYYY-MM-DD')),
PARTITION p3 VALUES LESS THAN (TO_DATE ('2015-12-01', 'YYYY-MM-DD')),
PARTITION p_max VALUES LESS THAN (MAXVALUE));
3. 操作步骤
3.1 can_redef_table - check if given table can be re-defined
3.3 sync_interim_table - synchronize interim table with the original
3.1 can_redef_table - check if given table can be re-defined
EXECUTE DBMS_REDEFINITION.CAN_REDEF_TABLE('JIEYU119','ORIG_TABLE');
3.2 start_redef_table - start the online re-organization
EXECUTE DBMS_REDEFINITION.START_REDEF_TABLE('JIEYU119','ORIG_TABLE','ORIG_TABLE_TEMP');
此时数据开始传输.
JIEYU119> select count(*) from orig_table;
COUNT(*)
----------
70000
JIEYU119>
JIEYU119> select count(*) from orig_table_temp;
COUNT(*)
----------
70000
3.2 start_redef_table - start the online re-organization
EXECUTE DBMS_REDEFINITION.START_REDEF_TABLE('JIEYU119','ORIG_TABLE','ORIG_TABLE_TEMP');
此时数据开始传输.
JIEYU119> select count(*) from orig_table;
COUNT(*)
----------
70000
JIEYU119>
JIEYU119> select count(*) from orig_table_temp;
COUNT(*)
----------
70000
EXECUTE DBMS_REDEFINITION.SYNC_INTERIM_TABLE('JIEYU119','ORIG_TABLE','ORIG_TABLE_TEMP');
这一步为源表与中间表数据,进行同步.
3.4 finish_redef_table - complete the online re-organization
此步Finish完后,源表orig_table上的主键pk_origtable_id,trigger,index转移到了中间表orig_table_temp上了,如下.
JIEYU119> SELECT owner,constraint_name,constraint_type,table_name,status
FROM user_constraints
WHERE table_name LIKE 'ORIG%';
OWNER CONSTRAINT_NAME C TABLE_NAME STATUS
--------------- ------------------------------ ------------ ------------------
JIEYU119 PK_ORIGTABLE_ID P ORIG_TABLE_TEMP ENABLED
abort_redef_table - clean up after errors or abort the online re-organization
EXECUTE DBMS_REDEFINITION.abort_redef_table('JIEYU119','ORIG_TABLE','ORIG_TABLE_TEMP');
3.6 结束.
在 DBMS_REDEFINITION.FINISH_REDEF_TABLE后,源表orig_table由堆表转化成了分区表.中间表orig_table_temp由分区表,转化成了堆表.
JIEYU119> SELECT table_name,partitioning_type,status
2 FROM user_part_tables
3 WHERE table_name LIKE 'ORIG%';
TABLE_NAME PARTITION STATUS
------------------ ------------ ---------
ORIG_TABLE RANGE VALID
JIEYU119> SELECT table_name,logging,partitioned,status
FROM user_tables
WHERE table_name LIKE 'ORIG%';
TABLE_NAME LOGGING PARTTIONED STATUS
------------------- ----------- --------------- ---------------
ORIG_TABLE YES VALID
ORIG_TABLE_TEMP YES NO VALID
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28569596/viewspace-1872502/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28569596/viewspace-1872502/