There are different methods to partition an existing table. Here we will create a non partitioned table(PRODUCT) and convert it to a partitioned table using EXCHANGE option.

EXAMPLE:

Create a normal table with index and constraints and insert some data:

 

conn oranet/oranet
Connected.


CREATE TABLE PRODUCT (
  id           NUMBER,
  code         VARCHAR2(10),
  description  VARCHAR2(50),
  created_date DATE
);  2    3    4    5    6

Table created.

ALTER TABLE PRODUCT ADD (
  CONSTRAINT PRODUCT_PK PRIMARY KEY (id)
);  2    3

Table altered.

CREATE INDEX created_date_ind ON PRODUCT(created_date);

Index created.

INSERT INTO PRODUCT VALUES (1, 'ADAM', 'BOB', TO_DATE('05-JUN-2005', 'DD-MON-YYYY'));
1 row created.

INSERT INTO PRODUCT VALUES (2, 'ADAM', 'BOB', TO_DATE('05-JUN-2005', 'DD-MON-YYYY'));
1 row created.


INSERT INTO PRODUCT VALUES (4, 'ADAM', 'BOB', TO_DATE('05-AUG-2005', 'DD-MON-YYYY'));
1 row created.

COMMIT;
Commit complete.

select table_name,partitioned from dba_tabLES where table_name='PRODUCT';

TABLE_NAME                     PAR
------------------------------ ---
PRODUCT                        NO

Now create an empty interim partitioned table with similar structure as that of old table, which we will use for exchange the data from PRODUCT table. Make sure to define the one partition with maxvalue.

  CREATE TABLE PRODUCT_TEMP (
  id            NUMBER ,
  code         VARCHAR2(10),
  description  VARCHAR2(50),
  created_date DATE
)
PARTITION BY RANGE (created_date)
(PARTITION partitioned_2006 VALUES LESS THAN (MAXVALUE));  2    3    4    5    6    7    8

Table created.

ALTER TABLE PRODUCT_TEMP ADD (
  CONSTRAINT PRODUCT_TEMP_pk PRIMARY KEY (id));  2

Table altered.

CREATE INDEX PRODUCT_TEMP_ind ON PRODUCT_TEMP (created_date) LOCAL;

Index created.

Now we will use the EXCHANGE PARTITION option , which will move the data from non-partitioned table(PRODUCT) to partitioned table(PRODUCT_TEMP).

 ALTER TABLE PRODUCT_TEMP
  EXCHANGE PARTITION partitioned_2006
  WITH TABLE PRODUCT
  WITHOUT VALIDATION
  UPDATE GLOBAL INDEXES;  2    3    4    5

Table altered.

select count(*) from PRODUCT_TEMP;

  COUNT(*)
----------
         3

select count(*) from PRODUCT;

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

Once this is complete we can drop the old table and rename the new table and all it’s constraints.

drop table PRODUCT;

Table dropped.

 alter table PRODUCT_TEMP rename to PRODUCT;

Table altered.

alter table PRODUCT rename constraint PRODUCT_TEMP_PK to PRODUCT_PK;

Table altered.

ALTER INDEX PRODUCT_TEMP_PK rename to PRODUCT_PK;

Index altered.

ALTER INDEX PRODUCT_TEMP_IND rename to created_date_ind;

Index altered.

Lets split the existing partition.

select table_name,partitioned from dba_tabLES where table_name='PRODUCT';

TABLE_NAME                     PAR
------------------------------ ---
PRODUCT                        YES

 Select partition_name, high_value from user_tab_partitions where table_name = 'PRODUCT';


PARTITION_NAME                 HIGH_VALUE
------------------------------ -------------------------------------
PARTITIONED_2006               MAXVALUE

ALTER TABLE PRODUCT
  SPLIT partition partitioned_2006 AT (TO_DATE('30-JUN-2005','DD-MON-YYYY'))
  INTO (PARTITION partitioned_JUN2005,
        PARTITION partitioned_2006)
  UPDATE GLOBAL INDEXES;  2    3    4    5

Table altered.

Select partition_name, high_value from user_tab_partitions where table_name = 'PRODUCT';

PARTITION_NAME                 HIGH_VALUE
------------------------------ -----------------------------------------------------------------------------------------
PARTITIONED_2006               MAXVALUE
PARTITIONED_JUN2005            TO_DATE(' 2005-06-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

 ALTER TABLE PRODUCT
  SPLIT partition partitioned_2006    AT (TO_DATE('30-JUL-2005','DD-MON-YYYY'))
  INTO (PARTITION partitioned_JULY2005,
        PARTITION partitioned_2006)
  UPDATE GLOBAL INDEXES;  2    3    4    5

Table altered.

 Select partition_name, high_value from user_tab_partitions where table_name = 'PRODUCT';

PARTITION_NAME                 HIGH_VALUE
------------------------------ -----------------------------------------------------------------------------------------
PARTITIONED_2006               MAXVALUE
PARTITIONED_JULY2005           TO_DATE(' 2005-07-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
PARTITIONED_JUN2005            TO_DATE(' 2005-06-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

EXEC DBMS_STATS.gather_table_stats('ORANET', 'PRODUCT', cascade = > TRUE);

PL/SQL procedure successfully completed.

Select partition_name, high_value,num_rowS from user_tab_partitions where table_name = 'PRODUCT';

PARTITION_NAME                 HIGH_VALUE                                                                                  NUM_ROWS
------------------------------ ----------------------------------------------------------------------------------------- ----------
PARTITIONED_2006               MAXVALUE                                                                                           1
PARTITIONED_JULY2005           TO_DATE(' 2005-07-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')                0
PARTITIONED_JUN2005            TO_DATE(' 2005-06-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')                2

 

There are other methods like dbms_redefinition and exp imp method to partition and existing table. I will upload them very soon.

 

For any issue or if you need more information please contact support@dbaclass.com