Partitioning existing tables in oracle database

Partitioning tables

If we have an existing table that need to be partitioned here are steps to follow

Consider a non partitioned table testx which has around 500000 rows

We can partition table based on requirements

Say we have a column called date which is in this format 20160401000000

Format is yyyymmddhhmmss


I will create a new table testx_partition with the following syntax. While creating table that will include partition name with a maxvalue clause. Here i chose partition name as TESTX_MAX

CREATE TABLE SVADAPALLI.TESTX_PARTITION
(
  RDR_KEY                         NUMBER,
  VEHICLE_KEY                     NUMBER,
  DATE                            NUMBER,
  VEHICLE_ID                      NUMBER,
)
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          80K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING
PARTITION BY RANGE (DATE)
(PARTITION TESTX_MAX VALUES LESS THAN (MAXVALUE));

Once the empty table is created, The next step is to move the data from original table to partition table

This is called partition exchange

ALTER TABLE testx_partition
  EXCHANGE PARTITION testx_max
  WITH TABLE testx
  WITHOUT VALIDATION;
 
 
  Once the above is done all values from original table will be moved to the new table.
 
 
  Now drop the original table and rename the new table.
 
  Drop table testx cascade;
 
  rename testx_partition to testx;
 
 
  Once this is done your new table will now have all your data in TESTX_MAX partition.
 
  Now that you have your data, you can easily split partition and create new partitions depending on your requirements. In my case i will do it monthly here.
 
  ALTER TABLE TESTX
 SPLIT PARTITION TESTX_MAX AT
 ('20160201000000')
 INTO (PARTITION TESTX_JAN
       TABLESPACE USERS
       PCTFREE    10
       INITRANS   1
       MAXTRANS   255
       STORAGE    (
                   MAXSIZE          UNLIMITED
                   BUFFER_POOL      DEFAULT
                   FLASH_CACHE      DEFAULT
                   CELL_FLASH_CACHE DEFAULT
                  ),
       PARTITION TESTX_MAX)
  UPDATE GLOBAL INDEXES;