Search code examples
sqloracle-databasedatabase-partitioning

Partition by Week - Oracle Database

I was wondering if you can help me figure out and deal with partitioning by week in Oracle.

My Situation is that I have a lot of data which is coming per day and I would like to make partitions per week. I will be also using sysdate when inserting the data to the DB in order to make the record unique.

Should I write them manually? If yes, how should it look? Should I still use something like: VALUES LESS THAN?

Really looking forward to hearing your opinions!

Solution

  • Weekly partition is a problem but you can create interval partition for 7 days. Oracle will automatically create new partition for new entries. 

    create table TEST_partition
(
  ID              number(4, 0)
, creation_date   date
)
partition by range  (creation_date)
  interval ( numtodsinterval(7, 'day') )
  (partition TEST_P1 values less than (to_date(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));