CREATE OR REPLACE PACKAGE psm_pt
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
   --
   -- 12/2009 (IV)  Tablespace Info fuer Partitionen hinzugefuegt
   --
   --
   --
   -- 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'
   




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


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

  function show_version return varchar2;
--
--
END psm_pt;
/


CREATE OR REPLACE PACKAGE BODY psm_pt


AS
	c_version CONSTANT varchar2(32) := '02.03 / 20090808';
	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;


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


 CURSOR c_tab_partitions (p_table_name varchar2, p_partition_name varchar2
	)
	IS
		SELECT COUNT ( * )
		FROM user_tab_partitions
		WHERE table_name = UPPER (p_table_name)
				AND partition_name = UPPER (p_partition_name);
--
--
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 user_indexes WHERE
		  partitioned <> 'YES' AND table_name = p_table_name
		  AND index_type <> 'LOB';

  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 '
						  ||'USER_PART_KEY_COLUMNS a, '
						  ||'user_tab_columns b '
						  ||'where object_type = :b1 and '
						  ||'a.name = b.table_name and '
						  ||'a.column_name = b.column_name and '
						  ||'a.name = :b2' ;
			  pkg_common_logging.write_log(v_stmt ,pkg_common_logging.set_debug);

EXECUTE IMMEDIATE v_stmt INTO v_ret USING 'TABLE',p_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 (p_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 user_tab_partitions
	 WHERE table_name = p_table_name AND partition_position =
	  (SELECT MAX(partition_position) FROM user_tab_partitions
	  WHERE table_name = p_table_name);

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

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;


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




		  FOR r_rec IN (
				SELECT table_name, partition_name FROM user_tab_partitions
				WHERE table_name = p_table_name
				ORDER BY partition_position)
		  LOOP

		  SELECT high_value INTO v_high_value FROM user_tab_partitions WHERE
					 table_name = p_table_name AND partition_name = r_rec.partition_name;

		  IF	INSTR(v_high_value ,'MAXVALUE') > 0 THEN
				pkg_common_logging.write_log('found MAXVALUE partition -> end',pkg_common_logging.set_info);
				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;

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

				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);
				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'));


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_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');


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

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


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



END psm_pt;
/