partitioning - limits on interval partitions.

View All Scripts Login to Run Script

Script Name partitioning - limits on interval partitions.
Description This script demonstrates the MAXVALUE clause, and the limit on the number of partitions a table can have, and its relevance to interval partitioned tables.
Area Partitioning
Contributor Connor Mcdonald (Oracle)
Created Tuesday August 01, 2017

Statement 1
```
drop table sales purge
```
```
ORA-00942: table or view does not exist 
```
More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 2

create table sales 
( tstamp       timestamp    not null, 
  empno        number(10)   not null, 
  ename        varchar2(10) not null, 
  deptno       varchar2(10) not null 
) 
partition by range (tstamp) 
( 
  partition p00 values less than (timestamp '2010-01-01 00:00:00'), 
  partition p01 values less than (timestamp '2010-02-01 00:00:00'), 
  partition p02 values less than (timestamp '2010-03-01 00:00:00'), 
  partition p03 values less than (timestamp '2010-04-01 00:00:00'), 
  partition p04 values less than (timestamp '2010-05-01 00:00:00'), 
  partition p05 values less than (timestamp '2010-06-01 00:00:00'), 
  partition p06 values less than (timestamp '2010-07-01 00:00:00') 
)

Table created.

Statement 3

insert into sales values ( timestamp '2013-01-01 00:00:00' , 0,0,0)

ORA-14400: inserted partition key does not map to any partition ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-14400

Statement 4
```
drop table sales purge
```
```
Table dropped.
```

Statement 5

create table sales 
( tstamp       timestamp    not null, 
  empno        number(10)   not null, 
  ename        varchar2(10) not null, 
  deptno       varchar2(10) not null 
) 
partition by range (tstamp) 
( 
  partition p00 values less than (timestamp '2010-01-01 00:00:00'), 
  partition p01 values less than (timestamp '2010-02-01 00:00:00'), 
  partition p02 values less than (timestamp '2010-03-01 00:00:00'), 
  partition p03 values less than (timestamp '2010-04-01 00:00:00'), 
  partition p04 values less than (timestamp '2010-05-01 00:00:00'), 
  partition p05 values less than (timestamp '2010-06-01 00:00:00'), 
  partition p06 values less than (timestamp '2010-07-01 00:00:00'), 
  partition pmax values less than (maxvalue) 
)

Table created.

Statement 6

insert into sales  
values ( timestamp '2013-01-01 00:00:00' , 0,0,0)

1 row(s) inserted.

Statement 7

alter table SALES set interval ( NUMTOYMINTERVAL(1,'MONTH') )

ORA-14759: SET INTERVAL is not legal on this table.

More Details: https://docs.oracle.com/error-help/db/ora-14759

Statement 8

alter table sales drop partition pmax

Table altered.

Statement 9

alter table SALES set interval ( NUMTOYMINTERVAL(1,'MONTH') )

Table altered.

Statement 10

select  
  partition_name pname,  
  partition_position pos, 
  high_value 
from   user_tab_partitions 
where  table_name = 'SALES'


PNAME POS HIGH_VALUE
P00 1 TIMESTAMP' 2010-01-01 00:00:00'
P01 2 TIMESTAMP' 2010-02-01 00:00:00'
P02 3 TIMESTAMP' 2010-03-01 00:00:00'
P03 4 TIMESTAMP' 2010-04-01 00:00:00'
P04 5 TIMESTAMP' 2010-05-01 00:00:00'
P05 6 TIMESTAMP' 2010-06-01 00:00:00'
P06 7 TIMESTAMP' 2010-07-01 00:00:00'

7 rows selected.

PNAME	POS	HIGH_VALUE
P00	1	TIMESTAMP' 2010-01-01 00:00:00'
P01	2	TIMESTAMP' 2010-02-01 00:00:00'
P02	3	TIMESTAMP' 2010-03-01 00:00:00'
P03	4	TIMESTAMP' 2010-04-01 00:00:00'
P04	5	TIMESTAMP' 2010-05-01 00:00:00'
P05	6	TIMESTAMP' 2010-06-01 00:00:00'
P06	7	TIMESTAMP' 2010-07-01 00:00:00'

Statement 11

drop table MILLION_PAR purge

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 12

create table MILLION_PAR ( x int ) 
partition by range ( x ) 
interval( 1 ) 
(   
  partition p0 values less than (1) 
)

Table created.

Statement 13

insert into MILLION_PAR values (2000000)

ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-14300

Statement 14
```
drop table sales purge
```
```
Table dropped.
```

Statement 15

create table sales 
( tstamp       timestamp    not null, 
  empno        number(10)   not null, 
  ename        varchar2(10) not null, 
  deptno       varchar2(10) not null 
) 
partition by range (tstamp) 
( 
  partition p00 values less than (timestamp '2010-01-01 00:00:00'), 
  partition p01 values less than (timestamp '2010-02-01 00:00:00'), 
  partition p02 values less than (timestamp '2010-03-01 00:00:00'), 
  partition p03 values less than (timestamp '2010-04-01 00:00:00'), 
  partition p04 values less than (timestamp '2010-05-01 00:00:00'), 
  partition p05 values less than (timestamp '2010-06-01 00:00:00'), 
  partition p06 values less than (timestamp '2010-07-01 00:00:00') 
)

Table created.

Statement 16

insert into sales values ( timestamp '2013-01-01 00:00:00' , 0,0,0)

ORA-14400: inserted partition key does not map to any partition ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-14400

Statement 17
```
drop table sales purge
```
```
Table dropped.
```

Statement 18

create table sales 
( tstamp       timestamp    not null, 
  empno        number(10)   not null, 
  ename        varchar2(10) not null, 
  deptno       varchar2(10) not null 
) 
partition by range (tstamp) 
( 
  partition p00 values less than (timestamp '2010-01-01 00:00:00'), 
  partition p01 values less than (timestamp '2010-02-01 00:00:00'), 
  partition p02 values less than (timestamp '2010-03-01 00:00:00'), 
  partition p03 values less than (timestamp '2010-04-01 00:00:00'), 
  partition p04 values less than (timestamp '2010-05-01 00:00:00'), 
  partition p05 values less than (timestamp '2010-06-01 00:00:00'), 
  partition p06 values less than (timestamp '2010-07-01 00:00:00'), 
  partition pmax values less than (maxvalue) 
)

Table created.

Statement 19

insert into sales  
values ( timestamp '2013-01-01 00:00:00' , 0,0,0)

1 row(s) inserted.

Statement 20

alter table SALES set interval ( NUMTOYMINTERVAL(1,'MONTH') )

ORA-14759: SET INTERVAL is not legal on this table.

More Details: https://docs.oracle.com/error-help/db/ora-14759

Statement 21

alter table sales drop partition pmax

Table altered.

Statement 22

alter table SALES set interval ( NUMTOYMINTERVAL(1,'MONTH') )

Table altered.

Statement 23

select  
  partition_name pname,  
  partition_position pos, 
  high_value 
from   user_tab_partitions 
where  table_name = 'SALES'


PNAME POS HIGH_VALUE
P00 1 TIMESTAMP' 2010-01-01 00:00:00'
P01 2 TIMESTAMP' 2010-02-01 00:00:00'
P02 3 TIMESTAMP' 2010-03-01 00:00:00'
P03 4 TIMESTAMP' 2010-04-01 00:00:00'
P04 5 TIMESTAMP' 2010-05-01 00:00:00'
P05 6 TIMESTAMP' 2010-06-01 00:00:00'
P06 7 TIMESTAMP' 2010-07-01 00:00:00'

7 rows selected.

PNAME	POS	HIGH_VALUE
P00	1	TIMESTAMP' 2010-01-01 00:00:00'
P01	2	TIMESTAMP' 2010-02-01 00:00:00'
P02	3	TIMESTAMP' 2010-03-01 00:00:00'
P03	4	TIMESTAMP' 2010-04-01 00:00:00'
P04	5	TIMESTAMP' 2010-05-01 00:00:00'
P05	6	TIMESTAMP' 2010-06-01 00:00:00'
P06	7	TIMESTAMP' 2010-07-01 00:00:00'

Statement 24

drop table MILLION_PAR purge

Table dropped.

Statement 25

create table MILLION_PAR ( x int ) 
partition by range ( x ) 
interval( 1 ) 
(   
  partition p0 values less than (1) 
)

Table created.

Statement 26

insert into MILLION_PAR values (2000000)

ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-14300

Additional Information

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