Oracle Partitioning Types - II (Single Level Partitions)

Syntax examples on how to create partition tables:
1.) Range Partitioning:

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
data_size NUMBER,
state VARCHAR2(20))
PARTITION BY RANGE (load_date)
( PARTITION data_01_2009 VALUES LESS THAN (TO_DATE('01-FEB-2009','DD-MON-YYYY')) TABLESPACE DATA_01,
PARTITION data_02_2009 VALUES LESS THAN (TO_DATE('01-MAR-2009','DD-MON-YYYY')) TABLESPACE DATA_02,
PARTITION data_03_2009 VALUES LESS THAN (TO_DATE('01-APR-2009','DD-MON-YYYY')) TABLESPACE DATA_03,
PARTITION data_04_2009 VALUES LESS THAN (TO_DATE('01-MAY-2009','DD-MON-YYYY')) TABLESPACE DATA_04
);

Table created.

This create Date range based partition each for the month of JAN, FEB, MAR & APR 2009.

2.) Hash Partitioning

Oracle apply's a hashing algorithm to the column selected on partitions, we can provide the number of partitions we want or also the names of partitions.

SQL> DROP TABLE DATA_LOAD;
Table dropped.

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
data_size NUMBER,
state VARCHAR2(20))
PARTITION BY HASH (source_id)
( PARTITION data_01_2009 TABLESPACE DATA_01,
PARTITION data_02_2009 TABLESPACE DATA_02,
PARTITION data_03_2009 TABLESPACE DATA_03,
PARTITION data_04_2009 TABLESPACE DATA_04
);
Table created.

Alternatively,
SQL> DROP TABLE DATA_LOAD;
Table dropped.

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
data_size NUMBER,
state VARCHAR2(20))
PARTITION BY HASH (source_id) PARTITIONS 4;
Table created.

3.) List Partitioning

SQL> DROP TABLE DATA_LOAD;
Table dropped.

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
data_size NUMBER,
state VARCHAR2(20))
PARTITION BY LIST (state)
(PARTITION NORTH_01 VALUES ('DELHI', 'JAMMU KASHMIR', 'PUNJAB', 'HARYANA'),
PARTITION WEST_01 VALUES ('RAJASTHAN', 'GUJRAT', 'MAHARASTRA'),
PARTITION SOUTH_01 VALUES ('TAMIL NADU', 'KERELA', 'KARNATAKA'),
PARTITION EAST_01 VALUES ('ASSAM', 'WEST BENGAL', 'NAGALAND'),
PARTITION NO_REGION VALUES (NULL),
PARTITION OTHER VALUES (DEFAULT)
);
Table created.

Part I
Part III
Part IV
Part V
Part VI
Part VII