Create partition for existing tables in Oracle: complete example

Here is a complete script to create/replace partition for existing table in Oracle database. You just need to modify your table names and run.

We have a TBL_CUSTOMERS table in schema named SCHEMANAME as script:

CREATE TABLE SCHEMANAME.TBL_CUSTOMERS
 (
   ID                 NUMBER(38)                 NOT NULL,
   CUS_NAME           VARCHAR2(255 BYTE),
   CUS_PHONE          VARCHAR2(50 BYTE),
   CUS_ADDRESS        VARCHAR2(255 BYTE),
   CUS_PROVINCE_CODE  CHAR(3 BYTE),
   CUS_PROVINCE_NAME  VARCHAR2(255 BYTE),
   CUS_DISTRICT_CODE  CHAR(5 BYTE),
   CUS_ACCOUNT_ID     NUMBER(38)                 NOT NULL,
   CREATED_DATE       DATE                       DEFAULT SYSDATE,
   CUS_WARDS_CODE     VARCHAR2(10 BYTE),
   CUS_WARDS_NAME     VARCHAR2(255 BYTE)
 )

This table as PRIMARY KEY named ID :

CREATE UNIQUE INDEX TBL_CUSTOMERS_PK ON  TBL_CUSTOMERS (ID);

ALTER TABLE  TBL_CUSTOMERS ADD (
CONSTRAINT TBL_CUSTOMERS_PK PRIMARY KEY (ID) 
USING INDEX TBL_CUSTOMERS_PK ENABLE VALIDATE);

This table already has some data, now we’ll need to create partition for this table. Just using this script below:

--create a temp table from original
CREATE TABLE SCHEMANAME.TBL_CUSTOMERS_TEMP 
 PARTITION BY RANGE(CREATED_DATE)
 (
 PARTITION P201906 VALUES LESS THAN (TO_DATE('20190701', 'YYYYMMDD')),
 PARTITION P201907 VALUES LESS THAN (TO_DATE('20190801', 'YYYYMMDD')), 
 PARTITION P201908 VALUES LESS THAN (TO_DATE('20190901', 'YYYYMMDD')), 
 PARTITION P201909 VALUES LESS THAN (TO_DATE('20191001', 'YYYYMMDD')), 
 PARTITION P201910 VALUES LESS THAN (TO_DATE('20191101', 'YYYYMMDD')), 
 PARTITION P201911 VALUES LESS THAN (TO_DATE('20191201', 'YYYYMMDD')), 
 PARTITION P201912 VALUES LESS THAN (TO_DATE('20200101', 'YYYYMMDD')) 
 ) 
 AS SELECT * FROM  SCHEMANAME.TBL_CUSTOMERS;

--create index for temp table (oraerr.com)
CREATE UNIQUE INDEX  SCHEMANAME.TBL_CUSTOMERS_PK_NEW ON  SCHEMANAME.TBL_CUSTOMERS_TEMP (ID);

--create PK on that index. No schemaname at CONSTRAINT
ALTER TABLE  SCHEMANAME.TBL_CUSTOMERS_TEMP ADD (CONSTRAINT  TBL_CUSTOMERS_PK_NEW  PRIMARY KEY (ID) USING INDEX  SCHEMANAME.TBL_CUSTOMERS_PK_NEW ENABLE VALIDATE);

--rename original table. No schemaname at new name
 ALTER TABLE SCHEMANAME.TBL_CUSTOMERS RENAME TO TBL_CUSTOMERS_OLD;

--rename newly created table. We specify the Schema name here.
ALTER TABLE SCHEMANAME.TBL_CUSTOMERS_TEMP RENAME TO TBL_CUSTOMERS;

And done.

Related:  Oracle find all tables that have specific columns

We have new table with partition and remains PK as original. Here I added SCHEMANAME for you to do this from SYS (other user than table’s user)

All you have to do is to replace:

  • the SCHEMANAME with your SCHEMA NAME,
  • TBL_CUSTOMERS with your table name,
  • CREATED_DATE with your column of partitioning,
  • and ID with your table’s primary key or index.
  • Add more partitions if needed

You can drop TBL_CUSTOMERS_OLD from now because we have TBL_CUSTOMERS ourselves.