Oracle11G 在線重定義

create tablespace tbs1 datafile '/opt/oracle/oradata/haier/tbs1.dbf' size 500m autoextend on maxsize 2G;

create tablespace tbs2 datafile '/opt/oracle/oradata/haier/tbs2.dbf' size 500m autoextend on maxsize 2G;

create tablespace tbs3 datafile '/opt/oracle/oradata/haier/tbs3.dbf' size 500m autoextend on maxsize 2G;

SQL> desc HHHH
 Name        Null?    Type
 ----------------------------------------- -------- ----------------------------
 PNTMALL_PNT_ID         NUMBER
 PNTMALL_PNT_DT         DATE
.......
 PNTMALL_HRTYPE_DESC        VARCHAR2(2000)

SQL> selectcount(*) from HHHH;

  COUNT(*)

----------

  16713034

alter table HHHH add constraint HHHH_PKEY primary key(PNTMALL_PNT_ID);

create table HHHH_tmp

partition by range(PNTMALL_PNT_DT)

(

partition p1 values less than (to_date('2016-01-01','yyyy-mm-dd')) tablespace tbs1,

partition p2 values less than (to_date('2017-01-01','yyyy-mm-dd')) tablespace tbs2,

partition p3 values less than (maxvalue) tablespace tbs3

)

as

select * from HHHH where 1=2;

SQL> begin

  2 DBMS_REDEFINITION.START_REDEF_TABLE('BER','HHHH','HHHH_TMP');

  3  end;

  4  /

PL/SQL proceduresuccessfully completed

SQL> selectobject_id,object_name,object_type,status from user_objects where object_namelike '%HHH%';

 OBJECT_ID OBJECT_NAME                                                                     OBJECT_TYPE         STATUS

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

    115233 HHHH_PKEY                                                                       INDEX               VALID

    115232 HHHH                                                                            TABLE               VALID

    115341 HHHH_TMP                                                                        TABLE PARTITION     VALID

    115340 HHHH_TMP                                                                        TABLE PARTITION     VALID

    115339 HHHH_TMP                                                                        TABLE PARTITION     VALID

    115338 HHHH_TMP                                                                        TABLE               VALID

    115342 MLOG$_HHHH                                                                      TABLE               VALID

    115343 RUPD$_HHHH

SQL> selectcount(*) from HHHH;

  COUNT(*)

----------

  16713034

SQL> selectcount(*) from HHHH_TMP;

  COUNT(*)

----------

  16713034

SQL> exec DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('BER','HHHH','HHHH_TMP',NUM_ERRORS => :V_ERR);

PL/SQL procedure successfully completed.

SQL> print v_err

     V_ERR

----------

 0

SQL> selectobject_id,object_name,object_type,status from user_objects where object_namelike '%HHH%';

 OBJECT_ID OBJECT_NAME                                                                     OBJECT_TYPE         STATUS

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

    115344 TMP$$_HHHH_PKEY0                                                                INDEX               VALID

    115343 RUPD$_HHHH                                                                      TABLE               VALID

    115342 MLOG$_HHHH                                                                      TABLE               VALID

    115338 HHHH_TMP                                                                        TABLE               VALID

    115339 HHHH_TMP                                                                        TABLE PARTITION     VALID

    115340 HHHH_TMP                                                                        TABLE PARTITION     VALID

    115341 HHHH_TMP                                                                        TABLE PARTITION     VALID

    115232 HHHH                                                                            TABLE               VALID

    115233 HHHH_PKEY                                                                       INDEX               VALID

9 rows selected

SQL> selecttable_name,index_name,status from user_indexes where table_name='HHHH_TMP';

TABLE_NAME                     INDEX_NAME                     STATUS

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

HHHH_TMP                       TMP$$_HHHH_PKEY0               VALID

SQL> EXECDBMS_REDEFINITION.SYNC_INTERIM_TABLE('BER','HHHH','HHHH_TMP');

PL/SQL proceduresuccessfully completed

SQL> selectobject_id,object_name,object_type,status from user_objects where object_namelike '%HHH%';

 OBJECT_ID OBJECT_NAME                                                                     OBJECT_TYPE         STATUS

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

    115338 HHHH                                                                            TABLE               VALID

    115339 HHHH                                                                            TABLE PARTITION     VALID

    115340 HHHH                                                                            TABLE PARTITION     VALID

    115341 HHHH                                                                            TABLE PARTITION     VALID

    115232 HHHH_TMP                                                                        TABLE               VALID

    115344 HHHH_PKEY                                                                       INDEX               VALID

    115233 TMP$$_HHHH_PKEY0                                                                INDEX               VALID

7 rows selected