Oracle database partition manager using PL/SQL
22 Dec 2016
Maintaining partitions is one of the common (and mundane) tasks in a database. Since most data warehouses have time-series data, a lot of these cases involve date-range partitions. I wrote the below script/framework that automates some of those boring parts, based on scenarios/requirements I have seen in real-life.
These are some of the high level requiremetns that I wanted to be handled.
- Have a configuration table that has the rules for the partitions
- Allow partition name and format to be configured using a template (only dates for now). For example P_{YYYY}_{MM} would get translated to P_2016_04
- Allow Table space name and format to be configured (to allow for year wise table spaces, for example) provide procedures to generate initial set of partitions or just the missing partitions.
Here’s the metadata table that holds the partition metadata and configuration.
CREATE TABLE partition_metadata(
OWNER VARCHAR2(30) NOT NULL,
TABLE_NAME VARCHAR2(30) NOT NULL,
PARTITION_FREQUENCY VARCHAR2(30) NOT NULL,
PARTITION_NAME_FORMAT VARCHAR2(30) NOT NULL,
PARTITION_TS_FORMAT VARCHAR2(30) NOT NULL,
FIRST_PARTITION_NAME VARCHAR2(30) NOT NULL,
MAXVAL_PARTITION_NAME VARCHAR2(30) NOT NULL,
MAXVAL_TS_NAME_FORMAT VARCHAR2(30) NOT NULL,
NUM_ADVANCE_PARTITIONS NUMBER DEFAULT 1 NOT NULL,
ACTIVE_RECORD_FLAG VARCHAR2(1) DEFAULT 'Y' NOT NULL,
DW_UPDATED_BY VARCHAR2(30) NOT NULL,
DW_LAST_UPDATED DATE NOT NULL
);
Next, we look at some test data. The following examples add daily and monthly configuration.
INSERT INTO PARTITION_METADATA (OWNER,TABLE_NAME,PARTITION_FREQUENCY,PARTITION_NAME_FORMAT,PARTITION_TS_FORMAT,FIRST_PARTITION_NAME,MAXVAL_PARTITION_NAME,MAXVAL_TS_NAME_FORMAT,NUM_ADVANCE_PARTITIONS,ACTIVE_RECORD_FLAG,DW_UPDATED_BY,DW_LAST_UPDATED)
VALUES ('DWH','SALES','DAILY','P{yyyy}{mm}{dd}','DWH_SALES_DAT','P20100101','PMAX','DWH_SALES_DATA',10,'Y','DBMAINTUSER',sysdate);
INSERT INTO PARTITION_METADATA (OWNER,TABLE_NAME,PARTITION_FREQUENCY,PARTITION_NAME_FORMAT,PARTITION_TS_FORMAT,FIRST_PARTITION_NAME,MAXVAL_PARTITION_NAME,MAXVAL_TS_NAME_FORMAT,NUM_ADVANCE_PARTITIONS,ACTIVE_RECORD_FLAG,DW_UPDATED_BY,DW_LAST_UPDATED)
VALUES ('DWH','SALES_MONTHLY','MONTHLY','P{yyyy}{mm}','DWH_SALES_DATA','P20100101','PMAX','DWH_SALES_DATA',2,'Y','DBMAINTUSER',sysdate);
commit;
The final package has the following stub. The complete packge body (including the scripts) are on github.
CREATE OR REPLACE package pkg_partition_manager
AS
PROCEDURE p_create_missing_partitions(
p_owner IN partition_metadata.owner%TYPE,
p_table_name IN partition_metadata.table_name%TYPE
);
END;
/
Couple of things to notice.
1) it uses the Oracle partition metadata to check for already existing partitions, stored in dba_partititions.
2) connect by clause to generate the partitions, based on the configuration present in the partition_metadata table.
The partial end result is a query such as this, which does a minus between the expected and existing partitions to get the missing partitions.
WITH all_partitions AS (
SELECT REPLACE(REPLACE('P{yyyy}{mm}',
'{mm}',
to_char(add_months(to_date('01-JAN-2010 00:00:00'),level,'mm')
),'{yyyy}',
to_char(add_months(to_date('01-JAN-2010 00:00:00'),level),'yyyy'
) partition_name,
add_months(to_date('01-JAN-2010 00:00:00'),level) + 1 partition_maxval,
level partition_position
FROM dual
CONNECT BY level < trunc(months_between(sysdate,to_date('01-JAN-2010 00:00:00'))) + 2
), current_partitions AS (
SELECT partition_name FROM all_tab_partitions
WHERE table_owner = 'DWH'
AND TABLE_NAME = 'SALES_MONTHLY')
SELECT ap.partition_name, ap.partition_maxval,
'alter table DWH.SALES_MONTHLY
split partition PMAX at ( to_date(''' || ap.partition_maxval || ''')) into (partition ' ||
ap.partition_name || ' ,partition PMAX) update global indexes;'
FROM current_partitions cp, all_partitions ap
WHERE ap.partition_name = cp.partition_name(+)
AND cp.partition_name IS NULL
ORDER BY partition_position ASC;
This spits out the split statements necessary to create the partitions. output varies based on whether the partitions are daily or monthly. You could run the final commands using execute immediate if needed.
BEGIN
pkg_partition_manager.p_create_missing_partitions (
p_owner => 'DWH',
p_table_name => 'SALES' -- daily partitoned table
);
END;
/
ALTER TABLE DWH.SALES split partition PMAX at ( to_date('03-JAN-2010 00:00:00'))
INTO (partition P20100102 ,partition PMAX) UPDATE global indexes;
ALTER TABLE DWH.SALES split partition PMAX at ( to_date('04-JAN-2010 00:00:00'))
INTO (partition P20100103 ,partition PMAX) UPDATE global indexes;
ALTER TABLE DWH.SALES split partition PMAX at ( to_date('05-JAN-2010 00:00:00'))
INTO (partition P20100104 ,partition PMAX) UPDATE global indexes;
BEGIN
pkg_partition_manager.p_create_missing_partitions (
p_owner => 'DWH',
p_table_name => 'SALES_MONTHLY' -- monthly partitioned table.
);
END;
/
ALTER TABLE DWH.SALES_MONTHLY split partition PMAX at ( to_date('01-FEB-2010 00:00:00'))
INTO (partition P201002 ,partition PMAX) UPDATE global indexes;
ALTER TABLE DWH.SALES_MONTHLY split partition PMAX at ( to_date('01-MAR-2010 00:00:00'))
INTO (partition P201003 ,partition PMAX) UPDATE global indexes;
ALTER TABLE DWH.SALES_MONTHLY split partition PMAX at ( to_date('01-APR-2010 00:00:00'))
INTO (partition P201004 ,partition PMAX) UPDATE global indexes;
At the moment, this code only takes care of range partitioned tables and only allows daily and monthly partitions. In reality, there are a lot more types (hash, composite partitioning using Range+Hash, Range+List, for eaxmple.). I will add support for them later, if there is ever a need for it.
This was, of course, very specific for my needs. Some of the tables had missing partitions, there were different versions of this query in multiple places and sometimes the logic was inconsistent (fixed 30-day months for example).
However, I think some of the ideas such as using “connect by” to generate the names and template based naming are useful for other use cases and I hope you find it useful.