ORA-14160: this physical attribute may not be specified for a table subpartition
The ORA-14160 this physical attribute may not be specified for a table subpartition erro occurred when I forgot to add the comma and the end of each subpartition specification. 9 times out of 10 this error will be the result of a typo or incorrectly specified table definition.
In the example below, commas should be after (5,7) and (3), as highlighted in red.
ERROR at line 19:
ORA-14160: this physical attribute may not be specified for a table subpartition
CREATE TABLE Queue( Queue_ID Number(10,0), Queue_Status_ID Number(10,0) CONSTRAINT Queue_NN01 NOT NULL, Queue_Source_ID Number(10,0) CONSTRAINT Queue_NN02 NOT NULL, Source_Value Varchar2(100 CHAR), Identifier Varchar2(100 CHAR) CONSTRAINT Queue_NN03 NOT NULL, Request_Message Clob, Result_Message Clob, Processing_Started Timestamp(3), Processing_Finished Timestamp(3), Tmstamp_Added Timestamp(3) DEFAULT CURRENT_TIMESTAMP CONSTRAINT Queue_NN04 NOT NULL, Tmstamp_Updated Timestamp(3) DEFAULT CURRENT_TIMESTAMP CONSTRAINT Queue_NN05 NOT NULL, User_Added Varchar2(100 CHAR) CONSTRAINT Queue_NN06 NOT NULL, User_Updated Varchar2(100 CHAR) CONSTRAINT Queue_NN07 NOT NULL) PARTITION BY RANGE(Tmstamp_Updated) SUBPARTITION BY LIST (Queue_Source_ID) SUBPARTITION TEMPLATE ( SUBPARTITION xmlonaccept_part VALUES('5','7'), SUBPARTITION autorenewals_part VALUES('3'), SUBPARTITION uploadonaccept_part VALUES('8')) (PARTITION queue_PDEC_12 VALUES LESS THAN (TO_DATE('01-JAN-2013','DD-MON-YYYY')), PARTITION queue_PJAN_13 VALUES LESS THAN (TO_DATE('01-FEB-2013','DD-MON-YYYY')));
Originally, I had wanted to do list-range partitioning because I thought it made more sense for the project I was working on, but then I found out that it’s not possible to do list-range partitioning in 10g, only range-list partitioning. You have to be klonopin risk depression on 11g to use list-range and unfortunately we are running Oracle 10g.
Like it, share it...
Category: Partitioning