I tried to create a range partition for each month and tried the following script , but it throws me error stating Missing Right Parenthesis . I have tried it in 10g and 11g both throws error . This extract function works fine with timestamp , but when it is used inside range definition its throwing error . Could someone help me out ?
SQL> CREATE TABLE TST_PART
2 (
3 pid NUMBER,
4 trans_dt TIMESTAMP(6)
5 )
6 PARTITION BY RANGE (extract(MONTH from TRANS_DT))
7 (
8 PARTITION P1 VALUES LESS THAN (2) TABLESPACE T1,
9 PARTITION P2 VALUES LESS THAN (MAXVALUE) TABLESPACE T2
10 );
PARTITION BY RANGE (extract(MONTH from TRANS_DT))
*
ERROR at line 6:
ORA-00907: missing right parenthesis