Monday, February 21, 2022

[SOLVED] How can I partition a database table every month?

Issue

Every month we got to partition a table for said month´s transaction, and we want to automatize this process, I'm thinking about using a crontab, but the problem lies in the fact that I need some parameters like date and partition table to do so.

This is a server running SunOS wich I can't use a shell other than the default sh, which makes a bit painful to program a shell script to run the query, but nonetheless that's what I want.

This is how we partition our table:

ALTER TABLE SCHEME.TABLE ADD PARTITION "NAME_201905"  VALUES LESS THAN
(TO_DATE('2019-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  COMPRESS FOR OLTP LOGGING
  STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TABLESPACE";

We need a way to execute that query every month and every parameter should match the current date.


Solution

create or alter table to have interval partition. This requirement will be taken care of automatically.

you can set interval of hour, day , week, month or year.

each time a new month's data comes Oracle automatically creates a new partition.



Answered By - Hijesh Vl
Answer Checked By - Senaida (WPSolving Volunteer)
at