How to acheive Day and Hourly partitioning in Oracle 11g?

I am using Oracle-11G database.

I am trying to create day wise partition and again sub-partition on hourly level.

Below is the script I am using

CREATE TABLE test_shipments
( order_date    DATE NOT NULL
, delivery_date DATE NOT NULL
)
PARTITION BY RANGE (order_date)
INTERVAL (NUMTODSINTERVAL(1, 'day'))
SUBPARTITION BY RANGE (delivery_date)
( PARTITION p_2006_jul VALUES LESS THAN (TO_DATE('2006-08-01','YYYY-MM-DD'))
  ( SUBPARTITION p06_jul_e1 VALUES LESS THAN (TO_DATE('2006-08-01 12:00:00','YYYY-MM-DD hh24:mi:ss')) 
  ,SUBPARTITION p06_jul_e2 VALUES LESS THAN (TO_DATE('2006-08-01 13:00:00','YYYY-MM-DD hh24:mi:ss')) 
  ,SUBPARTITION p06_jul_e3 VALUES LESS THAN (TO_DATE('2006-08-01 14:00:00','YYYY-MM-DD hh24:mi:ss')) 
  )
  );

I am expecting to have partition for every day and again sub-partition for every hour in that day. The above script creates partition per day only. Hourly sub-partition is not getting created. Can anyone please guide?


Source: oracle

Leave a Reply