While researching interval partitioning in Oracle 19c I came across a phenomenon I had already solved a long time ago but then forgot about. This time I decided to write about it so I don’t have to spend 5 minutes rediscovering the wheel. As always, if you are following along, make sure you are appropriately licensed for this feature
The Situation
Consider the following example:
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Aug 16 09:32:17 2019 Version 19.4.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Last Successful login time: Fri Aug 16 2019 09:07:55 +02:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.4.0.0.0 SQL> create table t (id, vc, d) 2 partition by range (id) interval (5000) 3 ( 4 partition p1 values less than (5000) 5 ) as 6 select object_id, object_name, created from dba_objects; create table t (id, vc, d) * ERROR at line 1: ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions SQL> !oerr ora 14300 14300, 00000, "partitioning key maps to a partition outside maximum permitted number of partitions" // *Cause: The row inserted had a partitioning key that maps to a partition number greater than 1048575 // *Action Ensure that the partitioning key falls within 1048575 partitions or subpartitions.
I knew about the limit of 1048575 partitions, but that shouldn’t be the case here:
SQL> select max(object_id), max(object_id) / 5000 from dba_objects;
MAX(OBJECT_ID) MAX(OBJECT_ID)/5000
-------------- -------------------
75743 15.1486
There shouldn’t be more than 16 partitions …
NEARBY: not having a not-null constraint on a partition key might be an indication of a sub-optimal design choice.
A quick search revealed an article on AskTom mentioning NULLs. I hadn’t thought about NULL values in dba_objects.object_id, but apparently there are:
SQL> select object_id, object_name, object_type from dba_objects where object_id is null;
OBJECT_ID OBJECT_NAME OBJECT_TYPE
---------- ------------------------------ -----------------------
SYS_HUB DATABASE LINK
Today I learned that database links don’t have object_ids. Neither is there a data_object_id.
Summary
Don’t try inserting NULLs as a partitioning key if that partition key uses interval partitioning. The error message, although strictly speaking correct, lead me astray for a moment:
SQL> create table t (id, vc, d) 2 partition by range (id) interval (5000) 3 ( 4 partition p1 values less than (5000) 5 ) 6 as 7 select object_id, object_name, created from dba_objects 8 WHERE OBJECT_ID IS NOT NULL; Table created.
Problem solved! Another, probably better approach, would be the addition of a not null constraint in the first place:
SQL> create table t (id not null, vc, d)
2 partition by range (id) interval (5000)
3 (
4 partition p1 values less than (5000)
5 )
6 as select object_id, object_name, created from dba_objects;
ERROR at line 6:
ORA-01400: cannot insert NULL into ("MARTIN"."T"."ID")
The intention of my post was to help users who get an ORA-14300 although they don’t exceed the partition limit. It’s not a post about good design ;)
Interlude
This actually works for non-interval partitioned tables, but ONLY if you have a catch all partition:
SQL> select dbms_rowid.rowid_object(rowid) from t1 where id is null;
DBMS_ROWID.ROWID_OBJECT(ROWID)
------------------------------
75564
SQL> select object_name, subobject_name, object_id, data_object_id
2 from dba_objects where data_object_id = 75564
3 /
OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID
-------------------- -------------------- ---------- --------------
T1 PMAX 75564 75564
SQL> select high_value from user_tab_partitions where table_name = 'T1' and partition_name = 'PMAX'
2 /
HIGH_VALUE
------------------------------
MAXVALUE
Hope this helps!
