Splitting the DEFAULT Partition of a List-Partitioned Table

View All Scripts Login to Run Script

Script Name Splitting the DEFAULT Partition of a List-Partitioned Table
Description Splitting a list-partitioned table
Area Partitioning
Referenced In Database VLDB and Partitioning Guide
Contributor Oracle
Created Thursday January 31, 2019

Statement 1

Create a list-partitioned table with a DEFAULT partition

CREATE TABLE sales_by_region  
 (dept_number         NUMBER NOT NULL,  
  dept_name           VARCHAR2(20),  
  quarterly_sales     NUMBER(10,2),  
  state               VARCHAR2(2)  
 )  
 PARTITION BY LIST (state)  
 (  
  PARTITION yearly_north VALUES ('MN','WI','MI'),  
  PARTITION yearly_south VALUES ('NM','TX','GA'),  
  PARTITION yearly_east VALUES  ('MA','NY','NC'),  
  PARTITION yearly_west VALUES  ('CA','OR','WA'),  
  PARTITION unknown VALUES (DEFAULT)  
 )

Table created.

Statement 2

List the current partitions

SELECT PARTITION_NAME, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME ='SALES_BY_REGION'


PARTITION_NAME HIGH_VALUE
UNKNOWN DEFAULT
YEARLY_EAST 'MA', 'NY', 'NC'
YEARLY_NORTH 'MN', 'WI', 'MI'
YEARLY_SOUTH 'NM', 'TX', 'GA'
YEARLY_WEST 'CA', 'OR', 'WA'

5 rows selected.

PARTITION_NAME	HIGH_VALUE
UNKNOWN	DEFAULT
YEARLY_EAST	'MA', 'NY', 'NC'
YEARLY_NORTH	'MN', 'WI', 'MI'
YEARLY_SOUTH	'NM', 'TX', 'GA'
YEARLY_WEST	'CA', 'OR', 'WA'

Statement 3

Insert some data

INSERT INTO SALES_BY_REGION VALUES (002, 'AUTO NORTH', 450000, 'MN')

1 row(s) inserted.

Statement 4

INSERT INTO SALES_BY_REGION VALUES (004, 'AUTO SOUTH',  945000, 'GA')

1 row(s) inserted.

Statement 5

INSERT INTO SALES_BY_REGION VALUES (006, 'AUTO EAST', 2125000, 'MA')

1 row(s) inserted.

Statement 6

INSERT INTO SALES_BY_REGION VALUES (006, 'AUTO EAST', 6101000, 'NY')

1 row(s) inserted.

Statement 7

INSERT INTO SALES_BY_REGION VALUES (006, 'AUTO EAST',  741000, 'NC')

1 row(s) inserted.

Statement 8

INSERT INTO SALES_BY_REGION VALUES (008, 'AUTO WEST',  901000, 'OR')

1 row(s) inserted.

Statement 9

INSERT INTO SALES_BY_REGION VALUES (009, 'AUTO MIDWEST', 1950000, 'AZ')

1 row(s) inserted.

Statement 10

INSERT INTO SALES_BY_REGION VALUES (009, 'AUTO MIDWEST', 5725000, 'UT')

1 row(s) inserted.

Statement 11

Display the data in a partition that was explicitly created

SELECT DEPT_NUMBER, DEPT_NAME, QUARTERLY_SALES, STATE FROM SALES_BY_REGION PARTITION(yearly_east)


DEPT_NUMBER DEPT_NAME QUARTERLY_SALES STATE
6 AUTO EAST 2125000 MA
6 AUTO EAST 6101000 NY
6 AUTO EAST 741000 NC

3 rows selected.

DEPT_NUMBER	DEPT_NAME	QUARTERLY_SALES	STATE
6	AUTO EAST	2125000	MA
6	AUTO EAST	6101000	NY
6	AUTO EAST	741000	NC

Statement 12

Display the data in the DEFAULT partition

SELECT DEPT_NUMBER, DEPT_NAME, QUARTERLY_SALES, STATE FROM SALES_BY_REGION PARTITION(unknown)


DEPT_NUMBER DEPT_NAME QUARTERLY_SALES STATE
9 AUTO MIDWEST 1950000 AZ
9 AUTO MIDWEST 5725000 UT

2 rows selected.

Statement 13
Try to add a new partition for values 'AZ' and 'UT'. This action fails because all undefined values are automatically included in the DEFAULT partition.
```
ALTER TABLE sales_by_region ADD PARTITION yearly_midwest VALUES ('AZ', 'UT')
```
```
ORA-14323: cannot add partition when DEFAULT partition exists 
```
More Details: https://docs.oracle.com/error-help/db/ora-14323

DEPT_NUMBER	DEPT_NAME	QUARTERLY_SALES	STATE
9	AUTO MIDWEST	1950000	AZ
9	AUTO MIDWEST	5725000	UT

Statement 14

Now split the DEFAULT partition to create a new partition for values 'AZ' and 'UT'. You must SPLIT the DEFAULT partition when you want to add a new partition.

ALTER TABLE sales_by_region    
   SPLIT PARTITION unknown VALUES ('AZ', 'UT')    
   INTO    
    ( PARTITION yearly_midwest,    
      PARTITION unknown)

Table altered.

Statement 15

List the current partitions

SELECT PARTITION_NAME, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME ='SALES_BY_REGION'


PARTITION_NAME HIGH_VALUE
UNKNOWN DEFAULT
YEARLY_EAST 'MA', 'NY', 'NC'
YEARLY_MIDWEST 'AZ', 'UT'
YEARLY_NORTH 'MN', 'WI', 'MI'
YEARLY_SOUTH 'NM', 'TX', 'GA'
YEARLY_WEST 'CA', 'OR', 'WA'

6 rows selected.

PARTITION_NAME	HIGH_VALUE
UNKNOWN	DEFAULT
YEARLY_EAST	'MA', 'NY', 'NC'
YEARLY_MIDWEST	'AZ', 'UT'
YEARLY_NORTH	'MN', 'WI', 'MI'
YEARLY_SOUTH	'NM', 'TX', 'GA'
YEARLY_WEST	'CA', 'OR', 'WA'

Statement 16

List the data in the new partition

SELECT DEPT_NUMBER, DEPT_NAME, QUARTERLY_SALES, STATE FROM SALES_BY_REGION PARTITION(yearly_midwest)


DEPT_NUMBER DEPT_NAME QUARTERLY_SALES STATE
9 AUTO MIDWEST 1950000 AZ
9 AUTO MIDWEST 5725000 UT

2 rows selected.

DEPT_NUMBER	DEPT_NAME	QUARTERLY_SALES	STATE
9	AUTO MIDWEST	1950000	AZ
9	AUTO MIDWEST	5725000	UT

Statement 17

List the data in the DEFAULT partition

SELECT DEPT_NUMBER, DEPT_NAME, QUARTERLY_SALES, STATE FROM SALES_BY_REGION PARTITION(unknown)

no data found

Additional Information

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