[20131125]ORA-14300.txt

[20131125]ORA-14300.txt

今天做一些测试,遇到ORA-14300,排除过程走了一点弯路,做一个记录:

SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SCOTT@test> create table t (id number, name varchar2(30)) partition by range (id) interval (100000) (partition p1 values less than (100000) ) ;
Table created.

SCOTT@test> select max(object_id),min(data_object_id) from dba_objects;
MAX(OBJECT_ID) MIN(DATA_OBJECT_ID)
-------------- -------------------
        280871                   0

--这样最多建立三个分区,如果执行如下:insert into t select object_id,object_name from dba_objects;但是实际上出现:

SCOTT@test> insert into t select object_id,object_name from dba_objects;
insert into t select object_id,object_name from dba_objects
*
ERROR at line 1:
ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions

SCOTT@test> !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.

--不可能建立这么多分区,为什么呢?google半天,感觉都不对,最后才想起来object_id可能含有NULL值.查询发现确实存在.

SCOTT@test>  select object_id,object_name from dba_objects where object_id is null ;
 OBJECT_ID OBJECT_NAME
---------- --------------------
           TEST1.COM

--单独做一个插入看看:
SCOTT@test> insert into t values (NULL,'test');
insert into t values (NULL,'test')
            *
ERROR at line 1:
ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions

--这个提示确实有点怪,不过简单想想很容易明白,如果建立的索引字段包含NULL在索引里面是排在最后的.执行如下OK:

SCOTT@test> insert into t select object_id,object_name from dba_objects where object_id is not null ;
77521 rows created.

SCOTT@test> commit ;
Commit complete.

SCOTT@test> select partition_name,compression,compress_for from user_tab_partitions where table_name='T';
PARTITION_NAME       COMPRESS COMPRESS_FOR
-------------------- -------- ------------
P1                   DISABLED
SYS_P3209            DISABLED
SYS_P3210            DISABLED

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-1061109/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-1061109/