Oracle Database Discussions

Hi All,

I need to implement automatic table partitioning in Oracle 11g version, but partitioning interval should be on daily basis(For every day).

I was able to perform this for Monthly and Yearly but not on daily basis.

create table part
(a date)PARTITION BY RANGE (a)
INTERVAL (NUMTOYMINTERVAL(1,'*MONTH*'))
(partition p1 values less than (TO_DATE('01-NOV-2007','DD-MON-YYYY'))
);

Table created

create table part
(a date)PARTITION BY RANGE (a)
INTERVAL (NUMTOYMINTERVAL(1,'*YEAR*'))
(partition p1 values less than (TO_DATE('01-NOV-2007','DD-MON-YYYY'))
);

Table created

But if i use DD or DAY instead of YEAR or MONTH it fails......Please suggest me how to perform this on daily basis.

SQL> 
  1  create table part
  2  (a date)PARTITION BY RANGE (a)
  3  INTERVAL (NUMTOYMINTERVAL(1,'*DAY*'))
  4  (partition p1 values less than (TO_DATE('01-NOV-2007','DD-MON-YYYY'))
  5* )
SQL> /
INTERVAL (NUMTOYMINTERVAL(1,'DAY'))
                            *
ERROR at line 3:
ORA-14752: Interval expression is not a constant of the correct type


SQL> create table part
(a date)PARTITION BY RANGE (a)
INTERVAL (NUMTOYMINTERVAL(1,'*DD*'))
(partition p1 values less than (TO_DATE('01-NOV-2007','DD-MON-YYYY'))
);  2    3    4    5
INTERVAL (NUMTOYMINTERVAL(1,'DD'))
                            *
ERROR at line 3:
ORA-14752: Interval expression is not a constant of the correct type

Please suggest me to resolve this ORA-14752 error for using DAY or DD or HH24

-Yasser