oracle重新定义表,ORACLE在线重定义

一、建表和索引

SQL> create table redefie_table (

2     id number(10) primary key,

3     create_date date

4  );

Table created.

SQL> insert into redefie_table  select  rownum,created from user_objects;

33 rows created.

SQL> commit;

Commit complete.

SQL> create index idx_redefie_table on redefie_table(create_date);

Index created.

二、收集统计信息

SQL> exec dbms_stats.gather_table_stats('jerry','redefie_table',cascade=>true);

PL/SQL procedure successfully completed.

三、创建临时分区表

SQL> create table partition_redefie_table (id number primary key, time date) partition by range

2  (time)

3  (

4     partition p1 values less than (to_date('2012-7-1','yyyy-mm-dd')),

5     partition p2 values less than (to_date('2013-1-1','yyyy-mm-dd')),

6     partition p3 values less than (to_date('2013-7-1','yyyy-mm-dd')),

7     partition p4 values less than (maxvalue)

8  );

Table created.

四 检查重定义的合理性

SQL> exec dbms_redefinition.can_redef_table('jerry','redefie_table');

PL/SQL procedure successfully completed.

五、开始重定义

5.1 如果分区表和源表列名相同，则可以用如下(本例中的列名不同，所以会报错)：

SQL> begin

2  dbms_redefinition.start_redef_table(

3     uname=>'JERRY',

4     orig_table=>'redefie_table',

5     int_table=>'partition_redefie_table'

6  );

7  end;

8

9  /

begin

*

ERROR at line 1:

ORA-42016: shape of interim table does not match specified column mapping

ORA-06512: at "SYS.DBMS_REDEFINITION", line 52

ORA-06512: at "SYS.DBMS_REDEFINITION", line 1646

ORA-06512: at line 2

5.2 如果分区表和源表列名不相同，则用如下(注意列名转换，源表和分区表的对应字段)：

SQL> exec dbms_redefinition.start_redef_table('JERRY','redefie_table','partition_redefie_table','id id,create_date time',       dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed.

SQL> exec dbms_redefinition.start_redef_table('JERRY','redefie_table','partition_redefie_table','id id,create_date time',       dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed.

SQL> select count(*) from partition_redefie_table

2  ;

COUNT(*)

----------

33

SQL> select count(*) from redefie_table;

COUNT(*)

----------

33

5.3同步新表，可选操作

QL> exec dbms_redefinition.sync_interim_table( uname=>'JERRY', orig_table=>'redefie_table',    int_table=>'partition_redefie_table');

PL/SQL procedure successfully completed.

5.4创建索引，在线重定义，只定义数据，索引需要单独建立

SQL> create index idx_partition_redefie_table on partition_redefie_table(time);

Index created.

5.5 结束重定义

SQL> exec dbms_redefinition.finish_redef_table( uname=>'JERRY', orig_table=>'redefie_table',    int_table=>'partition_redefie_table');

PL/SQL procedure successfully completed.

重定义在此处的含义，基表和分区表进行了交换，基表REDEFIE_TABLE变成了分区表，分区表PARTITION_REDEFIE_TABLE变成了基表：

SQL> select T.TABLE_NAME From user_tab_partitions t where t.table_name='REDEFIE_TABLE';

TABLE_NAME

------------------------------------------------------------

REDEFIE_TABLE

REDEFIE_TABLE

REDEFIE_TABLE

REDEFIE_TABLE

SQL> select T.TABLE_NAME From user_tab_partitions t where t.table_name='PARTITION_REDEFIE_TABLE';

no rows selected

六、删除临时表

SQL> drop table partition_redefie_table;

Table dropped.

七、索引重命名

SQL> select t.index_name from user_indexes t ;

INDEX_NAME

------------------------------------------------------------

SYS_C0013125

IDX_PARTITION_REDEFIE_TABLE

SQL> alter index IDX_PARTITION_REDEFIE_TABLE rename to idx_redefie_table;

SQL> select t.index_name from user_indexes t ;

INDEX_NAME

------------------------------------------------------------

IDX_REDEFIE_TABLE

SYS_C0013125

阅读(805) | 评论(0) | 转发(0) |