create table pt_config
(owner varchar2(32),
table_name varchar2(32),
tablespace_name varchar2(32),
date_type varchar2(8),
force_split varchar2(1),
force_merge varchar2(1),
force_drop varchar2(1),
drop_physical varchar2(1),
intervall_adv number);
alter table pt_config
add constraint pt_config_pk
primary key (owner,table_name);
alter table pt_config
add constraint chk_date_type
check (date_type in ('WEEK','MONTH','DAY','YEAR'));
CREATE OR REPLACE PACKAGE psm_pt
-- AUTHID CURRENT_USER ist optional. Wird mit AUTHID CURRENT_USER compiliert
-- müssen dem jeweiligen ausführenden User alle notwendigen Rechte gegranted werden
--
-- AUTHID CURRENT_USER
--
as
-- psm_pt
-- 2009; Ingo Voland
--
-- change hist:
-- 08/2009 (IV) Partition-Merging in Monatspartitionen hinzugefügt
-- splitting einer maxvalue partition beim erstellen neuen partitionen hinzugefügt
--
-- 08/2009 (IV) Partitions auf Jahresbasis hinzugefügt
--
--03/2010 (IV) added Schema support
--04/2010 (IV) added truncate single partition support
--10/2010 (IV) added partition verification functions
-- added support for configuration tables into crpt
--
--
--
-- Package enthaelt Funktionalitaeten fuer Partitionsmanagement
-- benoetigt: Date oder CHAR(datebasierend) Range-Partitionierte Tabellen
--
-- Übersicht:
-- Das Pakage dieht dem Partitionsmanagement. Es legt Partitionen basierend auf
-- dem in Aufruf übergebenen Datum für die jeweils angegebene Tabelle an
-- Ebenfalls können Paritionen über truncate für einen bestimmten historischen
-- zeitraum geleert oder per drop gelöscht werden.
-- Die Proceduren um truncate /drop warnen, falls es global Indexe gibt, die bei
-- bei einer solchen Operation invalidiert werden.
--
-- Die Range-Partitioniert Tabelle kann sowohl nach einer Splate vom type Date7Timestamp
-- als auch nach einer Spalte vom Type char/varchar2 partitioniert sein.
--
-- die Partitionsnamen und Werte werden wie folgt vergeben_
--
-- Partitioniertungszeiträume / Namen bei einer DATE-Type partitionierten Tabelle
--
-- Zeitraum Partitionsname Partitionsvalue
-- Monat Pyyyymm -> (P200907) to_date(last_day(part_date) + 1) -> to_date('20090801','yyyymmdd')
-- Woche Pyyyy_ww -> (P2009_35) to_date(first_day_of_next_week(part_date)) -> to_date('20090907','yyyymmdd')
-- Tag Pyyyy_mm_dd -> (P2009_09_07) to_date(part_date + 1) -> to_date('20090908,'yyyymmdd')
--
--
-- Partitioniertungszeiträume / Namen bei einer CHAR-Type partitionierten Tabelle
--
-- Zeitraum Partitionsname Partitionsvalue
-- Monat Pyyyymm -> (P200908) to_char(last_day(part_date) )||'00' -> '20090900'
-- Woche Pyyyymmdd -> (P20100222) to_char(first_day_of_next_week(part_date)) -> '20100222'
-- Tag Pyyyymmdd -> (P20100310) to_char(part_date + 1) -> '20100311'
--
--
--
-- known issues
-- (1) execute sys.dbms_system.ksdwrt muss verfügbar sein
-- (2) wenn es mit authid current user kompiliert wird muss der jeweils ausfuehrende user alle notwendigen Rechte
-- (alter table) an den betroffenen Tabellen haben
-- erfolgt die Komilierung ohne authid current user müssen die rechte an alle zu betrachtenden tabellen
-- an den package owner gegranted werden
-- crpt
-- Procedure zum Erstellen neuer Partitionen
-- p_table_name -> Tabellen namen
-- p_date_type -> Partitioniertungszeiträume, gültige Werte sind 'DAY','WEEK' , 'MONTH'
-- p_day Max_Value für die Partition ( less than ...)
--
-- Sollte eine Tabelle über eine MAXVALUE partition verfügen wird geprüft,
-- ob die neue partition über einen split der MXVALUE partition erstellt werden kann.
-- global + local indexes werden über index rebuild neu aufgebaut. Sollten globale indexe
-- vorhanden sein kann dies zu einer erheblichen laufzeit führen
--
-- Beispiel:
-- psm_pt.crpt('MY_TABLE','WEEK',sysdate)
-- psm_pt.crpt('MY_TABLE','DAY',sysdate + 5 )
-- psm_pt.crpt('MY_TABLE','MONTH',to_date('20090701'))
--
PROCEDURE crpt (
p_table_name VARCHAR2,
p_date_type VARCHAR2 := 'WEEK',
p_day DATE := SYSDATE,
p_force BOOLEAN DEFAULT FALSE,
p_tablespace_name user_tab_partitions.tablespace_name%TYPE DEFAULT NULL
);
--
-- hier wird die konfiguratio aus der Tabelle pt_config genommen
--
PROCEDURE crpt;
-- clean_partitions
-- Procedure zum Löschen von Partitionen
-- p_table_name -> Tabellen Name
-- p_max_date -> Datum, bis zu dem die Partitionen per drop oder truncate gelöscht werden
-- force_drop -> die Procedure prüft auf das Vorhandensein globaler Indexe. Falls es diese
-- gibt wird eine Fehlermeldung gegeben. Wenn force_drop = TRUE
-- wird die Partition dennoch gelöscht bzw geleert, im Anschluß
-- muß der use aber den globalen index neu aufbauen
-- Der Default-Wert ist FALSE
-- drop_physicaly -> True führt ein DROP Partition aus, False führt ein Truncate Partition aus
--
-- Beispiel:
-- psm_pt.clean_partitions('MY_TABLE',sysdate - 50, false, true);
--
PROCEDURE clean_partitions (
p_table_name VARCHAR2,
p_max_date DATE DEFAULT SYSDATE - 180,
force_drop IN BOOLEAN DEFAULT FALSE,
drop_physicaly IN BOOLEAN DEFAULT FALSE
);
-- merge_partitions
-- Procedure zum Mergen von Partitionen. hierbei werden Tage oder Wochenpartitionen zu Monatspartitionen
-- zuammen gefügt. Die Procedure führt ein rebuild der betroffenen lokalen Indexe aus
-- Vorhandene globale Indexe führen, falls nicht 'force_merge=true' gesetzt ist, zu Fehlermeldungen.
-- Wenn mit force_merge gearbeitet wird müssen eventuelle globale indexe neu validiert werden
--
-- Es ist empfehlenswert, das max-date immer der letzte Tage eines betroffenen Monats ist. In diesem Fall
-- erfolgt auch einen Umbenennung der Partition in das Monats-Partitionen-Namensschema
--
-- p_table_name -> Tabellen Name
-- p_min_date -> Datum, ab dem die Partitionen per merge zuammen geführt werden (unteres Limit)
-- p_max_date -> Datum, bis zu dem die Partitionen per merge zuammen geführt werden (oberes Limit)
-- force_merge -> die Procedure prüft auf das Vorhandensein globaler Indexe. Falls es diese
-- gibt wird eine Fehlermeldung gegeben. Wenn force_merge = TRUE
-- wird die Partition dennoch gelöscht bzw geleert, im Anschluß
-- muß der use aber den globalen index neu aufbauen
-- Der Default-Wert ist FALSE
--
-- Beispiel:
-- psm_pt.merge_partitions('MY_TABLE',sysdate - 80, last_day(sysdate -30), false, true);
--
PROCEDURE clean_partitions (
p_table_name VARCHAR2,
p_part VARCHAR2,
force_drop IN BOOLEAN DEFAULT FALSE
);
-- clean PArtition ist overloaded und kann auch mit einer einzelnen partition verwendet werden
PROCEDURE merge_partitions (
p_table_name VARCHAR2,
p_min_date DATE DEFAULT SYSDATE - 180,
p_max_date DATE DEFAULT SYSDATE - 60,
force_merge IN BOOLEAN DEFAULT FALSE
);
--
-- Intern genutzte definitionen
--
PROCEDURE truncate_sub_part (
p_table_name VARCHAR2,
p_sub_part VARCHAR2,
force_drop IN BOOLEAN DEFAULT FALSE
);
PROCEDURE drop_part (
p_table_name VARCHAR2,
p_part VARCHAR2,
force_drop IN BOOLEAN DEFAULT FALSE
);
--
-- Funktionen zum Prüfen der Verfügbarkeit der Partitionen
-- Es wird geprüft, ob eine Partition für das Datum p_min_required_date in der Tabelle enthalten ist.
-- Mögliche Rückgabewerte sind :
--
-- OK: partition Pxxxxxx for user.table exisiert
-- Warn: Maxvalue for user.table exisiert
-- ERROR: Partition missing for user.table
--
-- Im Falle einer fehlenden Parition wird ein Eintrag in den Oracle Alert Log geschrieben.
--
-- Verwendungsbeispiele:
-- select * from table (psm_pt.f_check_partitions('USERNAME.TABLE_NAME',sysdate +10))
-- select * from table (psm_pt.f_check_partitions(sysdate +100)
--
-- Wird kein Tabellen Name explizit vergeben werden alle über all_tables gefundenen
-- partitionierten Tabellen geprüft.
--
--
--
FUNCTION f_check_partitions (
p_table_name IN VARCHAR2,
p_min_required_date IN DATE DEFAULT SYSDATE + 7
)
RETURN xdb.xdb$string_list_t
PIPELINED;
FUNCTION f_check_partitions (
p_min_required_date IN DATE DEFAULT SYSDATE + 7
)
RETURN xdb.xdb$string_list_t
PIPELINED;
FUNCTION show_version
RETURN VARCHAR2;
END psm_pt;
/