Oracle common table-& gt; Partition Table conversion (0.9 billion data volume)

Oracle common table-> Partition Table conversion (0.9 billion data volume)

Background:

Environment: Linux 5.5 + Oracle 10.2.0.4

A common table T has no partitions due to improper preliminary design. Now, the data volume has reached more than 0.9 billion in the past few years, and the space usage is about 350 GB. It is unrealistic to redefine it as a partitioned table online, therefore, the application time window is used to stop this table application and transform it into a partitioned table.

If the data size of the T table is appropriate, you can refer to the following when online redefinition is optional:

1. Create a partition table

-- Create table: Create a partition table T_PART. partitions start from January 1, June.

Create table T_PART

(

......

)

Partition by range (time_stamp )(

Partition P20140601 values less than (TO_DATE ('00:00:00 ', 'syyyy-MM-DD HH24: MI: ss', 'nls _ CALENDAR = GREGORIAN '))

Tablespace DBS_D_JINGYU

);

Add the partition to July 15, June using the partition adding tool.

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

Alter session enable parallel dml;

Insert/* + append parallel (p, 10) */into t_part p select/* + parallel (n, 10) */* from T_old n;

Commit;

Check the execution plan of insert to make sure that the degree of parallelism is used.

Explain plan for insert/* + append parallel (p, 10) */into t_part p select/* + parallel (n, 10) */* from T_old n;

Execute the insert script

SQL> @/home/oracle/insert

~~~~~~~~~~~~~~~~~~~~~~~~~
You have created 908792694 rows.

Used time: 02: 09: 37.94

Submitted.

Used time: 00: 08: 13.76

4. Create an index for a partitioned table

4.1 rename the index name of the 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 create a primary key and index for the T_PART of the new Partition Table

Create unique index PK_T on T_PART (OID, TIME_STAMP, SERIAL_NO, CITY_ID) local tablespace DBS_ I _JINGYU nologging parallel 32;

The index has been created.

Used time: 04: 39: 53.10

Alter table T_PART add constraint PK_T primary key (OID, TIME_STAMP, SERIAL_NO, CITY_ID );

The table has been changed.

Used time: 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;

The index has been created.

Used time: 02: 27: 49.92

Create index IDX_T_3 on T_PART (TIME_STAMP, CITY_ID) local tablespace DBS_ I _JINGYU nologging parallel 32;

The index has been created.

Used time: 02: 19: 06.74

4.3 modify the index and table to logging, noparallel

Alter index PK_T logging noparallel;

Alter index IDX_T_2 logging noparallel;

Alter index IDX_T_3 logging noparallel;

Alter table T_PART logging;

4.4 Problems

An error occurred while creating a unique index:

SQL> create unique index PK_T on T_PART (OID, TIME_STAMP, SERIAL_NO, CITY_ID) local tablespace dbs_ I _jingyu nologging parallel 32;

Create unique index PK_T on T_PART (OID, TIME_STAMP, SERIAL_NO, CITY_ID) local tablespace dbs_ I _jingyu nologging parallel 32

ORA-12801: Error Signal in parallel query server P000

ORA-01652: unable to extend temp segments through 128 (in table space TMP)

Solution: increase the size of the temporary tablespace.

Alter tablespace TMP add tempfile '/usr3/oradata2/sysdata/tmp02.dbf' size 30G;

Alter tablespace TMP add tempfile '/usr3/oradata2/sysdata/tmp03.dbf' size 30G;

Alter tablespace TMP add tempfile '/usr3/oradata2/sysdata/tmp04.dbf' size 30G;

5. rename TABLE: Restore applications related to table T

Rename T_PART is T, and the T table application is restored.

Rename T_PART to T;

Determine whether to completely drop T_OLD and release the space based on the actual situation.

Drop table T_OLD purge;