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

Source: Internet
Author: User

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)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.