-- Reference_Partitioning

COL TABLE_NAME FOR A30
COL PARTITION_NAME FOR A30
COL HIGH_VALUE FOR A80
ALTER SESSION SET nls_date_format='DD-MON-RR HH24:MI:SS';
SET LINES 180
SET PAGES 1500
SET FEEDBACK ON
SET ECHO ON
CL SCR

DROP TABLE ORDER_ITEMS PURGE;
DROP TABLE ORDERS PURGE;

-- Creating the parent table with range partitions
CREATE TABLE ORDERS
(	
	order_id		NUMBER(12)	,
	order_date   	DATE		,
	order_mode   	VARCHAR2(8)	,
	customer_id  	NUMBER(6)	,
	order_status 	NUMBER(2)	,
	order_total  	NUMBER(8,2)	,
	sales_rep_id 	NUMBER(6)	,
	promotion_id 	NUMBER(6)	,
	CONSTRAINT   	ORDERS_PK 	PRIMARY KEY (ORDER_ID)
)
PARTITION BY RANGE (order_date)
(
	PARTITION ORDERS_2017_Q1 VALUES LESS THAN (TO_DATE('01-APR-2017','DD-MON-YYYY')),
	PARTITION ORDERS_2017_Q2 VALUES LESS THAN (TO_DATE('01-JUL-2017','DD-MON-YYYY')),
	PARTITION ORDERS_2017_Q3 VALUES LESS THAN (TO_DATE('01-OCT-2017','DD-MON-YYYY')),
	PARTITION ORDERS_2017_Q4 VALUES LESS THAN (TO_DATE('01-JAN-2018','DD-MON-YYYY'))
);

-- Creating the child table with REF partitioning
CREATE TABLE ORDER_ITEMS
(
	order_id		NUMBER(12)	NOT NULL, -- MUST
	line_item_id 	NUMBER(3)			,
	product_id   	NUMBER(6)			,
	unit_price   	NUMBER(8,2)			,
	quantity     	NUMBER(8)			,
	CONSTRAINT   	ORDER_ITEMS_FK FOREIGN KEY (ORDER_ID) REFERENCES ORDERS (ORDER_ID)
)
PARTITION BY REFERENCE (ORDER_ITEMS_FK);

-- Querying data dictionary tables
BREAK ON table_name
SELECT	table_name,partition_position,partition_name,high_value
FROM	user_tab_partitions
WHERE	table_name IN ('ORDERS','ORDER_ITEMS')
ORDER BY table_name,partition_position;

-- Dropping an old partition
ALTER TABLE ORDERS DROP PARTITION ORDERS_2017_Q1;

-- Querying data dictionary tables
BREAK ON table_name
SELECT	table_name,partition_position,partition_name,high_value
FROM	user_tab_partitions
WHERE	table_name IN ('ORDERS','ORDER_ITEMS')
ORDER BY table_name,partition_position;

-- Querying data dictionary tables
SELECT	table_name				,
		partitioning_type		,
		subpartitioning_type	,
		partition_count			,
		partitioning_key_count	,
		ref_ptn_constraint_name
FROM	user_part_tables
WHERE	table_name IN ('ORDER_ITEMS','ORDERS')
ORDER BY table_name;
