怎么在线对表进行分区（How to perform online redefinition）！

来源：互联网发布：我是歌手知乎编辑：程序博客网时间：2024/06/13 01:31

怎么在线对表进行分区（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.