How to get current time in Oracle, and use in interval partition? I'm trying to create an interval partitioned table in Oracle where the first partition is bounded to a week from now, but I just can't seem to figure out the syntax. 

Here's what I have so far..

CREATE TABLE TRANSACTION
(
....
, TRANSACTION_DATETIME TIMESTAMP(6) NOT NULL
....
)
PARTITION BY RANGE(TRANSACTION_DATETIME)
INTERVAL (NUMTODSINTERVAL(7,'day'))
(PARTITION p_first VALUES LESS THAN (SELECT SYSDATE+7 FROM DUAL));


I've tried that and many other variations, such as..

(PARTITION p_first VALUES LESS THAN (SYSDATE+7));  //OR
(PARTITION p_first VALUES LESS THAN (SYSDATE+7 FROM DUAL));


But none of them seem to work
You can not do that. You need to specify the date yourself, as that clause accepts only literals or MAXVALUE, and not expressions.



So you could just use:

(PARTITION p_first VALUES LESS THAN (DATE'2015-12-12'));

If you really want to calculate it dynamically, you need to use dynamic SQL.