CODEWITHSUNDEEP
oracle-databaseoracle12cdynamic-sqldatabase-partitioning
Kanat Seytov
Kanat Seytov

Reputation: 1

Partition by range in oracle using sysdate

I want to create partitions dynamicaly that depends on sysdate, like: 

create table partition_interval_demo
( id         number(6)
, val        varchar2(50)
, date_create  date)
partition by range (sale_date) interval (interval '1' day)
( partition p1 values less than ((sysdate-90)));

but I have error like

"ORA-14019: partition bound element must be one of: string, datetime or interval literal, number, or MAXVALUE"

so what can I do here?

Upvotes: 0

Views: 3369

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59436

You can use

partition p1 values less than (DATE '2021-01-01')

If your application adds data from let's say 2021-08-01, then Oracle will create single new partition for 2021-08-01, it can deal with gaps or missing days.

If you use SEGMENT CREATION DEFERRED clause (maybe meanwhile the default) then the initial partition does not even consume any space on your disk.

Upvotes: 0

Roberto Hernandez
Roberto Hernandez

Reputation: 8518

You can't do that. Following the documentation

Creating a Range-Partitioned Table

The PARTITION BY RANGE clause of the CREATE TABLE statement specifies that the table or index is to be range-partitioned. The PARTITION clauses identify the individual partition ranges, and the optional subclauses of a PARTITION clause can specify physical and other attributes specific to a partition segment. If not overridden at the partition level, partitions inherit the attributes of their underlying table.

The VALUES LESS THAN clause determines the partition bound: rows with partitioning key values that compare less than the ordered list of values specified by the clause are stored in the partition. Each partition is given a name (sales_q1, sales_q2, ...), and each partition is contained in a separate tablespace (tsa, tsb, ...).

That means that the range is determined for the key value. sysdate is a function, therefore it is not a value until it is executed. It can't be part of a range partition key value.

Keep in mind the purpose or partitioning. The idea is to split a big segment ( table ) into smaller ones ( partitions ) using a key column. In your case, you want to store in one partition the rows where the key column is less than sysdate-90, which means that today the row will go to one partition, but in one day if the condition sysdate-90 is met, the row should be moved to the other partition.

If you want to do this anyway, although I don't recommend it, you will have to develop your own maintenance process:

  1. The table has keys for each date ( 3 months of partitions for each date)
  2. The Partition others will get everything else, so anything that has no match with any of the existing partition keys.
  3. Every day you will have to retrieve the records that are meeting the condition sysdate-90 and move them to the others partition.
  4. For doing that you need to enable row movement in your table: alter table xx enable row movement

Hope it clarifies

Upvotes: 1

Related Questions