Partitioning in Oracle- How to do partition

In this post I have given an example how we can do partition of a table. In other post I will explain which type of partition we will choose and how we can improve partitioning performance etc.
STEP 1:

Create a temporary table with same definition of table, that is to be partitioned.

CREATE TABLE big_table2 (
id NUMBER(10),
created_date DATE,
lookup_id NUMBER(10),
data VARCHAR2(50)
)
PARTITION BY RANGE (created_date)
(PARTITION big_table_2007 VALUES LESS THAN (MAXVALUE));

Add constraints

ALTER TABLE big_table2 ADD (
CONSTRAINT big_table_pk2 PRIMARY KEY (id)
);

Create Index

CREATE INDEX bita_created_date_i2 ON big_table2(created_date) LOCAL;
CREATE INDEX bita_look_fk_i2 ON big_table2(lookup_id) LOCAL;

Add foreign key,

ALTER TABLE big_table2 ADD (
CONSTRAINT bita_look_fk2
FOREIGN KEY (lookup_id)
REFERENCES lookup(id)
);


Step 2

If the existing table name is big_table then,

ALTER TABLE big_table2
EXCHANGE PARTITION big_table_2007
WITH TABLE big_table
WITHOUT VALIDATION
UPDATE GLOBAL INDEXES;

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

DROP TABLE big_table;
RENAME big_table2 TO big_table;

ALTER TABLE big_table RENAME CONSTRAINT big_table_pk2 TO big_table_pk;
ALTER TABLE big_table RENAME CONSTRAINT bita_look_fk2 TO bita_look_fk;

ALTER INDEX bita_look_fk_i2 RENAME TO bita_look_fk_i;
ALTER INDEX bita_created_date_i2 RENAME TO bita_created_date_i;


STEP 3:

Next, we split the single large partition into smaller partitions as required.Syntax is,

ALTER TABLE table_name
SPLIT PARTITION partition_name
AT range_definition
INTO (PARTITION first_partition, PARTITION second_partition)
UPDATE GLOBAL INDEXES;

For our example,

ALTER TABLE big_table
SPLIT PARTITION big_table_2007
AT (TO_DATE('31-DEC-2005 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))
INTO (PARTITION big_table_2005, PARTITION big_table_2007)
UPDATE GLOBAL INDEXES;

ALTER TABLE big_table
SPLIT PARTITION big_table_2007
AT (TO_DATE('31-DEC-2006 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))
INTO (PARTITION big_table_2006,
PARTITION big_table_2007)
UPDATE GLOBAL INDEXES;

STEP 4:

The following queries show that the partitioning was successful.

SELECT partitioned FROM user_tables WHERE table_name = 'BIG_TABLE';

SELECT partition_name, num_rows FROM user_tab_partitions WHERE table_name = 'BIG_TABLE';

Dont be hopeless. Run the following query and then select user_tables.

EXEC DBMS_STATS.gather_table_stats(USER, 'BIG_TABLE', cascade => TRUE);

STEP 5:

Merge,

ALTER TABLE table_name
MERGE PARTITIONS partition_name1,partition_name2
INTO PARTITION partition_name1 second_partition first_partition range_definition partition_name table_name ;