如何在线对表进行分区（How to perform online redefinition）
怎么在线对表进行分区（How to perform online redefinition）！
今天是2013-10-17，之前同事做过一个表的在线分区，当时情况是这样的，有一个大表，每个月多要删除一个月的数据，当delete的时候不但速度慢，而且会产生一个undo的bug，考虑到使用分区表来解决此问题，也就是按照每个月划分range 分区，删除上一个月的数据的时候直接truncate partition 。我们使用dbms_redefinition包来完成表的在线分区功能。
创建一个在线表：
1、选择重新定义的方法。
一种为by key，也就是一个primary key 和psseudo-primary key，sseudo-primary is not null constraints， For this method, the versions of the tables before and after redefinition should have the same primary key columns,如果不指定options_flag则表示 使用默认该方法。
第二种是by rowid，添加了一个M_ROW$$的隐藏列，10.2.0之后，完了之后自动设置该column为unused，并且可以使用alter table ....drop unused column 去删除它。
2、使用can_redef_table确认一下该表是否可以在线重定义，如果不行的话那么就会报错
3、创建一个过渡表（在同一个schema下）包含所需的物理结构和逻辑属性。不是必须要创建该表的索引啊、约束啊、触发器啊等等。因为在使用copy_table_dependents的时候会自动创建。
4、如果使用by rowid，那么需要启动表的movement，alter table 。。。。。enable row movement;
5、为了加快处理大表的速度，可以使用并行操作。alter session force parallel dml paralle number; alter session force parallel query parallel number;
6、开始重新定义进程start_redef_table，指定schema和表名，指定过渡表，指定重定义的方法（dbms_redefinition.cons_use_pk or dbms_redefinition.cons_use_rowid)。
note：
You can query the DBA_REDEFINITION_OBJECTS view to list the objects currently involved in online redefinition.
If START_REDEF_TABLE fails for any reason, you must call ABORT_REDEF_TABLE, otherwise subsequent attempts to redefine the table will fail.
7、复制dependent 对象（如索引、触发器、物理话视图日志、授权、约束）和统计信息从需要新定义的表到过渡表中。有两种办法、1中是首选的也就是自动。2中是需要手动操作
对于第一种，我们使用dbms_redefinition.copy_table_dependents过程来在内部表中自动创建依赖的对象。但这会使该过渡表的dependent的名字和original dependent objects的名字一样。
第二种，就是手动进行操作了。注意要在执行完操作后必须是使用register_dependent_object 过程来重新注册这些依赖的对象。
8、执行finish_table完成表的重定义操作。
9、如果使用by rowid那么要删除伪列。在10.2.0之前会产生M_row$$一个标示列，需要设置unused然后删除，在10.2.0之后该M_ROW$$会自动设置为unused，可以执行alter table drop unused columns；
10、等待一段时间，保证所有的查询都在临时表中，那么接着删除该过渡表，有可能出现ORA-08103的错误。（谨记）
目前我有张表为amy。共有100多万条数据。如下：
SQL> conn rhys/amy;
Connected.
SQL> select count(*) from amy;
COUNT(*)
----------
1032509
SQL>
SQL> desc amy;
Name Null? Type
----------------------------- -------- --------------------
ID NUMBER
CREATE_DATE TIMESTAMP(6)
SQL>
查看该表 是否可以进行在线重定义。
SQL> execute dbms_redefinition.can_redef_table('RHYS','AMY',dbms_redefinition.cons_use_rowid);
PL/SQL procedure successfully completed.
SQL> alter table amy add constraint primary_key_id primary key (id);
Table altered.
SQL>
创建过渡表：
SQL> get bb.sql
1 create table amy_temp(
2 id number,
3 create_date timestamp
4 )
5 partition by range(id)
6 (
7 partition part200000 values less than (200000),
8 partition part500000 values less than (500000),
9 partition part_other values less than (maxvalue)
10* )
SQL> r
1 create table amy_temp(
2 id number,
3 create_date timestamp
4 )
5 partition by range(id)
6 (
7 partition part200000 values less than (200000),
8 partition part500000 values less than (500000),
9 partition part_other values less than (maxvalue)
10* )
Table created.
SQL>
为了加快速度，设置并行操作；
SQL>
SQL> alter session force parallel dml parallel 4;
Session altered.
SQL> alter session force parallel query parallel 4;
Session altered.
SQL>
开始 执行重定向操作：
SQL> begin
2 dbms_redefinition.start_redef_table(
3 uname=>'RHYS',
4 ORIG_TABLE=>'AMY',
5 INT_TABLE=>'AMY_TEMP',
6 OPTIONS_FLAG=>(DBMS_REDEFINITION.CONS_USE_ROWID)
7 );
8 end ;
9 /
PL/SQL procedure successfully completed.
SQL> select * from dba_redefinition_objects;
OBJECT_TYPE OBJECT_OWNER OBJECT_NAME BASE_TABLE_OWNER BASE_TABLE_NAME INTERIM_OBJECT_OWNER INTERIM_OBJECT_NAME
------------ -------------------- ------------------------------ ------------------------------ ------------------------------ -------------------- ------------------------------
EDITION_NAME
------------------------------
TABLE RHYS AMY RHYS AMY RHYS AMY_TEMP
执行完后，查看相关信息：
SQL>
SQL> select count(*) from amy_temp;
COUNT(*)
----------
1032509
SQL> select count(*) from amy;
COUNT(*)
----------
1032509
SQL> select PARTITIONED from user_tables where table_name='AMY';
PAR
---
NO
SQL> SELECT PARTITIONED FROM USER_TABLES WHERE TABLE_NAME='AMY_TEMP';
PAR
---
YES
SQL> SELECT OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME='AMY';
OWNER CONSTRAINT_NAME C TABLE_NAME
------------------------------ ------------------------------ - ------------------------------
RHYS PRIMARY_KEY_ID P AMY
SQL> SELECT OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME='AMY_TEMP';
no rows selected
SQL>
SQL> select table_name,column_name,data_type from user_tab_cols where table_name='AMY';
TABLE_NAME COLUMN_NAME DATA_TYPE
------------------------------ ------------------------------ ----------------------------------------------------------------------
AMY ID NUMBER
AMY CREATE_DATE TIMESTAMP(6)
SQL> COL TABLE_NAME FOR A50
SQL> COL DATA_TYPE FOR A60
SQL> R
1* select table_name,column_name,data_type from user_tab_cols where table_name='AMY'
TABLE_NAME COLUMN_NAME DATA_TYPE
-------------------------------------------------- ------------------------------ ------------------------
AMY ID NUMBER
AMY CREATE_DATE TIMESTAMP(6)
SQL> C /AMY/AMY_TEMP
1* select table_name,column_name,data_type from user_tab_cols where table_name='AMY_TEMP'
SQL> R
1* select table_name,column_name,data_type from user_tab_cols where table_name='AMY_TEMP'
TABLE_NAME COLUMN_NAME DATA_TYPE
-------------------------------------------------- ------------------------------ ------------------------
AMY_TEMP ID NUMBER
AMY_TEMP CREATE_DATE TIMESTAMP(6)
AMY_TEMP M_ROW$$ VARCHAR2
SQL>
SQL> SELECT * FROM USER_CONS_COLUMNS WHERE TABLE_NAME='AMY_TEMP';
no rows selected
SQL> SELECT INDEX_NAME,TABLE_NAME FROM USER_INDEXES WHERE TABLE_NAME='AMY_TEMP';
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
I_SNAP$_AMY_TEMP AMY_TEMP
SQL>
开始进行复制依赖对象。
SQL> var v_num number;
SQL> get p5.sql
1 begin
2 dbms_redefinition.copy_table_dependents(
3 uname=>'RHYS',
4 orig_table=>'AMY',
5 INT_TABLE=>'AMY_TEMP',
6 COPY_CONSTRAINTS=>TRUE,
7 copy_indexes=>(dbms_redefinition.cons_orig_params),
8 copy_privileges=>true,
9 copy_triggers=>true,
10 num_errors=>:v_num,
11 copy_statistics=>true,
12 ignore_errors=>false,
13 copy_mvlog=>true);
14* end;
SQL> r
1 begin
2 dbms_redefinition.copy_table_dependents(
3 uname=>'RHYS',
4 orig_table=>'AMY',
5 INT_TABLE=>'AMY_TEMP',
6 COPY_CONSTRAINTS=>TRUE,
7 copy_indexes=>(dbms_redefinition.cons_orig_params),
8 copy_privileges=>true,
9 copy_triggers=>true,
10 num_errors=>:v_num,
11 copy_statistics=>true,
12 ignore_errors=>false,
13 copy_mvlog=>true);
14* end;
PL/SQL procedure successfully completed.
SQL>
查看结果信息如下：
SQL> select owner,constraint_name,constraint_type,table_name from user_constraints where table_name='AMY';
OWNER CONSTRAINT_NAME C TABLE_NAME
------------------------------ ------------------------------ - ------------------------------
RHYS PRIMARY_KEY_ID P AMY
SQL> c /AMY/AMY_TEMP
1* select owner,constraint_name,constraint_type,table_name from user_constraints where table_name='AMY_TEMP'
SQL> R
1* select owner,constraint_name,constraint_type,table_name from user_constraints where table_name='AMY_TEMP'
OWNER CONSTRAINT_NAME C TABLE_NAME
------------------------------ ------------------------------ - ------------------------------
RHYS TMP$$_PRIMARY_KEY_ID0 P AMY_TEMP
SQL> SELECT * FROM USER_CONS_COLUMNS WHERE TABLE_NAME='AMY_TEMP';
OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME POSITION
------------------------------ ------------------------------ ------------------------------ ---------------------------------------- ----------
RHYS TMP$$_PRIMARY_KEY_ID0 AMY_TEMP ID 1
SQL>
SQL> select index_name,index_type,table_name from user_indexes where table_name='AMY';
INDEX_NAME INDEX_TYPE TABLE_NAME
------------------------------ --------------------------- ------------------------------
PRIMARY_KEY_ID NORMAL AMY
SQL> c /AMY/AMY_TEMP
1* select index_name,index_type,table_name from user_indexes where table_name='AMY_TEMP'
SQL> R
1* select index_name,index_type,table_name from user_indexes where table_name='AMY_TEMP'
INDEX_NAME INDEX_TYPE TABLE_NAME
------------------------------ --------------------------- ------------------------------
I_SNAP$_AMY_TEMP NORMAL AMY_TEMP
TMP$$_PRIMARY_KEY_ID0 NORMAL AMY_TEMP
SQL>
SQL> select PARTITIONED from user_tables where table_name='AMY';
PAR
---
NO
SQL> SELECT PARTITIONED FROM USER_TABLES WHERE TABLE_NAME='AMY_TEMP';
PAR
---
YES
执行finish_table完成表的重定义操作。
SQL> r
1 begin
2 dbms_redefinition.finish_redef_table(
3 uname=>'RHYS',
4 orig_table=>'AMY',
5 INT_TABLE=>'AMY_TEMP'
6 );
7* END;
PL/SQL procedure successfully completed.
SQL>
查看结果：
SQL> SELECT PARTITIONED FROM USER_TABLES WHERE TABLE_NAME='AMY_TEMP';
PAR
---
NO
SQL> select PARTITIONED from user_tables where table_name='AMY';
PAR
---
YES
SQL> select index_name,index_type,table_name from user_indexes where table_name='AMY_TEMP';
INDEX_NAME INDEX_TYPE TABLE_NAME
------------------------------ --------------------------- --------------------------------------------------
TMP$$_PRIMARY_KEY_ID0 NORMAL AMY_TEMP
SQL> select index_name,index_type,table_name from user_indexes where table_name='AMY';
INDEX_NAME INDEX_TYPE TABLE_NAME
------------------------------ --------------------------- --------------------------------------------------
PRIMARY_KEY_ID NORMAL AMY
SQL>
后续操作：
SQL> select table_name,column_name,data_type from user_tab_cols where table_name='AMY';
TABLE_NAME COLUMN_NAME DATA_TYPE
-------------------------------------------------- ------------------------------ ------------------------
AMY ID NUMBER
AMY CREATE_DATE TIMESTAMP(6)
AMY SYS_C00003_13101717:32:40$ VARCHAR2
SQL> ALTER TABLE AMY DROP UNUSED COLUMNS;
Table altered.
SQL> drop table amy_temp purge;
Table dropped.
SQL>
SQL> select table_name,partition_name,high_value from user_tab_partitions where table_name='AMY';
TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- -------------------- ----------------------------------------------------------------
AMY PART200000 200000
AMY PART500000 500000
AMY PART_OTHER MAXVALUE
SQL>
删除这个表中小于200000的数据。
eg：
SQL>
SQL> select * from (select * from amy order by id) where rownum<10;
ID CREATE_DATE
---------- ---------------------------------------
200000 17-OCT-13 03.45.38.985089 PM
200001 17-OCT-13 03.45.38.985201 PM
200002 17-OCT-13 03.45.38.985320 PM
200003 17-OCT-13 03.45.38.985422 PM
200004 17-OCT-13 03.45.38.985538 PM
200005 17-OCT-13 03.45.38.986409 PM
200006 17-OCT-13 03.45.38.986626 PM
200007 17-OCT-13 03.45.38.986729 PM
200008 17-OCT-13 03.45.38.986836 PM
9 rows selected.