How to make System partition in Oracle 11g?

Question

I am very new to database .I would like to do system partition for a existing large database table in oracle. Can someone suggest How i can achieve system partition for existing table in oracle database ?

Note i am only looking for System Partition not looking for range or Hash or composite Partition.

Answer 1

As far as I know, an existing table cannot be partitioned. You will have to recreate it. There is an Oracle package called dbms_redefinition for just this scenario (see https://docs.oracle.com/database/121/ARPLS/d_redefi.htm for details), but I will provide a very simple example without using this package.

Imagine you have the following non-partitioned table:

create table T_TABLE
(
  pkey         NUMBER not null,
  t_data       VARCHAR2(250) not null,
  partitionkey NUMBER not null
);

If you want to partition that table, the first step would be to rename the table:

alter table t_table rename to old_table;

Then, create the new table

create table T_TABLE
(
  pkey         NUMBER not null,
  t_data       VARCHAR2(250) not null,
  partitionkey NUMBER not null
)
partition by system
(
   partition p1 tablespace users,
   partition p2 tablespace users,
   partition p3 tablespace users
);     

Now you can insert your table rows from the old table into the new table. Your application/sql needs to tell the server in which partition to insert. For example, like this:

insert into t_table partition (p1) select * from old_table where partitionkey = 1;    
insert into t_table partition (p2) select * from old_table where partitionkey = 2;
insert into t_table partition (p3) select * from old_table where partitionkey = 3;        
commit;

Now you can drop your old table.

drop table old_table;