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

)

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

----------

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

巴特西

Oracle11G 在线重定义

最新文章

热门文章