Interesting findings with interval partitioning:
SQL> SELECT *
2 FROM v$version
3 /
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> DROP TABLE tbl PURGE
2 /
Table dropped.
SQL> CREATE TABLE tbl(
2 id number(6),
3 dt date
4 )
5 PARTITION BY RANGE(dt)
6 INTERVAL (INTERVAL '1' DAY)
7 (
8 PARTITION p1 VALUES LESS THAN (date '-857-12-31')
9 )
10 /
Table created.
SQL> select partition_name,
2 high_value
3 from user_tab_partitions
4 where table_name = 'TBL'
5 /
PARTITION_NAME HIGH_VALUE
-------------- ------------------------------------------------------------
P1 TO_DATE('-0857-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIAN')
SQL> INSERT
2 INTO tbl
3 VALUES(
4 1,
5 sysdate
6 )
7 /
1 row created.
SQL> DROP TABLE tbl PURGE
2 /
Table dropped.
SQL> CREATE TABLE tbl(
2 id number(6),
3 dt date
4 )
5 PARTITION BY RANGE(dt)
6 INTERVAL (INTERVAL '1' DAY)
7 (
8 PARTITION p1 VALUES LESS THAN (date '-858-01-01')
9 )
10 /
Table created.
SQL> select partition_name,
2 high_value
3 from user_tab_partitions
4 where table_name = 'TBL'
5 /
PARTITION_NAME HIGH_VALUE
-------------- ------------------------------------------------------------
P1 TO_DATE('-0858-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIAN')
SQL> INSERT
2 INTO tbl
3 VALUES(
4 1,
5 sysdate
6 )
7 /
INTO tbl
*
ERROR at line 2:
ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions
SQL>
From
Logical Database Limits:
Maximum number of partitions allowed per table or index: 1024K - 1
I always thought limit implies to number of actual, not potential partitions, however it looks like I was wrong, although it makes not much sense to limit potential and not actual partitions:
SQL> select trunc(sysdate) - date '-858-01-01',
2 1024 * 1024 - 1
3 from dual
4 /
TRUNC(SYSDATE)-DATE'-858-01-01' 1024*1024-1
------------------------------- -----------
1048661 1048575
SQL> select to_char(DATE'-858-01-01' + 1048575,'MM/DD/YYYY')
2 from dual
3 /
TO_CHAR(DA
----------
11/17/2012
SQL>
So tomorrow "magic" date should increase by one day. I'll test it. But more interesting if tomorrow I will be able to insert a row that forms a new partition into table TBL.
SY.