测试表orders是一个分区表
SQL> select * from orders; ORDER_ID ORDER_DATE ORDER_MO CUSTOMER_ID ORDER_TOTAL ---------- -------------------------------------- ----------- ----------- 1 03-FEB-23 02.08.30.000000 PM online 10 100 2 26-OCT-22 02.10.06.000000 PM offline 20 150 3 21-SEP-21 02.10.11.000000 PM online 30 180 4 17-JUN-24 02.10.54.000000 PM online 50 300 5 25-NOV-20 02.11.16.000000 PM offline 60 400 6 06-OCT-22 02.12.25.000000 PM offline 70 150 7 23-FEB-23 02.13.01.000000 PM online 10 120 7 rows selected.
分区表在线重定义为非分区表: 1.验证表是否可以执行在线重定义 begin dbms_redefinition.can_redef_table( uname =>'C##LZY', tname =>'orders', options_flag => dbms_redefinition.cons_use_rowid); end; / 2.创建临时过渡表 SQL> create table orders_tmp as select *from orders where 1=2; Table created. 此时过渡表中没有数据: 3.执行表的在线重定义 begin dbms_redefinition.start_redef_table( uname =>'C##LZY', orig_table => 'orders', int_table => 'orders_tmp', options_flag => dbms_redefinition.cons_use_rowid); end; / 此时过渡表中有数据: 4.自动创建依赖对象,使用copy_table_dependents过程在过渡表上自动创建依赖对象 declare num_errors pls_integer; begin dbms_redefinition.copy_table_dependents( uname =>'C##LZY', orig_table => 'orders', int_table => 'orders_tmp', copy_indexes =>dbms_redefinition.cons_orig_params, copy_triggers => TRUE, copy_constraints => TRUE, copy_privileges => TRUE, ignore_errors => TRUE, num_errors =>num_errors); end; / 5.执行把过渡表的内容与数据源表进行同步 往源表orders写入新记录: SQL> insert into ordersvalues(8,sysdate+1,'online',10,50); 1 row created. 进行同步: begin dbms_redefinition.sync_interim_table( uname =>'C##LZY', orig_table => 'orders', int_table => 'orders_tmp'); end; / 6.执行结束在线定义过程 begin dbms_redefinition.finish_redef_table( uname =>'C##LZY', orig_table => 'orders', int_table => 'orders_tmp'); end; / 7.查看表情况 表分区已消失: 索引正常,分区索引消失: 约束正常:
|