Oracle11G online redefinition
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-01-01)) tablespace tbs1
Partition p2 values less than (to_date ('2017-01-01-01-01)) 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
