1.1.tab_taobao_bill1.1.1 Check whether this table can be redefined online, no error indication can be, error will give the wrong message: exec dbms_redefinition.can_redef_table (' Ycheng ', ' p_ Tab_taobao_bill ');  1.1.2.EXPDP export table for backup (data_pump_1 directory here is/data1/dpdump) EXPDP \ '/as Sysdba\ ' Directory=data_ Pump_1 Tables=tab_taobao_bill dumpfile=ycheng_tab_taobao_bill0914.dmp Logfile=ycheng_tab_taobao_bill0914.log parallel=2; 1.1.3 Check the table space usage used by the Tab_gs_bag table (check the table space is abundant here, if the table space is insufficient, execute 1.1.4, increase tablespace) SELECT D.tablespace_name, SPACE | | ' m ' "Sum_space (M)", BLOCKS "Sum_blocks", Space-nvl (free_space, 0) | | ' m ' "Used_space (M)", ROUND ((1-NVL (free_space, 0)/SPACE) * 100, 2) | | '% ' "used_rate (%)", Free_space | | ' m ' "Free_space (M)" from (SELECT tablespace_name,round (SUM (BYTES)/(1024x768 * 1024x768), 2) space,sum (BLOCKS) Blocksfrom Dba_ Data_filesgroup by Tablespace_name) D, (SELECT tablespace_name,round (SUM (BYTES)/(1024x768 * 1024x768), 2) Free_spacefrom Dba_f Ree_spacegroup by tablespace_name) fwhere D.tablespace_name = f.tablespace_name (+) UNION allselect D.TABLESPACE_NAME, SPACE | | M"Sum_space (M)", BLOCKS Sum_blocks,used_space | | ' m ' "Used_space (M)", ROUND (NVL (used_space, 0)/SPACE * 100, 2) | | '% ' "used_rate (%)", NVL (free_space, 0) | | M ' "Free_space (M)" from (SELECT tablespace_name,round (SUM (BYTES)/(1024x768 * 1024x768), 2) space,sum (BLOCKS) Blocksfrom dba_t Emp_filesgroup by Tablespace_name) D, (SELECT tablespace_name,round (SUM (bytes_used)/(1024x768 * 1024x768), 2) Used_space,round (SUM (Bytes_free)/(1024 * 1024), 2) Free_spacefrom V$temp_space_headergroup by tablespace_name) fwhere D.tablespace_name = F.TABLESPACE_NAME (+) ORDER by 1;& nbsp; 1.1.4 Add table space alter tablespace USERS add datafile '/data1/ycmain/users20160914.dbf ' SIZE 31g; 1.2 Create a Ycheng.p_tab_taobao_bill intermediate table create table P_tab_taobao_bill (tx_logistic_id VARCHAR2 () not Null,trade_no VARCHAR2 ( (Bill_code), VARCHAR2, flag number,create_date Date default Sysdate not null,status number (2) The default 0 not null,rec_s Ite VARCHAR2 (+), Rec_man VARCHAR2 (+), Rec_bill_man VARCHAR2 (+), rec_date date,accept_date DATE,send_site VARCHAR2 (+), Send_man VARCHAR2 (1), send_date date,bl_cancel number (0), default of not null,cancel_date date, Cancel_desc VARCHAR2 (215), Bl_dispart number (1) default 0 not Null,prepare_site VARCHAR2 (+), name VARCHAR2 (120), Address VARCHAR2, Post_code VARCHAR2 (+), phone VARCHAR2 (+), mobile VARCHAR2 (+), Prov VARCHAR2 (+), City VARCHAR2 ( ), county VARCHAR2 (+), Send_start_time date,send_end_time date,item_name VARCHAR2 (.), piece number,special number, Remark VARCHAR2 (Insurance_value), Number,package_or_not number (1), Send_name VARCHAR2 (+) not null,send_address VARCHAR2 () not Null,send_post_code VARCHAR2 (), Send_phone VARCHAR2 (+), Send_mobile VARCHAR2 (+), Send_prov VARCHAR2 (+) not null,send_city VARCHAR2 (a) not null,send_county VARCHAR2 (+) not null,cancel_site VARCHAR2 (+), cancel _man VARCHAR2 (+), Rec_goods_man VARCHAR2 (+), message VARCHAR2 (+), customer_id VARCHAR2 (+), Goods_value number (10,2 ), Items_value Number,companyname VARCHAR2 (a), Send_companyname VARCHAR2 (), tOtal_weight number (10,2), Total_volume number (10,2), Pay_type VARCHAR2 (Ten), Transport_price number default 0.0, Insurance_price number,package_service VARCHAR2 (+), Package_service_price number,other_price NUMBER,total_price Number,promotion VARCHAR2 (+), Member_type VARCHAR2 (+), Business_network_no VARCHAR2 (+), Gmt_commit date,gmt_ Updated date,logistic_company_id VARCHAR2 (+), Schedule_type number,order_flag VARCHAR2 (+), Tms_service_code VARCHAR2, Logisticproviderid VARCHAR2, opt_date date,opt_man VARCHAR2 (+), Other7 number (10,2), Other9 VARCHAR2 (+), wmsaddress VARCHAR2 (+), Other8 VARCHAR2 (3), Other10 VARCHAR2 (+), Logistype VARCHAR2 (+), Eccompanyid VARCHAR2 ( ), Total_service_fee number (9,2), Buy_service_fee number (9,2), Cod_split_fee number (9,2), type VARCHAR2 (5), OrderType Number,servicetype number,seq number,send_company VARCHAR2 (+), Is_beyond number (1), GUID VARCHAR2 ($), cus_id VARCHAR2, weight_rate number (5,2), volume_rate number (5,2), least_expenses number,transport_type VARCHAR2 (20), Vist_receive VARCHAR2 (1), Vist_receive_price number (8,2), Delivery_type VARCHAR2 (2), Delivery_price number (8,2), Cod_ Type VARCHAR2 (2), Cod_price number (8,2), Back_signbill VARCHAR2 (2), Back_signbill_price number (8,2), wait_notifysend VARCHAR2 (2), Wait_notifysend_price number (8,2), sms_notify VARCHAR2 (1), Sms_notify_price number (8,2), Fuelsurcharge VARCHAR2 (1), Fuelsurcharge_price number (8,2), Duty_site VARCHAR2 (+), modifier VARCHAR2 (+), Modify_site VARCHAR2 (30), Modify_code VARCHAR2 (+), Destination VARCHAR2 (1), Favourable_bill VARCHAR2 (+), Bl_order_status Price number (10,2), Sign_status VARCHAR2 (), bl_insurance number (1), Creater_man VARCHAR2 (+), Creater_site VARCHAR2 ( Creater_code VARCHAR2 (+), Rec_bill_code VARCHAR2 (1), Bl_depute number (1), Bl_tms_control VARCHAR2 (+) partition by range (create_date) interval (numtoyminterval (1, ' Month ')) (partition tab_taobao_bill_2014_ Values less than (To_date (' 2015-01-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), partition tab_Taobao_bill_2015_01 values less than (To_date (' 2015-02-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), partition Tab_taobao_ Bill_2015_02 values less than (To_date (' 2015-03-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), partition Tab_taobao_bill_ 2015_03 values less than (To_date (' 2015-04-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), partition tab_taobao_bill_2015_04 Values less than (To_date (' 2015-05-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), partition tab_taobao_bill_2015_05 values Less Than ("To_date (' 2015-06-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), partition tab_taobao_bill_2015_06 values less than (To_date (' 2015-07-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), partition tab_taobao_bill_2015_07 values less than (to_date (' 2015-08-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), partition tab_taobao_bill_2015_08 values less than (' To_date (' 2015-09-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), partition tab_taobao_bill_2015_09 values less than (To_date (' 2015-10-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), partition tab_tAobao_bill_2015_10 values less than (To_date (' 2015-11-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), partition Tab_taobao_ Bill_2015_11 values less than (To_date (' 2015-12-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), partition Tab_taobao_bill_ 2015_12 values less than (To_date (' 2016-01-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), partition tab_taobao_bill_2016_01 Values less than (To_date (' 2016-02-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), partition tab_taobao_bill_2016_02 values Less Than ("To_date (' 2016-03-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), partition tab_taobao_bill_2016_03 values less than (To_date (' 2016-04-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), partition tab_taobao_bill_2016_04 values less than (to_date (' 2016-05-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), partition tab_taobao_bill_2016_05 values less than (' To_date (' 2016-06-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), partition tab_taobao_bill_2016_06 values less than (To_date (' 2016-07-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), partition Tab_taObao_bill_2016_07 values less than (To_date (' 2016-08-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), partition Tab_taobao_ Bill_2016_08 values less than (To_date (' 2016-09-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), partition Tab_taobao_bill_ 2016_09 values less than (To_date (' 2016-10-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), partition tab_taobao_bill_2016_10 Values less than (To_date (' 2016-11-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), partition tab_taobao_bill_2016_11 values Less Than ("To_date (' 2016-12-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), partition Tab_taobao_bill_2016_12 values less than (To_date (' 2017-01-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), partition tab_taobao_bill_2017_01 values less than (to_date (' 2017-02-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), partition tab_taobao_bill_2017_02 values less than (' To_date (' 2017-03-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), partition tab_taobao_bill_2017_03 values less than (To_date (' 2017-04-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), partition Tab_taoBao_bill_2017_04 values less than (To_date (' 2017-05-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), partition Tab_taobao_bill _2017_05 values less than (To_date (' 2017-06-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), partition tab_taobao_bill_2017_06 Values less than (To_date (' 2017-07-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), partition tab_taobao_bill_2017_07 values Less Than ("To_date (' 2017-08-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), partition tab_taobao_bill_2017_08 values less than (To_date (' 2017-09-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), partition tab_taobao_bill_2017_09 values less than (to_date (' 2017-10-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), partition tab_taobao_bill_2017_10 values less than (' To_date (' 2017-11-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), partition tab_taobao_bill_2017_11 values less than (To_date (' 2017-12-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), partition Tab_taobao_bill_2017_12 values less than (To_date (' 2018-01-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), partition TAB_TAOBAo_bill_2018_01 values less than (To_date (' 2018-02-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS ')) tablespace "USERS"; & nbsp;1.3.1. Delete the index drop, "Ycheng". Tab_taobao_bill_i ";d rop INDEX" Ycheng "." Tab_taobao_bill_i1 ";d rop INDEX" Ycheng "." Tab_taobao_bill_i2 ";d rop INDEX" Ycheng "." Tab_taobao_bill_i3 ";d rop INDEX" Ycheng "." TAB_TAOBAO_BILL_I4 ";d rop INDEX" Ycheng "." Tab_taobao_bill_i6 ";d rop INDEX" Ycheng "." Tab_taobao_bill_i7 ";d rop INDEX" Ycheng "." Tab_taobao_bill_i8 ";d rop INDEX" Ycheng "." Tab_taobao_bill_i9 "; 1.3.2. New index--create/recreate indexescreate index tab_taobao_bill_i on P_TAB_ Taobao_bill (bill_code) nologging local; create index tab_taobao_bill_i1 on P_tab_taobao_bill (CREATE_DATE, REC_ SITE, status) nologging local; create index tab_taobao_bill_i2 on P_tab_taobao_bill (Create_date, Send_site, status ) nologging local; create index tab_taobao_bill_i3 on P_tab_taobao_bill (Create_date, Bl_dispart, STATUS) nologging Local; create index TAB_TAOBAO_BILL_I4 on p_tAb_taobao_bill (customer_id) nologging local; create index tab_taobao_bill_i6 on P_tab_taobao_bill (MOBILE) Nologging local; create index tab_taobao_bill_i7 on P_tab_taobao_bill (send_mobile) nologging local; create Index tab_taobao_bill_i8 on P_tab_taobao_bill (send_phone) nologging local; create index tab_taobao_bill_i9 on P_ Tab_taobao_bill (Accept_date, Logisticproviderid) nologging local; create INDEX ind_bill_status on P_tab_taobao_ Bill ("Rec_bill_man", "STATUS") online nologging parallel 8; 1.4.1 Newly established primary key--create/recreate primary, unique and Foreign key Constraintsalter table P_tab_taobao_billadd constraint p_tab_taobao_bill_p primary KEY (TX_LOGISTIC_ID) Using Indextablespace users 1.42 Authorization Grant Select on Tab_taobao_bill to ycquery; 1.5 start online redefine: exec Dbms_redefinition.start_redef_table (' Ycheng ', ' Tab_taobao_bill ', ' P_tab_taobao_bill '); see if the gauge data is consistent select Count (1) from Ycheng. Tab_taobao_bill;select count (1) from Ycheng. P_tab_taobao_bill; 1.7. After you refresh the data using the Sync_interim_table module of the Dbms_redefinition package, the intermediate table can also see the data changes (here, when the original table data changes, Sync data can be refreshed as follows)--admin privileges Execute SQL command line execution, synchronize data on both sides exec dbms_redefinition.sync_interim_table (' Ycheng ', ' Tab_taobao_bill ', ' p_tab_ Taobao_bill ');--Administrator rights execute the SQL command line execution 1.8. End of online redefinition--Administrator rights Execute SQL command line execution, end redefining exec dbms_ Redefinition.finish_redef_table (' Ycheng ', ' Tab_taobao_bill ', ' P_tab_taobao_bill ');--Administrator rights Execute SQL command-line execution 1.9. Verify the Data Select COUNT (1) from Ycheng. Tab_taobao_bill;select count (1) from Ycheng. p_tab_taobao_bill; 1.10. View each partition data is correct select Partition_name from user_tab_partitions where table_name = ' TAB_ Taobao_bill '; select * from Tab_taobao_bill partition (Tab_taobao_bill_2016_12); 1.11. Delete the intermediate table drop Table p_tab_taobao_bill; 1.12. Modify the PRIMARY key ALTER TABLE "Ycheng". " Tab_taobao_bill "Drop constraint p_tab_taobao_bill_p cascade drop index; --create/recreate Primary, unique and foreign key Constraintsalter table Tab_taobao_billadd constraint tab_taobao_bill_p primary kEY (tx_logistic_id) using Indextablespace users 1.13 collection Tab_taobao_ The optimizer statistics for the Bill partition table and related objects need to be used by the SYS user to operate EXEC dbms_stats under Sqlplus. Gather_table_stats (ownname=> ' Ycheng ',tabname=> ' Tab_taobao_bill ',estimate_percent=>100,method_opt=> ' For all COLUMNS SIZE AUTO ', degree=>2,no_invalidate=>false,cascade=>true);
Oracle normal table to partition table (online redefinition method)
