Create Range Partitioned Table and View Table Details

View All Scripts Login to Run Script

Script Name Create Range Partitioned Table and View Table Details
Description Creates a range partitioned table, inserts data into the table, and then queries the data dictionary views to display table metadata.
Area SQL General / SQL Query
Referenced In Database SQL Language Reference
Contributor Oracle
Created Monday October 05, 2015

Statement 1

This table creates partitions by range.

Create a table with a number of partitions

CREATE TABLE empl_h  
  (  
     employee_id  NUMBER(6) PRIMARY KEY,  
     first_name   VARCHAR2(20),  
     last_name    VARCHAR2(25),  
     email        VARCHAR2(25),  
     phone_number VARCHAR2(20),  
     hire_date    DATE DEFAULT SYSDATE,  
     job_id       VARCHAR2(10),  
     salary       NUMBER(8, 2),  
     part_name    VARCHAR2(25)  
  ) PARTITION BY RANGE (hire_date) (  
PARTITION hire_q1 VALUES less than(to_date('01-APR-2014', 'DD-MON-YYYY')),   
PARTITION hire_q2 VALUES less than(to_date('01-JUL-2014', 'DD-MON-YYYY')),   
PARTITION hire_q3 VALUES less than(to_date('01-OCT-2014', 'DD-MON-YYYY')),   
PARTITION hire_q4 VALUES less than(to_date('01-JAN-2015', 'DD-MON-YYYY'))  
)

Table created.

Statement 2

Inserting sample rows to be included in different partitions.

Inserting rows into the partitions

INSERT INTO empl_h (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, Part_name)  
VALUES (1, 'Jane', 'Doe', 'example.com', '415.555.0100', '10-Feb-2014', '1001', 5001,'HIRE_Q1')

1 row(s) inserted.

Statement 3

Inserting sample rows to be included in different partitions.

Inserting rows into the partitions

INSERT INTO empl_h (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, Part_name)  
VALUES (2, 'John', 'Doe', 'example.net', '415.555.0101', '10-Apr-2014', '1002', 7001,'HIRE_Q2')

1 row(s) inserted.

Statement 4

Inserting sample rows to be included in different partitions.

Inserting rows into the partitions

INSERT INTO empl_h (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, Part_name)  
VALUES (3, 'Isabelle', 'Owl', 'example.org', '415.555.0102', '10-Sep-2014', '1003', 10001,'HIRE_Q3')

1 row(s) inserted.

Statement 5

Inserting sample rows to be included in different partitions.

Inserting rows into the partitions

INSERT INTO empl_h (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, Part_name)  
VALUES (4, 'Smith', 'Jones', 'example.in', '415.555.0103', '10-Dec-2014', '1004', 12001,'HIRE_Q4')

1 row(s) inserted.

Statement 6

Display the table created.

select * from empl_h


EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY PART_NAME
1 Jane Doe example.com 415.555.0100 10-FEB-14 1001 5001 HIRE_Q1
2 John Doe example.net 415.555.0101 10-APR-14 1002 7001 HIRE_Q2
3 Isabelle Owl example.org 415.555.0102 10-SEP-14 1003 10001 HIRE_Q3
4 Smith Jones example.in 415.555.0103 10-DEC-14 1004 12001 HIRE_Q4

4 rows selected.

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	PHONE_NUMBER	HIRE_DATE	JOB_ID	SALARY	PART_NAME
1	Jane	Doe	example.com	415.555.0100	10-FEB-14	1001	5001	HIRE_Q1
2	John	Doe	example.net	415.555.0101	10-APR-14	1002	7001	HIRE_Q2
3	Isabelle	Owl	example.org	415.555.0102	10-SEP-14	1003	10001	HIRE_Q3
4	Smith	Jones	example.in	415.555.0103	10-DEC-14	1004	12001	HIRE_Q4

Statement 7

Display the partition names using User_tab_partitions (data dictionary table)

SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'EMPL_H'


PARTITION_NAME
HIRE_Q1
HIRE_Q2
HIRE_Q3
HIRE_Q4

4 rows selected.

PARTITION_NAME
HIRE_Q1
HIRE_Q2
HIRE_Q3
HIRE_Q4

Statement 8

Display the partition table details using user_part_tables (data dictionary table)

SELECT TABLE_NAME, PARTITIONING_TYPE, STATUS FROM USER_PART_TABLES WHERE TABLE_NAME = 'EMPL_H'


TABLE_NAME PARTITIONING_TYPE STATUS
EMPL_H RANGE VALID

Statement 9
Creating a table by selecting a particular column from the data dictionary table "user_tab_partitions". This method is called parelleliz-ed table creation
Parallelization table creation
```
CREATE TABLE parts (p_name) AS SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'EMPL_H'
```
```
Table created.
```

TABLE_NAME	PARTITIONING_TYPE	STATUS
EMPL_H	RANGE	VALID

Statement 10

Displaying the newly created table

select * from parts


P_NAME
HIRE_Q1
HIRE_Q2
HIRE_Q3
HIRE_Q4

4 rows selected.

P_NAME
HIRE_Q1
HIRE_Q2
HIRE_Q3
HIRE_Q4

Statement 11

Comparing columns from the existing table and the newly created table, and display the information based on the comparison.

select E.HIRE_DATE,E.JOB_ID,P.p_name from empl_h E, parts P where E.Part_name = P.p_name


HIRE_DATE JOB_ID P_NAME
10-FEB-14 1001 HIRE_Q1
10-APR-14 1002 HIRE_Q2
10-SEP-14 1003 HIRE_Q3
10-DEC-14 1004 HIRE_Q4

4 rows selected.

HIRE_DATE	JOB_ID	P_NAME
10-FEB-14	1001	HIRE_Q1
10-APR-14	1002	HIRE_Q2
10-SEP-14	1003	HIRE_Q3
10-DEC-14	1004	HIRE_Q4

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database