Background introduction:
Environment: Linux 5.5 + Oracle 10.2.0.4
A normal table T, due to improper design of the previous period of time, the amount of data has reached 900 million +, the space occupied about 350G, the online redefinition of the partition table is not realistic, so take the application time window stop this table application, transformation into a partition table.
If the data volume of the T-meter is appropriate, you can choose to use the online redefinition operation, refer to: http://www.cnblogs.com/jyzhao/p/3876634.html
1. Create a partitioned table
--Create TABLE creates partition table T_part, partition starting from June 14.
CREATE TABLE T_part (...) Partition by Range (time_stamp) ( partition P20140601 values less than (To_date (' 2014-06-01 00:00:00 ', ' Syyyy-mm-dd HH 24:mi:ss ', ' Nls_calendar=gregorian ')) tablespace Dbs_d_jingyu);
Use the partition Add tool to add to June 15.
2. Set the new partition table to nologging and rename the original table T to T_old
ALTER TABLE T_part Nologging;rename T to T_old;
3. Parallel Direct Path Insertion
Insert/*+ Append Parallel (p,10) */into T_part P select/*+ Parallel (n,10) */* from T_old n;commit;
Review the execution plan for the next insert to determine the degree of parallelism that can be used.
Explain plan for Insert/*+ append parallel (p,10) */into T_part P select/*+ Parallel (n,10) */* from T_old N;
Execute Insert Script
Sql> @/home/oracle/insert~~~~~~~~~~~~~~~~~~~~~~~~~ has created 908792694 rows. Time spent: 02:09:37.94 commit completed. Time used: 00:08:13.76
4. Indexing a partitioned Table 4.1 index name of the renamed history table
Alter index pk_t rename to pk_t_bak;alter table t_old rename constraint pk_t to Pk_t_bak;alter index idx_t_2 rename to IDX _t_2_bak;alter index idx_t_3 Rename to Idx_t_3_bak;
4.2 Creating a primary key and index for the new partitioned table T_part
Create unique index pk_t on T_part (OID, Time_stamp, Serial_no, city_id) local tablespace dbs_i_jingyu nologging parallel 3 2; The index is created. Used time: 04:39:53.10alter table T_part add constraint pk_t primary key (OID, Time_stamp, Serial_no, city_id), table changed. Time used: 00:00:00.43
Create INDEX idx_t_2 on T_part (Time_stamp, Serial_no, city_id) local tablespace Dbs_i_jingyu nologging parallel 32; Index created. Time used: 02:27:49.92create index idx_t_3 on T_part (Time_stamp, city_id) local tablespace Dbs_i_jingyu nologging Parall El 32; The index has been created. Time used: 02:19:06.74
4.3 Modifying indexes and tables for Logging,noparallel
Alter index pk_t logging Noparallel;alter index idx_t_2 Logging noparallel;alter index idx_t_3 logging Noparallel;alter ta ble t_part logging;
4.4 Problems encountered
Establishing a unique index times wrong:
Sql> Create unique index pk_t on T_part (OID, Time_stamp, Serial_no, city_id) local tablespace Dbs_i_jingyu nologging PA Rallel 32;create Unique index pk_t on T_part (OID, Time_stamp, Serial_no, city_id) local tablespace Dbs_i_jingyu nologging Parallel 32ora-12801: Error signal emitted in parallel query server P000 ORA-01652: Unable to extend temp segment by 128 (in Tablespace TMP)
Workaround: Increase the size of the temporary table space
Alter tablespace TMP add tempfile '/usr3/oradata2/sysdata/tmp02.dbf ' size 30g;alter tablespace TMP add tempfile '/usr3/ora data2/sysdata/tmp03.dbf ' size 30g;alter tablespace TMP add tempfile '/usr3/oradata2/sysdata/tmp04.dbf ' size 30G;
5.rename table, Recovery of T-table related applications
Rename T_part for T, restores T-table application.
Rename T_part to T;
According to the actual situation decide whether to drop T_old completely, free space.
drop table T_old Purge;
Oracle Common table---Partition table conversion (900 million data volumes)