I have inherited a database that contains a large table (relative to the rest of the database - 10 million plus rows) of auditing data that needs to be retained for 60 days. Anything older than 60 days can be deleted.

I'm looking to partition this table by range (preferably by day), then run an event to keep things tidy by dropping partitions outside of the retention period.

The data in my table goes back around 6 months, so the first thing I'm going to do is run a manual delete on anything older than 60 days so I''m starting with a clean slate.

Then I need to create the partitions, but do I really have to list all my partitions in the alter table statement? i.e.

PARTITION part1 VALUES LESS THAN( TO_DAYS('2018-01-01 00:00:00') ), 
PARTITION part2 VALUES LESS THAN( TO_DAYS('2018-02-01 00:00:00') ), 
PARTITION part3 VALUES LESS THAN( TO_DAYS('2018-03-01 00:00:00') ), 
PARTITION part4 VALUES LESS THAN( TO_DAYS('2018-04-01 00:00:00') ), 
PARTITION part5 VALUES LESS THAN( TO_DAYS('2018-05-01 00:00:00') ),

... and so on

Is there a way to create the partition just by saying, partition the existing data by day?

I understand I will need to run a daily job to create a partition.


1 Answer 1


Unfortunately, no shortcut.

Do have an empty partition for "future" data; REORGANIZE to tomorrow and a fresh future. More discussion.

The ALTER will copy the entire table over, so be prepared for not being able to use the table for some time.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.