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