Oracle Common table---Partition table conversion (900 million data volumes)

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)