4 Replies Latest reply: Feb 6, 2013 5:52 AM by KTOMAR RSS

    Oracle 11R2, Interval partition using timestamp with timezone

    KTOMAR
      Hi,

      I was able to create range partition on one of very big table where column used for partition is timestamp with local timezone. I was wondering if I can achieve interval partition using this column. Syntax used:

      CREATE TABLE TABLE_PART
      (
      ID NUMBER,
           LOB VARCHAR2(100 BYTE),
           REGION VARCHAR2(100 BYTE),
           ISSUE_TYPE VARCHAR2(100 BYTE),
      CREATION_DATE TIMESTAMP (6) WITH LOCAL TIME ZONE
      )
      PARTITION BY RANGE (creation_date)
      INTERVAL(NUMTOYMINTERVAL(1, 'YEAR'))
      (
      PARTITION t1 VALUES LESS THAN (TO_TIMESTAMP_TZ('01-JAN-2011 07:00:00 +5:30' ,'DD-MON-YYYY HH:MI:SS TZH:TZM')),
      PARTITION t2 VALUES LESS THAN (TO_TIMESTAMP_TZ('01-JAN-2012 07:00:00 +5:30' ,'DD-MON-YYYY HH:MI:SS TZH:TZM'))
      );


      I am getting error as : SQL Error: ORA-14751: Invalid data type for partitioning column of an interval partitioned table

      But in 11gR2 I can see timestamp with timezone is supported. Any help will be appreciated.