oracle 線上重新定義，普通表改變分割槽表，分割槽表可以更改型別、分割槽欄位等

0、線上表的建立表結構

create table a (bbri date,id number ,name varchar2(20),qxbm number)

partition by list (qxbm )(partition list_1 values (1),

partition list_2 values (2),

partition list_3 values (3),

partition list_4 values (4),

partition list_5 values (5)

)

1、建立臨時表，臨時表跟源表結構一致，但分割槽列進行更改、分割槽型別也進行更改

create table tmp (bbri date,id number ,name varchar2(20),qxbm number)

partition by range (bbri )

(partition p1  values less than (to_date('2020-06-01', 'yyyy-mm-dd')),

partition p2  values less than (to_date('2020-07-01', 'yyyy-mm-dd')),

partition p3  values less than (to_date('2020-08-01', 'yyyy-mm-dd')),

partition p4  values less than (to_date('2020-09-01', 'yyyy-mm-dd')),

partition p5  values less than (to_date('2020-10-01', 'yyyy-mm-dd'))

)

3、檢查重定義的合理性；如果不能重定義，會顯示具體的原因。

exec dbms_redefinition.can_redef_table('TEST', 'A'); 

 

ORA-12089: cannot online redefine table "TEST"."A" with no primary key

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

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

ORA-06512: at line 2

需要新增主鍵

ALTER TABLE A ADD CONSTRAINTS PK_A_id PRIMARY KEY (id) online parallel 8 ; 

exec dbms_redefinition.can_redef_table('TEST', 'A'); 

4、重定義表結構（將線上的表重新整理臨時表中）

 BEGIN

        DBMS_REDEFINITION.start_redef_table(

        uname => 'TEST',

        orig_table => 'A',

        int_table => 'tmp');

   END; 

如果中途出現意外，使用如下語句回滾：

execute dbms_redefinition.abort_redef_table('TEST','A','tmp'); 

5、同步臨時表 （同步新增的資料）

 BEGIN

       dbms_redefinition.sync_interim_table(

       uname => 'TEST',

       orig_table => 'A',

       int_table => 'tmp');

  END;

  

6、建立新表的索引(線上重定義只定義資料，不建立索引）

用下面的SQL獲取建立索引的語句，然後建立到臨時表上面

select index_name from dba_indexes where owner='TEST' and table_name='A';

   select dbms_metadata.get_ddl('INDEX','IDX_CON_CONTENT_H1') from dual;

   select dbms_metadata.get_ddl('INDEX','PK_CON_CONTENT_H_CELL_ID1') from dual;

   select dbms_metadata.get_ddl('INDEX','PK_CON_CONTENT_H_HISTORY') from dual; 

7、收集臨時表的統計資訊

   exec dbms_stats.gather_table_stats('TEST', 'tmp', cascade => true); 

   

8、結束重定義

 BEGIN

        DBMS_REDEFINITION.FINISH_REDEF_TABLE(uname => 'TEST',

        orig_table => 'A',

        int_table  => 'tmp'

        );

   END;   

   

9、檢視錶的索引和約束，是否與之前一致   

select * from user_tab_partitions  where table_name='A';

select * from user_part_tables where table_name='A';

select * from  user_PART_KEY_COLUMNS where name=upper('a')

10、編譯失效的物件

SELECT 'ALTER ' || (CASE

     WHEN OBJECT_TYPE = 'PACKAGE BODY' THEN

    'PACKAGE'

 ELSE

     OBJECT_TYPE

     END) || ' ' || owner || '.' || OBJECT_NAME || ' COMPILE ' || (CASE

 WHEN OBJECT_TYPE = 'PACKAGE BODY' THEN

    'BODY;'

 ELSE

  ';'

   END),

   owner,

   OBJECT_NAME,

   OBJECT_TYPE,

   STATUS,

   O.CREATED,

   LAST_DDL_TIME

 FROM dba_OBJECTS O

 WHERE STATUS = 'INVALID'; 

 

11、刪除臨時表

     truncate table tmp;--因為是大表，建議使用該方式

     drop table tmp; --刪除臨時表的定義