title: Oracle 11g Composite partition - Range | Hash
body: <p>I am trying to create a table and partition it by range then sub-partition by hash. But I am getting an error. I am doubt what is wrong in the script. When we do the range partition can we specify different number of sub-partitions for the hash, like below in my example ( partition OTHER_1 having a three sub-partitions while all other partitions having two).</p>
  
  <p><code></p>
  
  <p>CREATE TABLE ACCOUNTHOLDER_P (id INT, purchased DATE, OBJECT_TYPE  VARCHAR2(50), PHONE_NUMBER  VARCHAR2(50)) </p>
  
  <p>PARTITION BY RANGE (OBJECT_TYPE)<br>
    SUBPARTITION BY HASH(PHONE_NUMBER) (</p>
  
  <pre><code>PARTITION PARTNER_1 VALUES LESS THAN ('||''''||'Partner%'||''''||')  TABLESPACE USERS (     
    SUBPARTITION sp1 TABLESPACE ABC,
    SUBPARTITION sp2 TABLESPACE ABC
    ),
  
  PARTITION CONSUMER_1 VALUES LESS THAN ('||''''||'User%'||''''||')   TABLESPACE USERS ( 
    SUBPARTITION sp3 TABLESPACE XYZ,
    SUBPARTITION sp4 TABLESPACE XYZ
    ),
  
  PARTITION OTHER_1 VALUES LESS THAN (MAXVALUE)   TABLESPACE USERS (
    SUBPARTITION sp5 TABLESPACE KLM,
    SUBPARTITION sp6 TABLESPACE KLM,
    SUBPARTITION sp7 TABLESPACE KLM
    ));
  </code></pre>
  
  <p></code></p>
  
  <p>Error :</p>
  
  <p><code>
  SQL Error: ORA-00907: missing right parenthesis
  00907. 00000 -  "missing right parenthesis"
  *Cause:<br>
  *Action:
  </code></p>
  
  <p>thanks</p>
  
  <p>Regards,</p>
  
  <p>Era</p>