Oracle converts a normal table to a partitioned table

db:11.2.0.30

Convert a normal table to a distinguished table

I. Rebuilding a partitioned table using the original table
Sql>create table Yoon (ID number primary key, time date);

Table created.

Sql>insert to Yoon Select rownum,created from Dba_objects;

74930 rows created.

Sql>select Count (*) from Yoon;
COUNT (*)
----------
74930

Sql>create table Yoon_new (Id,time) partition by range (time)
(partition P1 values less than (to_date (' 2011-10-01 ', ' yyyy-mm-dd '),
Partition P2 values less than (to_date (' 2012-10-01 ', ' yyyy-mm-dd ')),
Partition P3 values less than (to_date (' 2013-10-01 ', ' yyyy-mm-dd ')),
Partition P4 values less than (MAXVALUE))
As select Id,time from Yoon;

Table created.

Sql>select table_owner,table_name,partition_name from dba_tab_partitions where table_name= ' YOON_NEW ';

Table_owner table_name Partition_name
------------------------------ ------------------------------ ------------------------------
YOON yoon_new P1
YOON yoon_new P2
YOON yoon_new P3
YOON yoon_new P4

Sql>alter Table yoon Rename to Yoon_old;

Table altered.

Sql>alter table yoon_new Rename to Yoon;

Table altered.

Sql>select Count (*) from Yoon partition (P1);

COUNT (*)
----------
74445

Sql> Select COUNT (*) from Yoon partition (P2);

COUNT (*)
----------
0

Sql> Select COUNT (*) from Yoon partition (p3);

COUNT (*)
----------
0

Sql> Select COUNT (*) from Yoon Partition (p4);

COUNT (*)
----------
485

Advantages: The method is simple, easy to use, because the use of DDL statements, will not produce undo, and only a small number of redo, the efficiency is relatively high, and the completion of the table after the data has been distributed to each partition.
Cons: 1. Suitable for the modification of the table, in the idle operation, the table data volume should not be too large.
2. The table is too large to generate a large amount of undo when importing data, and it is not the way to import the data in batches.

Two. Swap partitions
Sql> CREATE TABLE Yoon (ID number primary key,time date);

Table created.

sql> INSERT INTO Yoon Select rownum,created from Dba_objects;

74930 rows created.

Sql> commit;

Commit complete.

Sql> CREATE TABLE Yoon_new (ID number primary key,time date) partition by range (time)
2 (partition P1 values less than (to_date (' 2015-10-01 ', ' yyyy-dd-mm ')),
3 partition P2 values less than (MaxValue));

Table created.

sql> ALTER table Yoon_new EXCHANGE PARTITION P1 with TABLE YOON;

Table altered.

Sql> Select COUNT (*) from Yoon;

COUNT (*)
----------
0

Sql> Select COUNT (*) from yoon_new;

COUNT (*)
----------
74930

Sql> ALTER TABLE Yoon Rename to Yoon_old;

Table altered.

sql> ALTER TABLE yoon_new rename to Yoon;

Table altered.

Sql> Select Table_owner,table_name,partition_name from dba_tab_partitions where table_name= ' YOON ';

Table_owner table_name Partition_name
------------------------------ ------------------------------ ------------------------------
YOON YOON P1
YOON YOON P2

Three. Online redefinition

Sql> CREATE TABLE Yoon (ID number primary key,time date);

Table created.

sql> INSERT INTO Yoon Select rownum,created from Dba_objects;

74930 rows created.

Sql> commit;

Commit complete.

Sql> EXEC dbms_redefinition.can_redef_table (user, ' YOON ', dbms_redefinition. CONS_USE_PK);

PL/SQL procedure successfully completed.

Sql> CREATE TABLE Yoon_new (ID number primary key,time date) partition by range (time)
2 (partition P1 values less than (to_date (' 2011-01-01 ', ' yyyy-dd-mm ')),
3 partition P2 values less than (to_date (' 2012-01-01 ', ' yyyy-dd-mm ')),
4 partition P3 values less than (to_date (' 2013-01-01 ', ' yyyy-dd-mm ')),
5 partition P4 values less than (MaxValue));

Table created.

Sql> EXEC dbms_redefinition. Start_redef_table (USER, ' YOON ', ' yoon_new ', ' ID id,time time ', dbms_redefinition. CONS_USE_PK);

PL/SQL procedure successfully completed.

Sql> EXEC dbms_redefinition. Finish_redef_table (' YOON ', ' YOON ', ' yoon_new ');

PL/SQL procedure successfully completed.

S
Ql> Select table_name from User_tables;

table_name
------------------------------
Yoon_new
YOON

Sql> Select COUNT (*) from yoon_new;

COUNT (*)
----------
74930

Sql> Select COUNT (*) from Yoon;

COUNT (*)
----------
74930

Sql> Select Table_owner,table_name,partition_name from dba_tab_partitions where table_name= ' YOON ';

Table_owner table_name Partition_name
------------------------------ ------------------------------ ------------------------------
YOON YOON P1
YOON YOON P2
YOON YOON P3
YOON YOON P4

On-line redefinition can guarantee the consistency of the data, in most of the time, the table Yoon can be normal DML operation, in the switching of the instantaneous lock table, with high availability, with strong flexibility, to meet a variety of different needs. You can create various constraints before switching, so that you do not need any additional administrative actions after switching .

Oracle converts a normal table to a partitioned table