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;
/


CREATE OR REPLACE PACKAGE BODY psm_pt AS
	c_version			 CONSTANT VARCHAR2 (32) := '03.10 / 20101030';
	v_stmt							 VARCHAR2 (1000);
	char_partitioned				 BOOLEAN;
	p_lst_day_ident				 VARCHAR2 (2) := '00';
	c_first_day 		 CONSTANT DATE := TO_DATE ('00010101', 'yyyymmdd');

	e_invalid_input				 EXCEPTION;

	b_check_partition_success	 BOOLEAN DEFAULT FALSE;

	TYPE t_part_merge IS TABLE OF user_tab_partitions.partition_name%TYPE
									INDEX BY PLS_INTEGER;

	my_part_merge					 t_part_merge;

	TYPE t_tab_ident IS RECORD
	(
		owner 		 all_tables.owner%TYPE,
		table_name	 all_tables.table_name%TYPE
	);

	my_tab_ident					 t_tab_ident;


	CURSOR c_tab_partitions (
		owner 				  VARCHAR2,
		tab_name 			  VARCHAR2,
		p_partition_name	  VARCHAR2
	) IS
		SELECT	COUNT (*)
		  FROM	all_tab_partitions
		 WHERE		 table_name = UPPER (tab_name)
					AND table_owner = UPPER (owner)
					AND partition_name = UPPER (p_partition_name);

	--
	--

	PROCEDURE set_tab_ident (v_tab_ident IN VARCHAR2) IS
	BEGIN
		my_tab_ident.owner :=
			UPPER (
				NVL (
					SUBSTR (
						TRIM (v_tab_ident),
						1,
						INSTR (TRIM (v_tab_ident), '.') - 1
					),
					USER
				)
			);
		my_tab_ident.table_name :=
			UPPER (
				SUBSTR (
					TRIM (v_tab_ident),
					INSTR (TRIM (v_tab_ident), '.') + 1,
					100
				)
			);
		pkg_common_logging.write_log (
			'table owner: ' || my_tab_ident.owner,
			pkg_common_logging.set_debug
		);
		pkg_common_logging.write_log (
			'table_name: ' || my_tab_ident.table_name,
			pkg_common_logging.set_debug
		);
	END;

	PROCEDURE write_alert_log (
		v_error_code	IN NUMBER,
		v_error_msg 	IN VARCHAR2
	) IS
		v_line	VARCHAR2 (1000);
	BEGIN
		v_line :=
			TO_CHAR (
				SYSDATE,
				'Dy Mon dd hh24:mi:ss yyyy',
				'NLS_DATE_LANGUAGE = AMERICAN'
			)
			|| CHR (10)
			|| 'ORA'
			|| v_error_code
			|| ' warning: '
			|| v_error_msg;

		sys.DBMS_SYSTEM.ksdwrt (2, v_line);
		sys.DBMS_SYSTEM.ksdwrt (2, 'Partition split/extenesion is needed');
	END;



	FUNCTION check_global_index (p_table_name VARCHAR2, force_drop IN BOOLEAN)
		RETURN BOOLEAN IS
		e_global_index   EXCEPTION;
		v_id				  NUMBER;
	BEGIN
		-- check global indexes
		SELECT	COUNT (*)
		  INTO	v_id
		  FROM	all_indexes
		 WHERE		 partitioned <> 'YES'
					AND table_name = my_tab_ident.table_name
					AND index_type <> 'LOB'
					AND owner = my_tab_ident.owner;

		pkg_common_logging.write_log (
			'global indexes found: ' || TO_CHAR (v_id),
			pkg_common_logging.set_debug
		);

		-- bail out if global index exists
		IF (v_id > 0 AND NOT force_drop) THEN
			pkg_common_logging.write_log (
				'force_drop must be true in oder to modify partitions',
				pkg_common_logging.set_info
			);
			RAISE e_global_index;
		END IF;

		RETURN TRUE;
	EXCEPTION
		WHEN e_global_index THEN
			DBMS_OUTPUT.put_line (
				'Global Indexes exists, can not drop a partition without index invalidation'
			);
			RETURN FALSE;
		WHEN OTHERS THEN
			RAISE;
	END check_global_index;



	FUNCTION part_column_type (p_table_name IN VARCHAR2)
		RETURN VARCHAR2 IS
		v_ret   VARCHAR2 (32);
	BEGIN
		pkg_common_logging.set_client_info ('part_column_type');

		v_stmt :=
				'select b.data_type from '
			|| 'ALL_PART_KEY_COLUMNS a, '
			|| 'all_tab_columns b '
			|| 'where object_type = :b1 and '
			|| 'a.name = b.table_name and '
			|| 'a.column_name = b.column_name and '
			|| 'a.owner = b.owner and a.owner = :b2 and '
			|| 'a.name = :b3';
		pkg_common_logging.write_log (v_stmt, pkg_common_logging.set_debug);

		EXECUTE IMMEDIATE v_stmt
			INTO v_ret
			USING 'TABLE', my_tab_ident.owner, my_tab_ident.table_name;

		pkg_common_logging.write_log (
			'Return: ' || v_ret,
			pkg_common_logging.set_debug
		);

		RETURN v_ret;
	EXCEPTION
		WHEN NO_DATA_FOUND THEN
			pkg_common_logging.write_log (
				'Table not found!' || SQLERRM,
				pkg_common_logging.set_info
			);
			RAISE;
		WHEN OTHERS THEN
			pkg_common_logging.write_log (
				'psm_pt.part_column_type error:' || SQLERRM,
				pkg_common_logging.set_info
			);
			RAISE;
	END part_column_type;


	-------------------------------------------------------------------------------
	-- show partition name
	-------------------------------------------------------------------------------
	FUNCTION sh_partition_name (
		p_day 							DATE,
		p_date_type 					VARCHAR2 := 'DAY',
		p_partition_name_prefix 	VARCHAR2 := 'P'
	)
		RETURN VARCHAR2 IS
		l_holiday_days   INTEGER;
		v_ret 			  VARCHAR2 (32);
	BEGIN
		pkg_common_logging.set_client_info ('sh_partition_name');


		IF char_partitioned THEN
			v_ret :=
				CASE UPPER (p_date_type)
					WHEN 'DAY' THEN
						p_partition_name_prefix
						|| TO_CHAR (
								p_day,
								'yyyymmdd',
								'NLS_DATE_LANGUAGE = German'
							)
					WHEN 'MONTH' THEN
						p_partition_name_prefix
						|| TO_CHAR (p_day, 'yyyymm', 'NLS_DATE_LANGUAGE = German')
					WHEN 'WEEK' THEN
						p_partition_name_prefix
						|| TO_CHAR (
								TRUNC (p_day, 'DAY') + 7,
								'yyyymmdd',
								'NLS_DATE_LANGUAGE = German'
							)
					WHEN 'YEAR' THEN
						p_partition_name_prefix
						|| TO_CHAR (
								TRUNC (p_day, 'yyyy'),
								'yyyy',
								'NLS_DATE_LANGUAGE = German'
							)
				END;
		ELSE
			v_ret :=
				CASE UPPER (p_date_type)
					WHEN 'DAY' THEN
						p_partition_name_prefix
						|| TO_CHAR (
								p_day,
								'yyyy_mm_dd',
								'NLS_DATE_LANGUAGE = German'
							)
					WHEN 'MONTH' THEN
						p_partition_name_prefix
						|| UPPER (
								TO_CHAR (
									p_day,
									'yyyymm',
									'NLS_DATE_LANGUAGE = German'
								)
							)
					WHEN 'WEEK' THEN
						p_partition_name_prefix
						|| UPPER (
								TO_CHAR (
									p_day,
									'yyyy_ww',
									'NLS_DATE_LANGUAGE = German'
								)
							)
					WHEN 'YEAR' THEN
						p_partition_name_prefix
						|| TO_CHAR (
								TRUNC (p_day, 'yyyy'),
								'yyyy',
								'NLS_DATE_LANGUAGE = German'
							)
				END;
		END IF;

		pkg_common_logging.write_log (
			'sh_partition_name Return: ' || v_ret,
			pkg_common_logging.set_debug
		);

		RETURN v_ret;
	EXCEPTION
		WHEN OTHERS THEN
			RAISE;
	END sh_partition_name;

	--
	--
	-------------------------------------------------------------------------------
	-- show partition name
	-------------------------------------------------------------------------------
	FUNCTION sh_partitioning_key_value (
		p_day 			DATE,
		p_date_type 	VARCHAR2 := 'WEEK'
	)
		RETURN VARCHAR2 IS
		v_ret   VARCHAR2 (128);
	BEGIN
		pkg_common_logging.set_client_info ('sh_partitioning_key_value');


		IF char_partitioned THEN
			v_ret :=
				CASE UPPER (p_date_type)
					WHEN 'DAY' THEN
						'''' || TO_CHAR (p_day + 1, 'yyyymmdd') || ''''
					WHEN 'MONTH' THEN
							''''
						|| TO_CHAR (ADD_MONTHS (p_day, 1), 'yyyymm')
						|| p_lst_day_ident
						|| ''''
					WHEN 'WEEK' THEN
							''''
						|| TO_CHAR (TRUNC (p_day, 'DAY') + 7, 'yyyymmdd')
						|| ''''
					WHEN 'YEAR' THEN
						''''
						|| TO_CHAR (
								TRUNC (ADD_MONTHS (p_day, 12), 'yyyy'),
								'yyyymmdd'
							)
						|| ''''
				END;
		ELSE
			v_ret :=
				CASE UPPER (p_date_type)
					WHEN 'DAY' THEN
							'to_date ('
						|| ''''
						|| TO_CHAR (p_day + 1, 'yyyy_mm_dd')
						|| ''''
						|| ',''yyyy_mm_dd'')'
					WHEN 'MONTH' THEN
						'to_date (' || ''''
						|| TO_CHAR (
								TRUNC (ADD_MONTHS (p_day, 1), 'mm'),
								'yyyy_mm_dd'
							)
						|| ''''
						|| ',''yyyy_mm_dd'')'
					WHEN 'WEEK' THEN
							'to_date ('
						|| ''''
						|| TO_CHAR (TRUNC (p_day, 'DAY') + 7, 'yyyy_mm_dd')
						|| ''''
						|| ',''yyyy_mm_dd'')'
					WHEN 'YEAR' THEN
						'to_date (' || ''''
						|| TO_CHAR (
								TRUNC (ADD_MONTHS (p_day, 12), 'yyyy'),
								'yyyy_mm_dd'
							)
						|| ''''
						|| ',''yyyy_mm_dd'')'
				END;
		END IF;

		pkg_common_logging.write_log (
			'sh_partitioning_key_value Return: ' || v_ret,
			pkg_common_logging.set_debug
		);

		RETURN v_ret;
	EXCEPTION
		WHEN OTHERS THEN
			RAISE;
	END sh_partitioning_key_value;

	--
	--
	-------------------------------------------------------------------------------
	-- partition exists
	-------------------------------------------------------------------------------
	FUNCTION partition_exist (
		p_table_name		  VARCHAR2,
		p_partition_name	  VARCHAR2
	)
		--RETURN 		BOOLEAN
		RETURN INTEGER IS
		l_count	 NUMBER;
	BEGIN
		pkg_common_logging.set_client_info ('partition_exist');

		IF c_tab_partitions%ISOPEN THEN
			CLOSE c_tab_partitions;
		END IF;

		OPEN c_tab_partitions (my_tab_ident.owner,
									  my_tab_ident.table_name,
									  p_partition_name);

		FETCH c_tab_partitions
		INTO l_count;

		IF c_tab_partitions%ISOPEN THEN
			CLOSE c_tab_partitions;
		END IF;

		IF l_count > 0 THEN
			RETURN 1;
		END IF;

		RETURN 0;
	EXCEPTION
		WHEN OTHERS THEN
			IF c_tab_partitions%ISOPEN THEN
				CLOSE c_tab_partitions;
			END IF;

			RETURN 0;
			RAISE;
	END partition_exist;

	--
	--

	FUNCTION maxvalue_partition_exists (
		p_table_name	IN 	 user_tab_partitions.table_name%TYPE,
		p_part_name 		OUT user_tab_partitions.partition_name%TYPE
	)
		RETURN BOOLEAN IS
		v_ret 			BOOLEAN := FALSE;
		v_high_value	VARCHAR2 (1000);
	BEGIN
		SELECT	high_value, partition_name
		  INTO	v_high_value, p_part_name
		  FROM	all_tab_partitions
		 WHERE	table_name = my_tab_ident.table_name
					AND table_owner = my_tab_ident.owner
					AND partition_position =
							 (SELECT   MAX (partition_position)
								 FROM   all_tab_partitions
								WHERE   table_name = my_tab_ident.table_name
										  AND table_owner = my_tab_ident.owner);

		IF INSTR (v_high_value, 'MAXVALUE') > 0 THEN
			v_ret := TRUE;
		END IF;

		RETURN v_ret;
	EXCEPTION
		WHEN OTHERS THEN
			pkg_common_logging.write_log_error;
			RETURN v_ret;
	END maxvalue_partition_exists;


	PROCEDURE crpt_ (
		p_table_name					 user_tab_partitions.table_name%TYPE,
		p_partition_name				 user_tab_partitions.partition_name%TYPE,
		p_partitioning_key_value	 VARCHAR2,
		p_force							 BOOLEAN DEFAULT FALSE,
		p_tablespace_name 			 user_tab_partitions.tablespace_name%TYPE DEFAULT NULL
	) IS
		l_partition_bound_too_low	 EXCEPTION;
		PRAGMA EXCEPTION_INIT (l_partition_bound_too_low, -14074);

		l_partition_value_exist 	 EXCEPTION;
		PRAGMA EXCEPTION_INIT (l_partition_value_exist, -14080);

		p_max_part_name				 user_tab_partitions.partition_name%TYPE;
	BEGIN
		pkg_common_logging.set_client_info ('crpt_');



		IF partition_exist (p_table_name, p_partition_name) > 0 THEN
			pkg_common_logging.write_log (
					p_table_name
				|| ', partition '
				|| p_partition_name
				|| ' already exists',
				pkg_common_logging.set_info
			);
			RETURN;
		END IF;

		IF maxvalue_partition_exists (p_table_name, p_max_part_name) THEN
			pkg_common_logging.write_log (
				'found maxvalue partition -> split',
				pkg_common_logging.set_debug
			);

			IF check_global_index (p_table_name, p_force) THEN
				v_stmt :=
						'alter table '
					|| p_table_name
					|| ' split partition  '
					|| p_max_part_name
					|| ' at ('
					|| p_partitioning_key_value
					|| ') '
					|| ' into (partition '
					|| p_partition_name
					|| ', partition '
					|| p_max_part_name
					|| ') '
					|| ' update indexes';
			ELSE
				pkg_common_logging.write_log (
					'Global index exist and no force=true was set -> '
					|| 'not executing as operation may take a very long time'
				);
				RETURN;
			END IF;
		ELSE
			v_stmt :=
					'ALTER TABLE '
				|| p_table_name
				|| ' ADD PARTITION '
				|| p_partition_name
				|| ' VALUES LESS THAN ('
				|| p_partitioning_key_value
				|| ')'
				|| CASE
						WHEN p_tablespace_name IS NULL THEN ' '
						ELSE ' tablespace ' || p_tablespace_name
					END;
		END IF;

		pkg_common_logging.write_log (v_stmt, pkg_common_logging.set_debug);

		EXECUTE IMMEDIATE v_stmt;

		pkg_common_logging.write_log (
			'Partition ' || p_partition_name || ' added',
			pkg_common_logging.set_info
		);

        pkg_common_logging.write_log('Partition ' || p_partition_name || ' added');

	EXCEPTION
		WHEN l_partition_bound_too_low OR l_partition_value_exist THEN
			pkg_common_logging.write_log (
					p_table_name
				|| ', partition '
				|| p_partition_name
				|| ' partition_bound_too_low',
				pkg_common_logging.set_info
			);
		WHEN OTHERS THEN
			RAISE;
	END crpt_;

	--
	--

	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
	) IS
	BEGIN
		pkg_common_logging.init_log (
			'psm_pt.crpt',
			p_table_name,
				'crpt_ => '
			|| UPPER (p_date_type)
			|| ':'
			|| TO_CHAR (p_day, 'yyyymmdd')
			|| CASE
					WHEN p_tablespace_name IS NOT NULL THEN
						' tablespace ' || p_tablespace_name
				END
		);

		IF UPPER (p_date_type) NOT IN ('WEEK', 'MONTH', 'DAY', 'YEAR') THEN
			RAISE e_invalid_input;
		END IF;

		set_tab_ident (p_table_name);

		char_partitioned :=
			part_column_type (UPPER (p_table_name)) IN
				('CHAR', 'VARCHAR', 'VARCHAR2');



		crpt_ (
			UPPER (p_table_name),
			sh_partition_name (p_day, p_date_type),
			sh_partitioning_key_value (p_day, p_date_type),
			p_force,
			p_tablespace_name
		);

	--	pkg_common_logging.reset_log;
	EXCEPTION
		WHEN e_invalid_input THEN
			pkg_common_logging.write_log_error (
				p_table_name || '-> Partitioning Range invalid',
				-20040,
					'Partitioning Range was '
				|| p_date_type
				|| ' but must be WEEK,MONTH or DAY'
			);
			raise_application_error (-20040, 'Partitioning Range invalid');
		WHEN OTHERS THEN
			pkg_common_logging.write_log_error;
			RAISE;
	END crpt;


	PROCEDURE crpt IS
		CURSOR c_all_tabl IS
			SELECT	a.*
			  FROM	pt_config a, all_tables b
			 WHERE	a.owner = b.owner AND a.table_name = b.table_name;

		v_date_end		 DATE;
		v_date_run		 DATE := SYSDATE;

		l_force_split	 BOOLEAN DEFAULT FALSE;
	BEGIN
		pkg_common_logging.init_log (
			'psm_pt.crpt',
			'config from pt_config',
			'start'
		);


		FOR r_all_tabl IN c_all_tabl LOOP
			IF r_all_tabl.date_type NOT IN ('DAY', 'WEEK', 'MONTH') THEN
				RAISE e_invalid_input;
			END IF;

			v_date_end :=
				CASE
					WHEN r_all_tabl.date_type = 'DAY' THEN
						SYSDATE + r_all_tabl.intervall_adv
					WHEN r_all_tabl.date_type = 'MONTH' THEN
						ADD_MONTHS (SYSDATE, r_all_tabl.intervall_adv)
					WHEN r_all_tabl.date_type = 'WEEK' THEN
						SYSDATE + (7 * r_all_tabl.intervall_adv)
				END;

			v_date_run := SYSDATE;


			l_force_split := r_all_tabl.force_split IN ('Y');

			WHILE v_date_run <= v_date_end LOOP
				pkg_common_logging.set_action (
					r_all_tabl.owner || '.' || r_all_tabl.table_name
				);
				crpt (
					r_all_tabl.owner || '.' || r_all_tabl.table_name,
					r_all_tabl.date_type,
					v_date_run,
					l_force_split,
					r_all_tabl.tablespace_name
				);


				v_date_run :=
					CASE
						WHEN r_all_tabl.date_type = 'DAY' THEN
							v_date_run + 1
						WHEN r_all_tabl.date_type = 'MONTH' THEN
							ADD_MONTHS (v_date_run, 1)
						WHEN r_all_tabl.date_type = 'WEEK' THEN
							v_date_run + 7
					END;
			END LOOP;
		END LOOP;

		 pkg_common_logging.reset_log;
	EXCEPTION
		WHEN e_invalid_input THEN
			pkg_common_logging.write_log_error (
				' Partitioning Range invalid',
				-20040,
				'Partitioning Range must be WEEK,MONTH or DAY'
			);
			raise_application_error (-20040, 'Partitioning Range invalid');
		WHEN OTHERS THEN
			pkg_common_logging.write_log_error;
			RAISE;
	END crpt;


	PROCEDURE ident_partitions (
		p_table_name				  VARCHAR2,
		p_max_date					  DATE DEFAULT SYSDATE - 60,
		p_min_date					  DATE DEFAULT SYSDATE - 180,
		p_part_merge			 OUT t_part_merge,
		check_first_only	 IN	  BOOLEAN DEFAULT FALSE
	) IS
		v_high_value	VARCHAR2 (1000);
		v_part_date 	DATE;
		rec_id			NUMBER := 0;
	BEGIN
		char_partitioned :=
			part_column_type (p_table_name) IN ('CHAR', 'VARCHAR', 'VARCHAR2');
		pkg_common_logging.write_log (
			'check for mergeable partitions: ' || p_table_name,
			pkg_common_logging.set_info
		);
		pkg_common_logging.write_log (
				'p_min_date: '
			|| TO_CHAR (p_min_date, 'yyyymmdd')
			|| '; p_max_date:'
			|| TO_CHAR (p_max_date, 'yyyymmdd'),
			pkg_common_logging.set_info
		);


		FOR r_rec
			IN (SELECT		table_name, partition_name
					  FROM	all_tab_partitions
					 WHERE	table_name = my_tab_ident.table_name
								AND table_owner = my_tab_ident.owner
				 ORDER BY	partition_position) LOOP
			SELECT	high_value
			  INTO	v_high_value
			  FROM	all_tab_partitions
			 WHERE		 table_name = my_tab_ident.table_name
						AND table_owner = my_tab_ident.owner
						AND partition_name = r_rec.partition_name;

			IF INSTR (v_high_value, 'MAXVALUE') > 0 AND check_first_only THEN
				--
				--  in case we have a maxvalue we will abort
				--  check_first_only should not really be used when droptin partitions as we do not want to drop the maxvalue
				--
				pkg_common_logging.write_log (
					'found MAXVALUE partition -> end',
					pkg_common_logging.set_info
				);

				rec_id := rec_id + 1;
				p_part_merge (rec_id) := 'MAXVALUE';


				RETURN;
			END IF;


			IF char_partitioned THEN
				v_stmt :=
					'select to_date('
					|| CASE
							WHEN SUBSTR (v_high_value, 8, 2) IN
									  (p_lst_day_ident, '99') THEN
								SUBSTR (v_high_value, 1, 7) || '01'''
							ELSE
								v_high_value
						END
					|| ',''yyyymmdd'')  from dual';
			ELSE
				v_stmt := 'select ' || v_high_value || '  from dual';
			END IF;

			BEGIN
				EXECUTE IMMEDIATE v_stmt INTO v_part_date;

				pkg_common_logging.write_log (
						CASE WHEN char_partitioned THEN 'char' ELSE 'date' END
					|| ' partitioned, date:'
					|| TO_CHAR (v_part_date, 'yyyymmdd'),
					pkg_common_logging.set_debug
				);
				b_check_partition_success := TRUE;
			EXCEPTION
				WHEN OTHERS THEN
					v_part_date := NULL;
			END;

			IF v_part_date BETWEEN p_min_date + 1 AND p_max_date + 1 THEN
				rec_id := rec_id + 1;
				p_part_merge (rec_id) := r_rec.partition_name;
				pkg_common_logging.write_log (
					'partition ' || r_rec.partition_name || ' found in date range',
					pkg_common_logging.set_info
				);

				IF check_first_only THEN
					RETURN;
				END IF;
			ELSE
				pkg_common_logging.write_log (
					'partition ' || r_rec.partition_name || ' not in date range',
					pkg_common_logging.set_debug
				);
			END IF;
		END LOOP;
	EXCEPTION
		WHEN OTHERS THEN
			pkg_common_logging.write_log_error;
			RAISE;
	END;



	PROCEDURE merge_all_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
	) IS
	BEGIN
		pkg_common_logging.init_log (
			'psm_pt.merge_partition',
			p_table_name,
				'daterange: '
			|| TO_CHAR (p_min_date, 'yyyymmdd')
			|| ' to '
			|| TO_CHAR (p_max_date, 'yyyymmdd')
		);



		IF check_global_index (p_table_name, force_merge) THEN
			pkg_common_logging.write_log (
				p_table_name
				|| ' has not global indexes or force_merge was set -> OK to run',
				pkg_common_logging.set_debug
			);
			ident_partitions (
				p_table_name,
				p_max_date,
				p_min_date,
				my_part_merge
			);


			IF my_part_merge.COUNT > 1 THEN
				FOR i IN my_part_merge.FIRST .. (my_part_merge.LAST - 1) LOOP
					pkg_common_logging.set_action (my_part_merge (i));

					v_stmt :=
							'alter table '
						|| p_table_name
						|| ' merge partitions '
						|| my_part_merge (i)
						|| ', '
						|| my_part_merge (i + 1)
						|| ' into partition '
						|| my_part_merge (i + 1)
						|| ' UPDATE INDEXES';

					pkg_common_logging.write_log (
						v_stmt,
						pkg_common_logging.set_debug
					);

					EXECUTE IMMEDIATE v_stmt;

					pkg_common_logging.write_log (
						'merged into ' || my_part_merge (i + 1),
						pkg_common_logging.set_info
					);
				END LOOP;

				pkg_common_logging.set_action (p_table_name);

				IF p_max_date = LAST_DAY (p_max_date) THEN
					v_stmt :=
							'alter table '
						|| p_table_name
						|| ' rename partition '
						|| my_part_merge (my_part_merge.LAST)
						|| ' to '
						|| sh_partition_name (p_max_date, 'MONTH');

					pkg_common_logging.write_log (
						v_stmt,
						pkg_common_logging.set_debug
					);

					EXECUTE IMMEDIATE v_stmt;

					pkg_common_logging.write_log (
							'renamed '
						|| my_part_merge (my_part_merge.LAST)
						|| ' to '
						|| sh_partition_name (p_max_date, 'MONTH'),
						pkg_common_logging.set_info
					);
				ELSIF my_part_merge.COUNT = 1 THEN
					pkg_common_logging.write_log (
						my_part_merge (my_part_merge.LAST)
						|| ' is not the last partition for this month-> no rename',
						pkg_common_logging.set_info
					);
				ELSE
					pkg_common_logging.write_log (
						'no partitions found',
						pkg_common_logging.set_debug
					);
				END IF;
			ELSE
				pkg_common_logging.write_log (
						'found only one partition: '
					|| my_part_merge (my_part_merge.LAST)
					|| ' -> can not merge',
					pkg_common_logging.set_info
				);
			END IF;
		ELSE
			pkg_common_logging.write_log (
				p_table_name || ' has global indexes and force_merge was not set',
				pkg_common_logging.set_info
			);
		END IF;



		pkg_common_logging.reset_log;
	EXCEPTION
		WHEN OTHERS THEN
			pkg_common_logging.write_log_error;
			RAISE;
	END;



	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
	) IS
		v_cur_date	 DATE := p_min_date;
	BEGIN
		pkg_common_logging.init_log (
			'psm_pt.merge_partition',
			p_table_name,
				'daterange: '
			|| TO_CHAR (p_min_date, 'yyyymmdd')
			|| ' to '
			|| TO_CHAR (p_max_date, 'yyyymmdd')
		);

		set_tab_ident (p_table_name);

		LOOP
			pkg_common_logging.write_log (
				'call merge for ' || TO_CHAR (v_cur_date, 'yyyymmdd') || ' to '
				|| TO_CHAR (
						LEAST (LAST_DAY (v_cur_date), p_max_date),
						'yyyymmdd'
					),
				pkg_common_logging.set_debug
			);

			merge_all_partitions (
				p_table_name,
				v_cur_date,
				LEAST (LAST_DAY (v_cur_date), p_max_date),
				force_merge
			);

			v_cur_date := LEAST (LAST_DAY (v_cur_date), p_max_date) + 1;

			EXIT WHEN v_cur_date >= p_max_date;
		END LOOP;

		pkg_common_logging.reset_log;
	EXCEPTION
		WHEN OTHERS THEN
			pkg_common_logging.write_log_error;
			RAISE;
	END;

	PROCEDURE clean_partitions (
		p_table_name		VARCHAR2,
		p_part				VARCHAR2,
		force_drop		IN BOOLEAN DEFAULT FALSE
	) IS
	BEGIN
		pkg_common_logging.init_log (
			'psm_pt.clean_partitions',
			p_table_name,
			'clean_partition: ' || p_part
		);
		set_tab_ident (p_table_name);

		IF check_global_index (p_table_name, force_drop) THEN
			v_stmt :=
					'alter table '
				|| p_table_name
				|| ' truncate   partition '
				|| p_part;
			pkg_common_logging.write_log (v_stmt, pkg_common_logging.set_debug);

			EXECUTE IMMEDIATE v_stmt;
		ELSE
			pkg_common_logging.write_log (
				p_table_name || ' has global indexes and truncate was not done',
				pkg_common_logging.set_info
			);
		END IF;

		pkg_common_logging.reset_log;
	EXCEPTION
		WHEN OTHERS THEN
			pkg_common_logging.write_log_error;
			RAISE;
	END clean_partitions;


	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
	) IS
		v_high_value	VARCHAR2 (1000);
		v_id				NUMBER;
		v_part_date 	DATE;
	BEGIN
		pkg_common_logging.init_log (
			'psm_pt.clean_partitions',
			p_table_name,
			'clean_partitions'
		);
		set_tab_ident (p_table_name);

		char_partitioned :=
			part_column_type (p_table_name) IN ('CHAR', 'VARCHAR', 'VARCHAR2');

		IF check_global_index (p_table_name, force_drop) THEN
			ident_partitions (
				p_table_name,
				p_max_date,
				c_first_day,
				my_part_merge
			);


			-- identify partitions
			FOR i IN my_part_merge.FIRST .. my_part_merge.LAST LOOP
				v_stmt :=
						'alter table '
					|| p_table_name
					|| CASE WHEN drop_physicaly THEN ' drop' ELSE ' truncate' END
					|| '  partition '
					|| my_part_merge (i);

				pkg_common_logging.write_log (
					v_stmt,
					pkg_common_logging.set_debug
				);

				EXECUTE IMMEDIATE v_stmt;

				pkg_common_logging.write_log (
					my_part_merge (i)
					|| CASE WHEN drop_physicaly THEN ' drop' ELSE ' truncate' END,
					pkg_common_logging.set_info
				);
			END LOOP;
		ELSE
			pkg_common_logging.write_log (
				p_table_name || ' has global indexes and force_merge was not set',
				pkg_common_logging.set_info
			);
		END IF;

		pkg_common_logging.reset_log;
	EXCEPTION
		WHEN OTHERS THEN
			pkg_common_logging.write_log_error;
			RAISE;
	END clean_partitions;


	PROCEDURE truncate_sub_part (
		p_table_name		VARCHAR2,
		p_sub_part			VARCHAR2,
		force_drop		IN BOOLEAN DEFAULT FALSE
	) IS
		v_id	 NUMBER;
	BEGIN
		set_tab_ident (p_table_name);

		IF check_global_index (p_table_name, force_drop) THEN
			EXECUTE IMMEDIATE
					'alter table '
				|| p_table_name
				|| ' truncate subpartition '
				|| p_sub_part;
		END IF;
	EXCEPTION
		WHEN OTHERS THEN
			RAISE;
	END truncate_sub_part;



	PROCEDURE drop_part (
		p_table_name		VARCHAR2,
		p_part				VARCHAR2,
		force_drop		IN BOOLEAN DEFAULT FALSE
	) IS
	BEGIN
		set_tab_ident (p_table_name);

		IF check_global_index (p_table_name, force_drop) THEN
			EXECUTE IMMEDIATE
				'alter table ' || p_table_name || ' drop  partition  ' || p_part;
		END IF;
	END drop_part;

	FUNCTION show_version
		RETURN VARCHAR2 IS
	BEGIN
		RETURN 'psm_pt version is ' || c_version;
	END show_version;

	--


	FUNCTION build_return_stmt (
		my_part_merge	 IN t_part_merge,
		p_table_name	 IN VARCHAR2
	)
		RETURN VARCHAR2 IS
		v_return   VARCHAR2 (200);
	BEGIN
		IF my_part_merge.COUNT > 0
			AND INSTR (my_part_merge (my_part_merge.FIRST), 'MAXVALUE') = 0 THEN
			v_return :=
					'OK: partition '
				|| my_part_merge (my_part_merge.LAST)
				|| ' for '
				|| p_table_name;
		ELSIF my_part_merge.COUNT > 0
				AND INSTR (my_part_merge (my_part_merge.FIRST), 'MAXVALUE') > 0 THEN
			v_return := 'Warn: Maxvalue for ' || p_table_name;
		ELSIF b_check_partition_success THEN
			v_return := 'ERROR: Partition missing for ' || p_table_name;

			write_alert_log (-20001, v_stmt || ' for table ' || p_table_name);
		END IF;

		RETURN v_return;
	END build_return_stmt;

	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 IS
	BEGIN
		set_tab_ident (p_table_name);
		my_part_merge.delete;
		ident_partitions (
			p_table_name,
			p_min_required_date + 1000,
			p_min_required_date,
			my_part_merge,
			TRUE
		);


		PIPE ROW (build_return_stmt (my_part_merge, p_table_name));
	END f_check_partitions;


	FUNCTION f_check_partitions (
		p_min_required_date IN DATE DEFAULT SYSDATE + 7
	)
		RETURN xdb.xdb$string_list_t
		PIPELINED IS
	BEGIN
		pkg_common_logging.init_log (
			'psm_pt.f_check_partitions',
			USER,
			TO_CHAR (p_min_required_date, 'yyyymmdd')
		);

		FOR r_tables
			IN (SELECT	 DISTINCT a.owner || '.' || a.name tablename
					FROM	 all_part_key_columns a, all_tab_columns b
				  WHERE		  object_type = 'TABLE'
							 AND a.name = b.table_name
							 AND a.column_name = b.column_name
							 AND a.owner = b.owner
							 AND b.data_type IN
									  ('DATE',
										'TIMESTAMP(6)',
										'TIMESTAMP(6) WITH TIME ZONE')) LOOP
			pkg_common_logging.write_log (
				r_tables.tablename,
				pkg_common_logging.set_info
			);
			my_part_merge.delete;
			set_tab_ident (r_tables.tablename);
			b_check_partition_success := FALSE;

			ident_partitions (
				r_tables.tablename,
				p_min_required_date + 200,
				p_min_required_date,
				my_part_merge,
				TRUE
			);



			PIPE ROW (build_return_stmt (my_part_merge, r_tables.tablename));
		END LOOP;

		pkg_common_logging.reset_log;
	EXCEPTION
		WHEN OTHERS THEN
			pkg_common_logging.write_log_error;
			RAISE;
	END f_check_partitions;
END psm_pt;
/