温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

Oracle11G 在线重定义

发布时间:2020-06-16 04:21:46 来源:网络 阅读:556 作者:dbapower 栏目:关系型数据库

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


向AI问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI