asked on

Adding multiple partition to a oracle table

Hello Experts,

I need an urgent help on adding partition to a table.
Here is my code:

 CREATE TABLE TEST 
    (
    COL1 VARCHAR2(10),
    COL2 VARCHAR2(32) GENERATED ALWAYS AS (SUBSTR(TRIM(TIMESTAMP), 1, 8)) VIRTUAL NOT NULL,
    col3 VARCHAR2(32) GENERATED ALWAYS AS (SUBSTR(TRIM(TIMESTAMP), 1, 6)) VIRTUAL NOT NULL
    )
    PARTITION BY RANGE(COL2)(PARTITION P20120822 VALUES LESS THAN (20120823)),
    PARTITION BY RANGE(col3)(PARTITION P201206 VALUES LESS THAN (20120823)),
    ;

Open in new window

But I get the error by for two partition cannot be done , so what would be the best solution to get the range partition on two columns ?

slightwv (䄆 Netminder)

You can only partition a table by a single item.

You might need to use sub-partitions on the second value if you need that many partitions.

http://ora-14027.ora-code.com/

Swadhin Ray

ASKER

can we do a sub partition on the range for two different columns ?

slightwv (䄆 Netminder)

You partition on one range for col2 then you should be able to subpartition by col3 or col3 then col2.

Check out:
Composite Partitioned Table - By Range And Range

http://psoug.org/reference/partitions.html

Swadhin Ray

ASKER

I am getting syntax error for the below code .
I am doing something really wrong on this:

CREATE TABLE TEST_T
  (
    COL1 VARCHAR2(10),
    COL2 VARCHAR2(32) GENERATED ALWAYS AS (SUBSTR(TRIM(TIMESTAMP), 1, 8)) VIRTUAL NOT NULL,
    col3 VARCHAR2(32) GENERATED ALWAYS AS (SUBSTR(TRIM(TIMESTAMP), 1, 6)) VIRTUAL NOT NULL
  )
  PARTITION BY RANGE
  (
    COL2
  )
  SUBPARTITION BY RANGE
  (
    COL3
  )
  SUBPARTITION TEMPLATE
  (
    SUBPARTITION P201206 VALUES LESS THAN (20120823)
  )
  PARTITION P20120822 VALUES LESS THAN
  (
    20120823
  ) ;

Open in new window

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial