Manage partitioned tables

Go back to OCM 12c Preparation Project 

Hands On Lab – Index

[1] Range Partition
[2] Interval Partition (extension of Range Partition)
[3] List Partition
[4] Hash Partition
[5] Reference Partition
[6] System Partition
[7] Virtual Column-Based Partitioning
[8] Range-Hash Partition
[9] Range-list Partitioning
[10] Range-Rash Partitioning
[11] List-range Partition
[12] List-Hash Partitioning
[13] List-List Partitioning
[14] Interval-List Partitioning.
[15] Interval-Range Partitioning
[16] Interval-Hash Partitioning
[17] Perform partition maintenance operations
Adding a new partition
Dropping a partition
Coalescing partitions
Exchange partitions
Merging partitions
Renaming partitions
Moving partitions
Splitting partitions
Truncating partitions

Partition:

[1] Range Partition

Database VLDB and Partitioning Guide
-> Creating a Range-Partitioned Table

Example 4-1 Creating a range-partitioned table

create tablespace &tablespace datafile '/u03/app/oracle/oradata/cdb2/pdb2/&tablespace..dbf' 
size 5M autoextend on

CREATE TABLE sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))
TABLESPACE tsa
, PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))
TABLESPACE tsb
, PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
TABLESPACE tsc
, PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
TABLESPACE tsd
);

Example 4-2 Creating a range-partitioned table with LOGGING and ENABLE ROW MOVEMENT

drop table sales;

CREATE TABLE sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
STORAGE (INITIAL 100K NEXT 50K) LOGGING
PARTITION BY RANGE (time_id)
( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))
TABLESPACE tsa STORAGE (INITIAL 20K NEXT 10K)
, PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))
TABLESPACE tsb
, PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
TABLESPACE tsc
, PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
TABLESPACE tsd
)
ENABLE ROW MOVEMENT;

Example 4-3 Creating a range-partitioned global index table

CREATE INDEX amount_sold_ix ON sales(amount_sold)
GLOBAL PARTITION BY RANGE(amount_sold)
( PARTITION p_100 VALUES LESS THAN (100)
, PARTITION p_1000 VALUES LESS THAN (1000)
, PARTITION p_10000 VALUES LESS THAN (10000)
, PARTITION p_100000 VALUES LESS THAN (100000)
, PARTITION p_1000000 VALUES LESS THAN (1000000)
, PARTITION p_greater_than_1000000 VALUES LESS THAN (maxvalue)
);


$ sqlplus HR/HR@pdb2
create table tbl_range_part
(id number,
name varchar2(10),
surname varchar2(10),
ddate date)
partition by range(ddate)
(partition part_2015 values less than (to_date('01-jan-2016','dd-mon-yyyy')),
partition part_jan_2016 values less than (to_date('01-feb-2016','dd-mon-yyyy')),
partition part_feb_2016 values less than (to_date('01-mar-2016','dd-mon-yyyy')),
partition part_mar_2016 values less than (to_date('01-apr-2016','dd-mon-yyyy')),
partition part_apr_2016 values less than (to_date('01-may-2016','dd-mon-yyyy')),
partition part_others values less than (maxvalue));

insert into tbl_range_part values(&1,'&2','&3',&4);
select * from tbl_range_part;
select * from tbl_range_part partition ( &1 );
select partition_name from user_tab_partitions where table_name = 'TBL_RANGE_PART';

alter table tbl_range_part add partition part_jan_2019 values less than (to_date('01-feb-2019','dd-mon-yyyy'));

ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition

--SPLIT
alter table tbl_range_part split partition for (to_date('01-mar-2019','dd-mon-yyyy'))
at (to_date('01-mar-2019','dd-mon-yyyy')) into (PARTITION part_feb_2019,PARTITION DEFAULT_PART);

--MERGE
alter table tbl_range_part merge partitions PART_JAN_2016,PART_FEB_2016 into partition PART_JF_2016
update indexes;


create table tbl_range_part2
(id number,
name varchar2(10),
surname varchar2(10),
year number,
month number,
day number)
partition by range ( year , month)
(partition jan_2016 values less than (2016,2),
partition feb_2016 values less than (2016,03)) 
/

desc tbl_range_part2
select table_name,PARTITIONING_TYPE,partition_count,status from user_part_tables;

--ADD
alter table tbl_range_part2 add partition mar_2019 values less than (2019,04);

--RENAME
alter table tbl_range_part2 rename partition mar_2019 to march2019;

--TRUNCATE
alter table tbl_range_part2 truncate partition march2019;

--DROP
alter table tbl_range_part2 drop partition march2019;

[2] Interval Partition (extension of Range Partition)

Database VLDB and Partitioning Guide
--> Creating Interval-Partitioned Tables

Example 4-4 Creating an interval-partitioned table
CREATE TABLE interval_sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
) 
PARTITION BY RANGE (time_id) 
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2008', 'DD-MM-YYYY')),
PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2009', 'DD-MM-YYYY')),
PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2009', 'DD-MM-YYYY')),
PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2010', 'DD-MM-YYYY')) );


create table tbl_interval_part
(id number,
name varchar2(20),
surname varchar2(20),
ddate date)
partition by range (ddate)
interval ( numtoyminterval ( 1 ,'MONTH'))
(partition part1 values less than (to_date('05052016','ddmmyyyy')))


select partition_name,high_value from user_tab_partitions where table_name = 'TBL_INTERVAL_PART'
order by partition_position;

insert into tbl_interval_part values (&1,'&2','&3',&4);

col partition_name format a20
col high_value format a50 word_wrapped
select partition_name,high_value from user_tab_partitions
where table_name ='TBL_INTERVAL_PART'
order by partition_position

--To disable an interval partition.
alter table tbl_interval_part set interval (); 

set long 10000
set pagesize 10000
select dbms_metadata.get_ddl('TABLE','TBL_INTERVAL_PART') from dual;

--Add partition
LOCK TABLE tbl_interval_part PARTITION FOR(TO_DATE('01-JAN-2019','dd-MON-yyyy')) IN SHARE MODE;

ALTER TABLE tbl_interval_part SET INTERVAL (NUMTOYMINTERVAL(1,'MONTH'));

LOCK TABLE tbl_interval_part PARTITION FOR(TO_DATE('01-FEB-2019','dd-MON-yyyy')) IN SHARE MODE;

ALTER TABLE tbl_interval_part SET INTERVAL (NUMTOYMINTERVAL(1,'MONTH'));


Database SQL Language Reference
--> NUMTODSINTERVAL : converts n to an INTERVAL DAY TO SECOND literal. 
The value for interval_unit specifies the unit of n and must resolve to one of the following string values:
'DAY'
'HOUR'
'MINUTE'
'SECOND'

--> NUMTOYMINTERVAL : NUMTOYMINTERVAL converts number n to an INTERVAL YEAR TO MONTH literal.
The value for interval_unit specifies the unit of n and must resolve to one of the following string values:
'YEAR'
'MONTH'

--Enable range partition to interval range
alter table tbl_interval_part set interval (NUMTODSINTERVAL ( 1 ,'DAY')); 

set long 10000
set pagesize 10000
select dbms_metadata.get_ddl('TABLE','TBL_INTERVAL_PART') from dual;


[3] List Partition

Database VLDB and Partitioning Guide
--> Creating a List-Partitioned Table

Example 4-6 Creating a list-partitioned table

CREATE TABLE q1_sales_by_region
(deptno number, 
deptname varchar2(20),
quarterly_sales number(10, 2),
state varchar2(2))
PARTITION BY LIST (state)
(PARTITION q1_northwest VALUES ('OR', 'WA'),
PARTITION q1_southwest VALUES ('AZ', 'UT', 'NM'),
PARTITION q1_northeast VALUES ('NY', 'VM', 'NJ'),
PARTITION q1_southeast VALUES ('FL', 'GA'),
PARTITION q1_northcentral VALUES ('SD', 'WI'),
PARTITION q1_southcentral VALUES ('OK', 'TX'));

Example 4-7 Creating a list-partitioned table with a default partition

CREATE TABLE sales_by_region (item# INTEGER, qty INTEGER, 
store_name VARCHAR(30), state_code VARCHAR(2),
sale_date DATE)
STORAGE(INITIAL 10K NEXT 20K) TABLESPACE tsa 
PARTITION BY LIST (state_code) 
(
PARTITION region_east
VALUES ('MA','NY','CT','NH','ME','MD','VA','PA','NJ')
STORAGE (INITIAL 8M) 
TABLESPACE tsb,
PARTITION region_west
VALUES ('CA','AZ','NM','OR','WA','UT','NV','CO')
NOLOGGING,
PARTITION region_south
VALUES ('TX','KY','TN','LA','MS','AR','AL','GA'),
PARTITION region_central 
VALUES ('OH','ND','SD','MO','IL','MI','IA'),
PARTITION region_null
VALUES (NULL),
PARTITION region_unknown
VALUES (DEFAULT)
);


create table tbl_list_part
(id number,
name varchar2(20),
surname varchar2(20),
course varchar2(20))
tablespace tsa
partition by list (course)
(partition part_sql values ('SQL'),
partition part_plsql values ('PLSQL'),
partition part_oracle values ('ORACLE'),
partition C values (DEFAULT))

insert into tbl_list_part values (&1,'&2','&3','&4')

select * from tbl_list_part;

select * from tbl_list_part partition ( &1 );

select partition_name from user_tab_partitions where table_name='TBL_LIST_PART';

--ADD PARTITION
alter table tbl_list_part add partition values ('ORACLE19C');
ORA-14323: cannot add partition when DEFAULT partition exists

--SPLIT PARTITION
alter table tbl_list_part split PARTITION for ('ORACLE19C') VALUES ('ORACLE19C')
into (partition part_oracle19c, partition part_rest);


[4] Hash Partition

Database VLDB and Partitioning Guide
--> Creating a Hash Partitioned Table

CREATE TABLE scubagear
(id NUMBER,
name VARCHAR2 (60))
PARTITION BY HASH (id)
PARTITIONS 4 
STORE IN (tsa, tsb, tsc, tsd);

CREATE TABLE departments_hash (department_id NUMBER(4) NOT NULL, 
department_name VARCHAR2(30))
PARTITION BY HASH(department_id) PARTITIONS 16;

CREATE TABLE departments_hash2 (department_id NUMBER(4) NOT NULL, 
department_name VARCHAR2(30))
STORAGE (INITIAL 10K)
PARTITION BY HASH(department_id)
(PARTITION p1 TABLESPACE tsa, PARTITION p2 TABLESPACE tsb,
PARTITION p3 TABLESPACE tsc, PARTITION p4 TABLESPACE tsd);

CREATE INDEX loc_dept_ix ON departments_hash(department_id) LOCAL;

Example 4-5 Creating a hash partitioned global index
CREATE INDEX hgidx ON scubagear (id) GLOBAL
PARTITION BY HASH (id)
(PARTITION p1 TABLESPACE tsa,
PARTITION p2 TABLESPACE tsb,
PARTITION p3 TABLESPACE tsc,
PARTITION p4 TABLESPACE tsd);


create table tbl_hash_part
partition by hash(object_id)
partitions 8
tablespace tsa
as
select * from dba_objects;

select table_name,partitioning_type,partition_count,status from user_part_tables 
where table_name = 'TBL_HASH_PART';

select table_name,partition_name from user_tab_partitions
where table_name = 'TBL_HASH_PART';

--add 
alter table tbl_hash_part add partition;

--Coalescing 
alter table tbl_hash_part COALESCE PARTITION;

create table tbl_hash_part1
partition by hash (object_id)
partitions 8
store0 in (tsa,tsb,tsc,tsd)
as
select * from dba_objects;

select table_name,partition_name,tablespace_name from user_tab_partitions 
where table_name = 'TBL_HASH_PART1';


create table tbl_hash_part2
partition by hash (object_id)
(partition part1 tablespace tsa,
partition part2 tablespace tsb,
partition part3 tablespace tsc,
partition part4 tablespace tsd)
as
select * from dba_objects
/

select table_name,partition_name,tablespace_name from user_tab_partitions 
where table_name ='TBL_HASH_PART2';
select table_name,partition_name,tablespace_name,partition_position,num_rows 
from user_tab_partitions where table_name ='TBL_HASH_PART2'

EXEC dbms_stats.gather_table_stats(USER,'TBL_HASH_PART2');

select table_name,partition_name,tablespace_name,partition_position,num_rows 
from user_tab_partitions where table_name ='TBL_HASH_PART2'

select table_name,partitioning_type,partition_count,status from user_part_tables 
where table_name in ('TBL_HASH_PART','TBL_HASH_PART1','TBL_HASH_PART2');


[5] Reference Partition

Database VLDB and Partitioning Guide
--> Creating Reference-Partitioned Tables

Example 4-8 Creating reference-partitioned tables

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 Q1_2005 VALUES LESS THAN (TO_DATE('01-APR-2005','DD-MON-YYYY')),
PARTITION Q2_2005 VALUES LESS THAN (TO_DATE('01-JUL-2005','DD-MON-YYYY')),
PARTITION Q3_2005 VALUES LESS THAN (TO_DATE('01-OCT-2005','DD-MON-YYYY')),
PARTITION Q4_2005 VALUES LESS THAN (TO_DATE('01-JAN-2006','DD-MON-YYYY'))
);

CREATE TABLE order_items
( order_id NUMBER(12) NOT NULL,
line_item_id NUMBER(3) NOT NULL,
product_id NUMBER(6) NOT NULL,
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);


create table tbl_ref_part 
(id number primary key,
name varchar2(20),
surname varchar2(20))
partition by range (id)
(partition p1 values less than (10), 
partition p2 values less than (20));


create table tbl_ref_part_child
(id number primary key,
name varchar2(20),
constraint fk_id foreign key (id) references tbl_ref_part(id)
)
partition by reference (fk_id);

select table_name,partition_name from user_tab_partitions 
where table_name LIKE 'TBL_REF%';

select table_name,partitioning_type,ref_ptn_constraint_name 
from user_part_tables where table_name like 'TBL_REF%';


Database VLDB and Partitioning Guide
--> Creating Interval-Reference Partitioned Tables

CREATE TABLE par(pk INT CONSTRAINT par_pk PRIMARY KEY, i INT)
PARTITION BY RANGE(i) INTERVAL (10)
(PARTITION p1 VALUES LESS THAN (10));

CREATE TABLE chi(fk INT NOT NULL, i INT,
CONSTRAINT chi_fk FOREIGN KEY(fk) REFERENCES par(pk))
PARTITION BY REFERENCE(chi_fk);

INSERT INTO par VALUES(15, 15);
INSERT INTO par VALUES(25, 25);
INSERT INTO par VALUES(35, 35);


SELECT table_name, partition_position, high_value, interval
FROM USER_TAB_PARTITIONS WHERE table_name IN ('PAR', 'CHI')
ORDER BY 1, 2;

ALTER TABLE par SPLIT PARTITION FOR (25) AT (25)
INTO (partition x, partition y);

SELECT table_name, partition_position, high_value, interval
FROM USER_TAB_PARTITIONS WHERE table_name IN ('PAR', 'CHI')
ORDER BY 1, 2; 

[6] System Partition

create table tbl_sys_part
(id number,
name varchar2(20),
surname varchar2(20))
partition by system
(partition part1 tablespace tsa, 
partition part2 tablespace tsb);

select partitioning_type from user_part_tables where table_name like '%SYS%';

SQL> !oerr ora 14701
14701, 00000, "partition-extended name or bind variable must be used for 
DMLs on tables partitioned by the System method"
// *Cause: User attempted not to use partition-extended syntax
// for a table partitioned by the System method
// *Action: Must use of partition-extended syntax in contexts mentioned above.

insert into tbl_sys_part partition (&1) values (&1,'&2','&3');

select * from tbl_sys_part;

delete from tbl_sys_part where id=1;

commit;

select * from tbl_sys_part;

--DOC HOL
CREATE TABLE system_part_tab1 (number1 integer, number2 integer) 
PARTITION BY SYSTEM
( PARTITION p1,
PARTITION p2,
PARTITION p3,
PARTITION p_last);

ALTER TABLE system_part_tab1 ADD 
PARTITION p4,
PARTITION p5,
PARTITION p6
BEFORE PARTITION p_last;

SELECT SUBSTR(TABLE_NAME,1,18) table_name, TABLESPACE_NAME, 
SUBSTR(PARTITION_NAME,1,16) partition_name 
FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='SYSTEM_PART_TAB1';

[7] Virtual Column-Based Partitioning

Database VLDB and Partitioning Guide
--> Using Virtual Column-Based Partitioning


Example 4-20 Creating a table with a virtual column for the subpartitioning key

CREATE TABLE sales_virt
( prod_id NUMBER(6) NOT NULL
, cust_id NUMBER NOT NULL
, time_id DATE NOT NULL
, channel_id CHAR(1) NOT NULL
, promo_id NUMBER(6) NOT NULL
, quantity_sold NUMBER(3) NOT NULL
, amount_sold NUMBER(10,2) NOT NULL
, total_amount AS (quantity_sold * amount_sold)
)
PARTITION BY RANGE (time_id) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
SUBPARTITION BY RANGE(total_amount)
SUBPARTITION TEMPLATE
( SUBPARTITION p_small VALUES LESS THAN (1000)
, SUBPARTITION p_medium VALUES LESS THAN (5000)
, SUBPARTITION p_large VALUES LESS THAN (10000)
, SUBPARTITION p_extreme VALUES LESS THAN (MAXVALUE)
)
(PARTITION sales_before_2007 VALUES LESS THAN
(TO_DATE('01-JAN-2007','dd-MON-yyyy'))
)
ENABLE ROW MOVEMENT
PARALLEL NOLOGGING;


set linesize 150
set pagesize 15000

create table tbl_virtual_part
(id number,
name varchar2(20),
surname varchar2(20),
price number,
quantity number,
total_value as (price * quantity) virtual) 
partition by range (total_value)
( partition part1 values less than (1000),
partition part2 values less than (2000));


insert into tbl_virtual_part (id,name,surname,price,quantity) values(1,'first','row',100,8);

insert into tbl_virtual_part (id,name,surname,price,quantity) values(2,'Second','row',300,6);

select * from tbl_virtual_part;
select * from tbl_virtual_part partition(part1);
select * from tbl_virtual_part partition(part2);


[8] Range-Hash Partition

Database VLDB and Partitioning Guide
--> Specifying Composite Partitioning When Creating Tables
-----> About Creating Composite Range-Hash Partitioned Tables

Example 4-9 Creating a composite range-hash partitioned table using one STORE IN clause

CREATE TABLE sales_range_hash
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id) SUBPARTITION BY HASH (cust_id)
SUBPARTITIONS 8 STORE IN (tsa, tsb, tsc, tsd)
( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))
, PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))
, PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
, PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
);


--> Creating a Composite Range-Hash Partitioned Table With Varying Tablespaces

CREATE TABLE employees_range_hash (department_id NUMBER(4) NOT NULL, 
last_name VARCHAR2(25), job_id VARCHAR2(10)) 
PARTITION BY RANGE(department_id) SUBPARTITION BY HASH(last_name)
SUBPARTITIONS 8 STORE IN (tsa, tsb, tsc, tsd)
(PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (2000) STORE IN (tsa, tsb, tsc, tsd),
PARTITION p3 VALUES LESS THAN (MAXVALUE) 
(SUBPARTITION p3_s1 TABLESPACE tsa,
SUBPARTITION p3_s2 TABLESPACE tsb));

CREATE INDEX employee_ix ON employees_range_hash(department_id)
LOCAL STORE IN (tsa, tsb, tsc,tsd);


--HOL 
create table tbl_range_hash_part
tablespace tsa
partition by range (object_id)
subpartition by hash(namespace)
subpartitions 4
(
partition part1 values less than (1000),
partition part2 values less than (10000),
partition part3 values less than (30000),
partition part4 values less than (maxvalue))
as
select * from dba_objects

exec dbms_stats.gather_table_stats(USER,'TBL_RANGE_HASH_PART');

select table_name,partition_name,subpartition_count,num_rows,blocks from user_tab_partitions
where table_name = 'TBL_RANGE_HASH_PART'

select table_name,partition_name,subpartition_name from user_tab_subpartitions
where table_name ='TBL_RANGE_HASH_PART';

[9] Range-list Partitioning

Database VLDB and Partitioning Guide
--> Creating a Composite Range-List Partitioned Table

Example 4-10 Creating a composite range-list partitioned table

CREATE TABLE quarterly_regional_sales
(deptno number, item_no varchar2(20),
txn_date date, txn_amount number, state varchar2(2))
TABLESPACE tsa
PARTITION BY RANGE (txn_date)
SUBPARTITION BY LIST (state)
(PARTITION q1_1999 VALUES LESS THAN (TO_DATE('1-APR-1999','DD-MON-YYYY'))
(SUBPARTITION q1_1999_northwest VALUES ('OR', 'WA'),
SUBPARTITION q1_1999_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q1_1999_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q1_1999_southeast VALUES ('FL', 'GA'),
SUBPARTITION q1_1999_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q1_1999_southcentral VALUES ('OK', 'TX')
),
PARTITION q2_1999 VALUES LESS THAN ( TO_DATE('1-JUL-1999','DD-MON-YYYY'))
(SUBPARTITION q2_1999_northwest VALUES ('OR', 'WA'),
SUBPARTITION q2_1999_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q2_1999_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q2_1999_southeast VALUES ('FL', 'GA'),
SUBPARTITION q2_1999_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q2_1999_southcentral VALUES ('OK', 'TX')
),
PARTITION q3_1999 VALUES LESS THAN (TO_DATE('1-OCT-1999','DD-MON-YYYY'))
(SUBPARTITION q3_1999_northwest VALUES ('OR', 'WA'),
SUBPARTITION q3_1999_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q3_1999_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q3_1999_southeast VALUES ('FL', 'GA'),
SUBPARTITION q3_1999_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q3_1999_southcentral VALUES ('OK', 'TX')
),
PARTITION q4_1999 VALUES LESS THAN ( TO_DATE('1-JAN-2000','DD-MON-YYYY'))
(SUBPARTITION q4_1999_northwest VALUES ('OR', 'WA'),
SUBPARTITION q4_1999_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q4_1999_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q4_1999_southeast VALUES ('FL', 'GA'),
SUBPARTITION q4_1999_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q4_1999_southcentral VALUES ('OK', 'TX')
)
);

--> Creating a Composite Range-List Partitioned Table Specifying Tablespaces

CREATE TABLE sample_regional_sales
(deptno number, item_no varchar2(20),
txn_date date, txn_amount number, state varchar2(2))
PARTITION BY RANGE (txn_date)
SUBPARTITION BY LIST (state)
(PARTITION q1_1999 VALUES LESS THAN (TO_DATE('1-APR-1999','DD-MON-YYYY'))
TABLESPACE tsa
(SUBPARTITION q1_1999_northwest VALUES ('OR', 'WA'),
SUBPARTITION q1_1999_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q1_1999_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q1_1999_southeast VALUES ('FL', 'GA'),
SUBPARTITION q1_others VALUES (DEFAULT) TABLESPACE tsa
),
PARTITION q2_1999 VALUES LESS THAN ( TO_DATE('1-JUL-1999','DD-MON-YYYY'))
TABLESPACE tsb
(SUBPARTITION q2_1999_northwest VALUES ('OR', 'WA'),
SUBPARTITION q2_1999_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q2_1999_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q2_1999_southeast VALUES ('FL', 'GA'),
SUBPARTITION q2_1999_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q2_1999_southcentral VALUES ('OK', 'TX')
),
PARTITION q3_1999 VALUES LESS THAN (TO_DATE('1-OCT-1999','DD-MON-YYYY'))
TABLESPACE tsc
(SUBPARTITION q3_1999_northwest VALUES ('OR', 'WA'),
SUBPARTITION q3_1999_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q3_others VALUES (DEFAULT) TABLESPACE tsc
),
PARTITION q4_1999 VALUES LESS THAN ( TO_DATE('1-JAN-2000','DD-MON-YYYY'))
TABLESPACE tsd
);


--HOL

create table tbl_range_list_part
partition by range(object_id)
subpartition by list(object_type)
(partition part1 values less than (100)
( subpartition part1_sub_index values ('INDEX'),
subpartition part1_sub_table values ('TABLE')),
partition part2 values less than (200)
( subpartition part2_sub_index values ('INDEX'),
subpartition part2_sub_table values ('TABLE')),
partition part3 values less than (maxvalue))
as
select * from dba_objects where object_type in ('INDEX','TABLE');


select count(*) from tbl_range_list_part subpartition(part1_sub_index);
select count(*) from tbl_range_list_part subpartition(part2_sub_table);

[10] Range-Range Partitioning

Database VLDB and Partitioning Guide
--> Creating Composite Range-Range Partitioned Tables

Example 4-11 Creating a composite range-range partitioned table

CREATE TABLE shipments
( order_id NUMBER NOT NULL
, order_date DATE NOT NULL
, delivery_date DATE NOT NULL
, customer_id NUMBER NOT NULL
, sales_amount NUMBER NOT NULL
)
PARTITION BY RANGE (order_date)
SUBPARTITION BY RANGE (delivery_date)
( PARTITION p_2006_jul VALUES LESS THAN (TO_DATE('01-AUG-2006','dd-MON-yyyy'))
( SUBPARTITION p06_jul_e VALUES LESS THAN (TO_DATE('15-AUG-2006','dd-MON-yyyy'))
, SUBPARTITION p06_jul_a VALUES LESS THAN (TO_DATE('01-SEP-2006','dd-MON-yyyy'))
, SUBPARTITION p06_jul_l VALUES LESS THAN (MAXVALUE)
)
, PARTITION p_2006_aug VALUES LESS THAN (TO_DATE('01-SEP-2006','dd-MON-yyyy'))
( SUBPARTITION p06_aug_e VALUES LESS THAN (TO_DATE('15-SEP-2006','dd-MON-yyyy'))
, SUBPARTITION p06_aug_a VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
, SUBPARTITION p06_aug_l VALUES LESS THAN (MAXVALUE)
)
, PARTITION p_2006_sep VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
( SUBPARTITION p06_sep_e VALUES LESS THAN (TO_DATE('15-OCT-2006','dd-MON-yyyy'))
, SUBPARTITION p06_sep_a VALUES LESS THAN (TO_DATE('01-NOV-2006','dd-MON-yyyy'))
, SUBPARTITION p06_sep_l VALUES LESS THAN (MAXVALUE)
)
, PARTITION p_2006_oct VALUES LESS THAN (TO_DATE('01-NOV-2006','dd-MON-yyyy'))
( SUBPARTITION p06_oct_e VALUES LESS THAN (TO_DATE('15-NOV-2006','dd-MON-yyyy'))
, SUBPARTITION p06_oct_a VALUES LESS THAN (TO_DATE('01-DEC-2006','dd-MON-yyyy'))
, SUBPARTITION p06_oct_l VALUES LESS THAN (MAXVALUE)
)
, PARTITION p_2006_nov VALUES LESS THAN (TO_DATE('01-DEC-2006','dd-MON-yyyy'))
( SUBPARTITION p06_nov_e VALUES LESS THAN (TO_DATE('15-DEC-2006','dd-MON-yyyy'))
, SUBPARTITION p06_nov_a VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
, SUBPARTITION p06_nov_l VALUES LESS THAN (MAXVALUE)
)
, PARTITION p_2006_dec VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
( SUBPARTITION p06_dec_e VALUES LESS THAN (TO_DATE('15-JAN-2007','dd-MON-yyyy'))
, SUBPARTITION p06_dec_a VALUES LESS THAN (TO_DATE('01-FEB-2007','dd-MON-yyyy'))
, SUBPARTITION p06_dec_l VALUES LESS THAN (MAXVALUE)
)
);

--HOL
create table tbl_range_range_part
partition by range(object_id)
subpartition by range(namespace)
(partition part1 values less than (1000)
( subpartition part1_sub1 values less than (4),
subpartition part1_sub2 values less than (maxvalue)),
partition part2 values less than (maxvalue)
( subpartition part2_sub1 values less than (10),
subpartition part2_sub2 values less than (maxvalue)))
as
select * from dba_objects;

select table_name,partition_name,subpartition_count from user_tab_partitions where table_name = 'TBL_RANGE_RANGE_PART';

select table_name,partition_name,subpartition_name from user_tab_subpartitions where table_name = 'TBL_RANGE_RANGE_PART';

select count(*) from tbl_Range_range_part subpartition(part2_sub2);
select count(*) from tbl_Range_range_part subpartition(part1_sub1);

[11] List-range Partition

Database VLDB and Partitioning Guide
---> Creating Composite List-Range Partitioned Tables

Example 4-14 Creating a composite list-range partitioned table

CREATE TABLE accounts
( id NUMBER
, account_number NUMBER
, customer_id NUMBER
, balance NUMBER
, branch_id NUMBER
, region VARCHAR(2)
, status VARCHAR2(1)
)
PARTITION BY LIST (region)
SUBPARTITION BY RANGE (balance)
( PARTITION p_northwest VALUES ('OR', 'WA')
( SUBPARTITION p_nw_low VALUES LESS THAN (1000)
, SUBPARTITION p_nw_average VALUES LESS THAN (10000)
, SUBPARTITION p_nw_high VALUES LESS THAN (100000)
, SUBPARTITION p_nw_extraordinary VALUES LESS THAN (MAXVALUE)
)
, PARTITION p_southwest VALUES ('AZ', 'UT', 'NM')
( SUBPARTITION p_sw_low VALUES LESS THAN (1000)
, SUBPARTITION p_sw_average VALUES LESS THAN (10000)
, SUBPARTITION p_sw_high VALUES LESS THAN (100000)
, SUBPARTITION p_sw_extraordinary VALUES LESS THAN (MAXVALUE)
)
, PARTITION p_northeast VALUES ('NY', 'VM', 'NJ')
( SUBPARTITION p_ne_low VALUES LESS THAN (1000)
, SUBPARTITION p_ne_average VALUES LESS THAN (10000)
, SUBPARTITION p_ne_high VALUES LESS THAN (100000)
, SUBPARTITION p_ne_extraordinary VALUES LESS THAN (MAXVALUE)
)
, PARTITION p_southeast VALUES ('FL', 'GA')
( SUBPARTITION p_se_low VALUES LESS THAN (1000)
, SUBPARTITION p_se_average VALUES LESS THAN (10000)
, SUBPARTITION p_se_high VALUES LESS THAN (100000)
, SUBPARTITION p_se_extraordinary VALUES LESS THAN (MAXVALUE)
)
, PARTITION p_northcentral VALUES ('SD', 'WI')
( SUBPARTITION p_nc_low VALUES LESS THAN (1000)
, SUBPARTITION p_nc_average VALUES LESS THAN (10000)
, SUBPARTITION p_nc_high VALUES LESS THAN (100000)
, SUBPARTITION p_nc_extraordinary VALUES LESS THAN (MAXVALUE)
)
, PARTITION p_southcentral VALUES ('OK', 'TX')
( SUBPARTITION p_sc_low VALUES LESS THAN (1000)
, SUBPARTITION p_sc_average VALUES LESS THAN (10000)
, SUBPARTITION p_sc_high VALUES LESS THAN (100000)
, SUBPARTITION p_sc_extraordinary VALUES LESS THAN (MAXVALUE)
)
) ENABLE ROW MOVEMENT;


--HOL

create table tbl_list_range_part
partition by list (object_type)
subpartition by range (object_id)
(partition part1 values ('INDEX','TABLE')
( subpartition part1_sub1 values less than (100),
subpartition part1_sub2 values less than (maxvalue)),
partition part2 values ('SYNONYM')
( subpartition part2_sub1 values less than (500),
subpartition part2_sub2 values less than (maxvalue)))
as
select * from dba_objects
where object_type in ('INDEX','TABLE','SYNONYM')


select count(*) from tbl_list_range_part subpartition (part2_sub2);
select count(*) from tbl_list_range_part subpartition (part2_sub1);

select count(*) from tbl_list_range_part subpartition (part1_sub1);
select count(*) from tbl_list_range_part subpartition (part1_sub2);

[12] List-Hash Partitioning

Database VLDB and Partitioning Guide
--> Creating Composite List-Hash Partitioned Tables

Example 4-12 Creating a composite list-hash partitioned table

CREATE TABLE accounts_list_hash
( id NUMBER
, account_number NUMBER
, customer_id NUMBER
, balance NUMBER
, branch_id NUMBER
, region VARCHAR(2)
, status VARCHAR2(1)
)
PARTITION BY LIST (region)
SUBPARTITION BY HASH (customer_id) SUBPARTITIONS 8
( PARTITION p_northwest VALUES ('OR', 'WA')
, PARTITION p_southwest VALUES ('AZ', 'UT', 'NM')
, PARTITION p_northeast VALUES ('NY', 'VM', 'NJ')
, PARTITION p_southeast VALUES ('FL', 'GA')
, PARTITION p_northcentral VALUES ('SD', 'WI')
, PARTITION p_southcentral VALUES ('OK', 'TX')
);

--HOL
create table tbl_list_hash_part
partition by list (object_type)
subpartition by hash (namespace)
subpartitions 8
(partition part1 values ('INDEX'),
partition part2 values ('TABLE'))
as
select * from dba_objects where object_type in ('INDEX','TABLE');


select table_name,partition_name,subpartition_count from user_tab_partitions where table_name='TBL_LIST_HASH_PART';

select partition_name,count(subpartition_name) from user_tab_subpartitions
where table_name = 'TBL_LIST_HASH_PART' group by partition_name;

select partition_name,subpartition_name
from user_tab_subpartitions
where table_name = 'TBL_LIST_HASH_PART'


create table tbl_list_hash_part2
partition by list (object_type)
subpartition by hash (namespace)
subpartitions 8
(partition part1 values ('INDEX'),
partition part2 values ('TABLE'),
partition part3 values ('SYNONYMS'),
partition part4 values ('VIEW'),
partition part5 values ('PROCEDURE'),
partition part6 values ('DB LINK'),
partition part7 values ('MATERIALIZED VIEW'),
partition part8 values (DEFAULT))
as
select * from dba_objects;


select partition_name,count(subpartition_name) 
from user_tab_subpartitions 
where table_name = 'TBL_LIST_HASH_PART2' 
group by partition_name; 


[13] List-List Partitioning


Database VLDB and Partitioning Guide
--> Creating Composite List-List Partitioned Tables

Example 4-13 Creating a composite list-list partitioned table

CREATE TABLE accounts_list_list
( id NUMBER
, account_number NUMBER
, customer_id NUMBER
, balance NUMBER
, branch_id NUMBER
, region VARCHAR(2)
, status VARCHAR2(1)
)
PARTITION BY LIST (region)
SUBPARTITION BY LIST (status)
( PARTITION p_northwest VALUES ('OR', 'WA')
( SUBPARTITION p_nw_bad VALUES ('B')
, SUBPARTITION p_nw_average VALUES ('A')
, SUBPARTITION p_nw_good VALUES ('G')
)
, PARTITION p_southwest VALUES ('AZ', 'UT', 'NM')
( SUBPARTITION p_sw_bad VALUES ('B')
, SUBPARTITION p_sw_average VALUES ('A')
, SUBPARTITION p_sw_good VALUES ('G')
)
, PARTITION p_northeast VALUES ('NY', 'VM', 'NJ')
( SUBPARTITION p_ne_bad VALUES ('B')
, SUBPARTITION p_ne_average VALUES ('A')
, SUBPARTITION p_ne_good VALUES ('G')
)
, PARTITION p_southeast VALUES ('FL', 'GA')
( SUBPARTITION p_se_bad VALUES ('B')
, SUBPARTITION p_se_average VALUES ('A')
, SUBPARTITION p_se_good VALUES ('G')
)
, PARTITION p_northcentral VALUES ('SD', 'WI')
( SUBPARTITION p_nc_bad VALUES ('B')
, SUBPARTITION p_nc_average VALUES ('A')
, SUBPARTITION p_nc_good VALUES ('G')
)
, PARTITION p_southcentral VALUES ('OK', 'TX')
( SUBPARTITION p_sc_bad VALUES ('B')
, SUBPARTITION p_sc_average VALUES ('A')
, SUBPARTITION p_sc_good VALUES ('G')
)
);

--HOL

create table tbl_list_list_part
partition by list ( object_type ) 
subpartition by list ( status ) 
(partition part1 values ('INDEX')
( subpartition part1_sub1 values ('VALID'),
subpartition part1_sub2 values ('INVALID')),
partition part2 values ('TABLE')
( subpartition part2_sub1 values ('VALID'),
subpartition part2_sub2 values ('INVALID')))
as
select * from dba_objects
where object_type in ('INDEX','TABLE');

select count(*) from tbl_list_list_part subpartition (part1_sub1);
select count(*) from tbl_list_list_part subpartition (part2_sub2);

[14] Interval-List Partitioning.

Database VLDB and Partitioning Guide
--> Creating Composite Interval-List Partitioned Tables

Example 4-15 Creating a composite interval-list partitioned table

CREATE TABLE sales_interval_list
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id) INTERVAL (NUMTODSINTERVAL(1,'DAY'))
SUBPARTITION BY LIST (channel_id)
SUBPARTITION TEMPLATE
( SUBPARTITION p_catalog VALUES ('C')
, SUBPARTITION p_internet VALUES ('I')
, SUBPARTITION p_partners VALUES ('P')
, SUBPARTITION p_direct_sales VALUES ('S')
, SUBPARTITION p_tele_sales VALUES ('T')
)
(PARTITION before_2000 VALUES LESS THAN (TO_DATE('01-JAN-2000','dd-MON-yyyy'))
)
PARALLEL;


set linesize 150
set pagesize 15000

create table tbl_interval_list_part
partition by range (created)
interval ( numtoyminterval ( 1 , 'MONTH')) 
subpartition by list (object_type)
( partition p1 values less than (to_date('01012000','ddmmyyyy'))
( subpartition sub_table values ('TABLE'),
subpartition sub_index values ('INDEX')))
as
select * from dba_objects;

set linesize 150
col high_value format a40
col table_name format a20
col partition_name format a10

select table_name,partition_name,subpartition_count,high_value from user_tab_partitions
where table_name = 'TBL_INTERVAL_LIST_PART';

[15] Interval-Range Partitioning

Database VLDB and Partitioning Guide
--> Creating Composite Interval-Range Partitioned Tables

Example 4-16 Creating a composite interval-range partitioned table

CREATE TABLE sales_interval_range
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id) INTERVAL (NUMTODSINTERVAL(1,'DAY'))
SUBPARTITION BY RANGE(amount_sold)
SUBPARTITION TEMPLATE
( SUBPARTITION p_low VALUES LESS THAN (1000)
, SUBPARTITION p_medium VALUES LESS THAN (4000)
, SUBPARTITION p_high VALUES LESS THAN (8000)
, SUBPARTITION p_ultimate VALUES LESS THAN (maxvalue)
)
(PARTITION before_2000 VALUES LESS THAN (TO_DATE('01-JAN-2000','dd-MON-yyyy'))
)
PARALLEL;

--HOL
create table tbl_interval_range_part
partition by range (created)
interval ( numtoyminterval ( 1 ,'month'))
subpartition by range (object_id)
( partition p1 values less than (to_date('01012010','ddmmyyyy'))
( subpartition p1_sub1 values less than (10),
subpartition p1_sub2 values less than (maxvalue)))
as
select * from dba_objects;


select count(*) from tbl_interval_range_part partition (p1);

select table_name,partition_name,subpartition_count,high_value from user_tab_partitions
where table_name = 'TBL_INTERVAL_RANGE_PART';

select count(*) from tbl_interval_range_part subpartition (&subpartition);
select count(*) from tbl_interval_range_part partition (&partition);


col table_name format a25
col partition_name format a25
col subpartition_name format a25
select table_name,partition_name,subpartition_name from user_tab_subpartitions 
where table_name ='TBL_INTERVAL_RANGE_PART';

select count(*) from tbl_interval_range_part subpartition (&subpartition);


[16] Interval-Hash Partitioning

Database VLDB and Partitioning Guide
--> Creating Composite Interval-Hash Partitioned Tables

CREATE TABLE sales_interval_hash
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
SUBPARTITION BY HASH (cust_id) SUBPARTITIONS 4
(PARTITION before_2000 VALUES LESS THAN (TO_DATE('01-JAN-2000','dd-MON-yyyy'))
)
PARALLEL;


CREATE TABLE sales_interval_hash2
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
SUBPARTITION BY hash(cust_id)
SUBPARTITION template
( SUBPARTITION p1 TABLESPACE tsa
, SUBPARTITION p2 TABLESPACE tsb
, SUBPARTITION p3 TABLESPACE tsc
, SUBPARTITION P4 TABLESPACE tsd
)
(PARTITION before_2000 VALUES LESS THAN (TO_DATE('01-JAN-2000','dd-MON-yyyy'))
)
PARALLEL;


create table tbl_interval_hash_part
partition by range ( created )
interval ( numtoyminterval ( 1, 'MONTH'))
subpartition by hash (object_id)
subpartitions 4
(partition p1 values less than (to_date('01012011','ddmmyyyy')))
as
select * from dba_objects;

select partition_name,subpartition_count,high_value from user_tab_partitions 
where table_name = 'TBL_INTERVAL_HASH_PART';

select count(*) from tbl_interval_hash_part partition (&partition);

[17] Perform partition maintenance operations

#Adding new partition to range-partitioned table

ALTER TABLE sales
ADD PARTITION jan99 VALUES LESS THAN (to_date('01-FEB-1999'),'dd-MON-yyyy')
TABLESPACE tsx;

ALTER TABLE sales ADD 
PARTITION sales_q1_2007 VALUES LESS THAN (TO_DATE('01-APR-2007','dd-MON-yyyy')),
PARTITION sales_q2_2007 VALUES LESS THAN (TO_DATE('01-JUL-2007','dd-MON-yyyy')),
PARTITION sales_q3_2007 VALUES LESS THAN (TO_DATE('01-OCT-2007','dd-MON-yyyy')),
PARTITION sales_q4_2007 VALUES LESS THAN (TO_DATE('01-JAN-2008','dd-MON-yyyy'))
; 

--HOL
create table tbl_range_part_test
(id number,name varchar2(20),surname varchar2(20),value number)
partition by range (value)
( partition part1 values less than (10), 
partition part2 values less than (20));

alter table tbl_Range_part_test add partition part3 values less than (30);

select table_name,partition_name from user_tab_partitions where table_name='TBL_RANGE_PART_TEST';



#Adding new partition to hash-partitioned table

ALTER TABLE scubagear ADD PARTITION;

ALTER TABLE scubagear
ADD PARTITION p_named TABLESPACE gear5;

--HOL

create table tbl_hash_part_new 
(id number,name varchar2(10),surname varchar2(10),value number) 
partition by hash (value) 
partitions 8; 

alter table tbl_hash_part_new add partition;

select table_name,partition_name from user_tab_partitions where table_name='TBL_HASH_PART_NEW';


#Adding new partition to list-partitioned table

ALTER TABLE q1_sales_by_region 
ADD PARTITION q1_nonmainland VALUES ('HI', 'PR')
STORAGE (INITIAL 20K NEXT 20K) TABLESPACE tbs_3
NOLOGGING;

--HOL

create table tbl_list_part_new
(id number,name varchar2(10),surname varchar2(10),course varchar2(10))
partition by list (course) 
( partition part1 values ('ORACLE'),
partition part2 values ('PLSQL')); 

alter table tbl_list_part_new add partition part3 values ('SQL'); 

select table_name,partition_name,tablespace_name from user_tab_partitions
where table_name = 'TBL_LIST_PART_NEW';

#Adding new partition to interval-partitioned table

set long 10000
set pagesize 10000
select dbms_metadata.get_ddl('TABLE','TBL_INTERVAL_PART') from dual;

--Add partition
LOCK TABLE tbl_interval_part PARTITION FOR(TO_DATE('01-JAN-2019','dd-MON-yyyy')) IN SHARE MODE;

ALTER TABLE tbl_interval_part SET INTERVAL (NUMTOYMINTERVAL(1,'MONTH'));

LOCK TABLE tbl_interval_part PARTITION FOR(TO_DATE('01-FEB-2019','dd-MON-yyyy')) IN SHARE MODE;

ALTER TABLE tbl_interval_part SET INTERVAL (NUMTOYMINTERVAL(1,'MONTH'));

--To disable interval partition table.
ALTER TABLE transactions SET INTERVAL ();

--HOL

--Convert monthly partition to daily partition
SQL> ALTER TABLE TBL_INTERVAL_PART SET INTERVAL (NUMTOYMINTERVAL(1,'MONTH'));

Table altered.

SQL> c/YM/DS
1* ALTER TABLE TBL_INTERVAL_PART SET INTERVAL (NUMTODSINTERVAL(1,'MONTH'))
SQL> c/MONTH/DAY
1* ALTER TABLE TBL_INTERVAL_PART SET INTERVAL (NUMTODSINTERVAL(1,'DAY'))
SQL> r
1* ALTER TABLE TBL_INTERVAL_PART SET INTERVAL (NUMTODSINTERVAL(1,'DAY'))

Table altered.

--Convert daily partition to monthly partition 
SQL> c/DS/YM
1* ALTER TABLE TBL_INTERVAL_PART SET INTERVAL (NUMTOYMINTERVAL(1,'DAY'))
SQL> c/DAY/MONTH
1* ALTER TABLE TBL_INTERVAL_PART SET INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
SQL> r
1* ALTER TABLE TBL_INTERVAL_PART SET INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))

Table altered.



#Adding partition and subpartition to composite-*list partitioned tables

Example 4-26 Adding partitions to a range-list partitioned table

ALTER TABLE quarterly_regional_sales 
ADD PARTITION q1_2000 VALUES LESS THAN (TO_DATE('1-APR-2000','DD-MON-YYYY'))
STORAGE (INITIAL 20K NEXT 20K) TABLESPACE tsa NOLOGGING
(
SUBPARTITION q1_2000_northwest VALUES ('OR', 'WA'),
SUBPARTITION q1_2000_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q1_2000_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q1_2000_southeast VALUES ('FL', 'GA'),
SUBPARTITION q1_2000_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q1_2000_southcentral VALUES ('OK', 'TX')
);

ALTER TABLE quarterly_regional_sales
MODIFY PARTITION q1_1999 
ADD SUBPARTITION q1_1999_south
VALUES ('AR','MS','AL') tablespace tsa;


#Adding partitin and subpartition to composite-*range partitioned tables

Example 4-27 Adding partitions to a range-range partitioned table

ALTER TABLE shipments
ADD PARTITION p_2007_jan
VALUES LESS THAN (TO_DATE('01-FEB-2007','dd-MON-yyyy')) COMPRESS
( SUBPARTITION p07_jan_e VALUES LESS THAN (TO_DATE('15-FEB-2007','dd-MON-yyyy'))
, SUBPARTITION p07_jan_a VALUES LESS THAN (TO_DATE('01-MAR-2007','dd-MON-yyyy'))
, SUBPARTITION p07_jan_l VALUES LESS THAN (TO_DATE('01-APR-2007','dd-MON-yyyy'))
) ;

ALTER TABLE shipments
MODIFY PARTITION p_2007_jan
ADD SUBPARTITION p07_jan_vl VALUES LESS THAN (MAXVALUE) ;


--HOL

create table tbl_list_range_test
(id number,
course varchar2(10))
partition by list (course) 
subpartition by range (id) 
( partition part1 values ('Oracle') 
(subpartition part1_sub1 values less than (10))); 

alter table tbl_list_range_test add partition part2 values('PLSQL'); 

alter table tbl_list_range_test modify partition part1 
add subpartition part1_sub2 values less than(20);

select partition_name,high_value from user_tab_partitions 
where table_name='TBL_LIST_RANGE_TEST';

select partition_name,subpartition_name,high_value from user_tab_subpartitions 
where table_name='TBL_LIST_RANGE_TEST';

alter table tbl_list_range_test add partition part3 values ('RAC') 
(subpartition part3_sub1 values (50));

select partition_name,subpartition_name,high_value from user_tab_subpartitions 
where table_name='TBL_LIST_RANGE_TEST';


#Adding partitin and subpartition to composite-*hash partitioned tables

ALTER TABLE sales ADD PARTITION q1_2000
VALUES LESS THAN (2000, 04, 01) COMPRESS
SUBPARTITIONS 8 STORE IN tsa;

ALTER TABLE diving MODIFY PARTITION locations_us
ADD SUBPARTITION us_locs5 TABLESPACE us1;


--HOL

create table tbl_list_hash_part_test
(id number,course varchar2(10),value number)
partition by list (course)
subpartition by hash (value)
subpartitions 8
(partition part1 values ('Oracle'));


ALTER TABLE tbl_list_hash_part_test add partition part2 values('PLSQL') 
subpartitions 4;

select table_name,partition_name,subpartition_count from user_tab_partitions 
where table_name='TBL_LIST_HASH_PART_TEST';

alter table tbl_list_hash_part_test modify partition part2 add subpartition;


17.2 Coalescing partitions

#Coalescing a Partition in a Hash Partitioned Table

ALTER TABLE ouu1
COALESCE PARTITION;

#Coalescing a Subpartition in a *-Hash Partitioned Table

ALTER TABLE diving MODIFY PARTITION us_locations
COALESCE SUBPARTITION; 

#Coalescing Hash Partitioned Global Indexes

ALTER INDEX hgidx COALESCE PARTITION;

--HOL
col table_name format a25
col partition_name format a25
col tablespace_name format a25
select table_name,partition_name,tablespace_name from user_tab_partitions 
where table_name = 'TBL_HASH_PART_NEW'

alter table tbl_hash_part_new coalesce partition;

select partition_name,count(subpartition_name) from user_tab_subpartitions 
where table_name ='TBL_RANGE_HASH_PART'
group by partition_name;

alter table tbl_range_hash_part modify partition part1 coalesce subpartition;


17.3 Dropping a partition

#Dropping a Partition from a Table that Contains Data and Global Indexes
ALTER TABLE sales DROP PARTITION dec98
UPDATE INDEXES;

#Dropping a Partition Containing Data and Referential Integrity Constraints

DELETE FROM sales partition (dec94);
ALTER TABLE sales DROP PARTITION dec94;

#Dropping Interval Partitions

ALTER TABLE sales DROP PARTITION FOR(TO_DATE('01-SEP-2007','dd-MON-yyyy'));

#Dropping Index Partitions

ALTER INDEX npr DROP PARTITION P1;
ALTER INDEX npr REBUILD PARTITION P2;

#Dropping Multiple Partitions

ALTER TABLE sales DROP PARTITION sales_q1_2008, sales_q2_2008,
sales_q3_2008, sales_q4_2008;


--HOL

alter table tbl_list_range_test drop partition part1;
alter table tbl_range_list_test drop partition part1 update indexes;


17.4 Exchange partitions


#Convert a partition to a non-partition table.

create table tbl_range_part_exch (id number)
partition by range (id)
(partition part1 values less than (100),
partition part2 values less than (200));

insert into tbl_range_part_exch values(&1);

select * from tbl_range_part_exch;

create table tbl_non_part_exch (id number);

alter table tbl_range_part_exch 
exchange partition part1 with table tbl_non_part_exch;

select count(*) from tbl_non_part_exch;


#Convert subpartition to a non-partitioned table.

set linesize 150
set pagesize 15000
select table_name,partition_name,subpartition_name from user_tab_subpartitions 
where table_name='TBL_LIST_RANGE_PART';

create table tbl_non_part_exch_2 as select * from dba_objects where 1 = 2;

alter table tbl_list_range_part
exchange subpartition part1_sub1 with table tbl_non_part_exch_2;

select count(*) from tbl_non_part_exch_2;

select count(*) from tbl_list_range_part subpartition (part1_sub1);


#Convert non-partitioned table to a partition(subpartition) of partitioned-table

connect sys/oracle_4U@pdb2 as sysdba

grant dba to redef_test identified by redef_test;

connect redef_test/redef_test@pdb2

create table tbl_non_part_2
as
select * from dba_objects where object_id is not null;

alter table tbl_non_part_2 add primary key(object_id);

EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('REDEF_TEST','TBL_NON_PART_2');

CREATE TABLE TBL_PARTITIONED_REDEF
PARTITION BY RANGE (OBJECT_ID)
( PARTITION PART1 VALUES LESS THAN (100),
PARTITION PART2 VALUES LESS THAN (1000),
PARTITION PART3 VALUES LESS THAN (10000),
PARTITION PART4 VALUES LESS THAN (MAXVALUE))
as
select * from dba_objects where 1 = 2;


exec dbms_redefinition.start_redef_table('REDEF_TEST','TBL_NON_PART_2',
'TBL_PARTITIONED_REDEF',null,2);


exec dbms_redefinition.sync_interim_table('REDEF_TEST','TBL_NON_PART_2',
'TBL_PARTITIONED_REDEF');

SET SERVEROUTPUT ON
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
uname => 'REDEF_TEST', 
orig_table => 'TBL_NON_PART_2',
int_table => 'TBL_PARTITIONED_REDEF',
copy_indexes => DBMS_REDEFINITION.CONS_ORIG_PARAMS, 
copy_triggers => TRUE, 
copy_constraints => TRUE, 
copy_privileges => TRUE, 
ignore_errors => TRUE, 
num_errors => num_errors);
END;
/

exec dbms_redefinition.finish_redef_table('REDEF_TEST','TBL_NON_PART_2',
'TBL_PARTITIONED_REDEF');

SELECT table_name,partition_name from dba_tab_partitions where 
table_name='TBL_NON_PART_2';

SELECT table_name,partition_name from dba_tab_partitions where 
table_name='TBL_PARTITIONED_REDEF';

select count(*) from tbl_non_part_2;
select count(*) from TBL_PARTITIONED_REDEF; 


#Exchange partition(subpartition) of partitioned table with partition(subpartition) of 
composite-partitioned table and vice versa


create table tbl_list_part_3
partition by list(status)
(partition part1 values ('VALID'),partition part2 values ('INVALID'))
as
select * from dba_objects;


create table tbl_range_list_part_3
partition by range (object_id)
subpartition by list(status)
( partition part1 values less than (maxvalue)
( subpartition part1_sub1 values ('VALID'),
subpartition part1_sub2 values ('INVALID')))
as
select * from dba_objects where 1 = 2;


select count(*) from tbl_list_part_3;
select count(*) from tbl_range_list_part_3;

ALTER TABLE TBL_RANGE_LIST_PART_3
EXCHANGE PARTITION part1 with table tbl_list_part_3;

select count(*) from tbl_list_part_3;
select count(*) from tbl_range_list_part_3;

17.5 Merging partitions

create table tbl_range_part_merge 
partition by range ( object_id )
(partition part1 values less than (100),
partition part2 values less than (200),
partition part3 values less than (maxvalue))
as
select * from dba_objects;


select count(*) from tbl_range_part_merge partition(&partition);


alter table tbl_range_part_merge MERGE PARTITIONS part1,part2 INTO PARTITION 
part2 update indexes;

select count(*) from tbl_range_part_merge partition(part2);

SQL> select count(*) from tbl_range_part_merge partition(part1);
select count(*) from tbl_range_part_merge partition(part1)
*
ERROR at line 1:
ORA-02149: Specified partition does not exist

17.6 Renaming partitions

ALTER TABLE TBL_RANGE_PART_MERGE RENAME PARTITION PART2 TO PART5;

select count(*) from tbl_range_part_merge partition(part2);
ERROR at line 1:
ORA-02149: Specified partition does not exist

select count(*) from tbl_range_part_merge partition(part5);


17.7 Moving partitions


select name from v$tablespace;

alter table tbl_range_part_merge 
MOVE partition part5 tablespace USERS parallel (degree 2);

alter table tbl_range_list_part_3 
MOVE subpartition part1_sub1 tablespace TSA;


17.8 Splitting partitions

create table tbl_range_part_split 
partition by range ( object_id ) 
(partition part1 values less than (100),
partition part2 values less than (maxvalue))
as
select * from dba_objects;

select count(*) from tbl_range_part_split partition (part1);
COUNT(*)
----------
98

select count(*) from tbl_range_part_split partition (part2);
COUNT(*)
----------
92431


alter table tbl_range_part_split
SPLIT PARTITION part1 at (50) INTO 
(partition part1_1,partition part1_2);

select count(*) from tbl_range_part_split partition (part1_1);
COUNT(*)
----------
48

select count(*) from tbl_range_part_split partition (part1_2);
COUNT(*)
----------
50


alter table tbl_range_part_split 
split partition part2 at (10000) into 
(partition part2_1,partition part2);


set linesize 50
set linesize 150
select partition_name,high_value
from dba_tab_partitions
where table_name = 'TBL_RANGE_PART_SPLIT';

PARTITION_NAME HIGH_VALUE
------------------------- ---------------
PART2_1 10000
PART2 MAXVALUE
PART1_2 100
PART1_1 50


17.9 Truncating partitions

ALTER TABLE TBL_RANGE_PART_SPLIT TRUNCATE PARTITION part1_1;

SELECT COUNT(*) FROM TBL_RANGE_PART_SPLIT partition (part1_1);

=====================
Path to Documentation:
=====================

Database VLDB and Partitioning Guide
4 Partition Administration
-> Creating a Range-Partitioned Table
-> Creating Interval-Partitioned Tables
-> Creating a Hash Partitioned Table
-> Creating a List-Partitioned Table
-> Creating Reference-Partitioned Tables
-> Specifying Composite Partitioning When Creating Tables

-->Maintenance Operations for Partitioned Tables and Indexes
---->About Adding Partitions and Subpartitions

#Data Dictionaries

USER_TAB_PARTITIONS
USER_SEGMENTS
USER_PART_TABLES
DBA_TAB_PARTITIONS
USER_LOB_PARTITIONS

Reference
Database Administrator’s Guide
--> 20 Managing Tables
-----> 20.7.10 Online Table Redefinition Examples

Database PL/SQL Packages and Types Reference
--> 122 DBMS_REDEFINITION

Thank you for visiting this blog 🙂