CODEWITHSUNDEEP
oracle-databaseoracle11g
Saurye
Saurye

Reputation: 27

"Dynamic" partitions in oracle 11g

I have a log table with a lot of information. I would like to partition it into two: first part is the logs from the past month, since they are commonly viewed. Second part is the logs from the rest of the year (Compressed).

My problem is that all the examples of partitions where "up until 1/1/2013", "more recent than 1/1/2013" - That is with fixed dates...

What I am looking for/expecting is a way to define a partition on the last month, so that when the day changes, the logs from 30 days ago, are "automatically" transferred to the compressed partition.

I guess I can create another table which is completley compressed and move info using JOBS, but I was hoping for a built-in solution.

Thank you.

Upvotes: 1

Views: 3464

Answers (2)

Kombajn zbożowy
Kombajn zbożowy

Reputation: 10693

It is not possible to automatically transfer data to a compressed partition. You can, however, schedule a simple job to compress last month's partition at the beginning of every month with this statement:

ALTER TABLE some_table
MOVE PARTITION FOR (add_months(trunc(SYSDATE), -1)
COMPRESS;

If you wanted to stay with only two partitions: current month and archive for all past transactions you could also merge partitions with ALTER TABLE MERGE PARTITIONS, but as far as I'm concerned it would rebuild the whole archive partition, so I would discourage doing so and stay with storing each month in its separate partition.

Upvotes: 0

tbone
tbone

Reputation: 15473

I think you want interval partitions based on a date. This will automatically generate the partitions for you. For example, monthly partitions would be:

create table test_data (
   created_date      DATE default sysdate not null,
   store_id          NUMBER,
   inventory_id      NUMBER,
   qty_sold          NUMBER
)
PARTITION BY RANGE (created_date)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(
   PARTITION part_01 values LESS THAN (TO_DATE('20130101','YYYYMMDD'))
)

As data is inserted, Oracle will put into the proper partition or create one if needed. The partition names will be a bit cryptic (SYS_xxxx), but you can use the "partition for" clause to grab only the month you want. For example:

select * from test_data partition for (to_date('20130101', 'YYYYMMDD'))

Upvotes: 4

Related Questions