TB Blog

Hi !

In this case I need to truncate a partition but I don’t know it’s name. This table has a range partition for every month , so I know HIGH_VALUE of particular partition.

We can find partition_name in ALL_TAB_PARTITIONS table but HIGH_VALUE column has CLOB data type. We need a script like below for to change it’s type to char.

CREATE OR REPLACE PROCEDURE   GUNES.TRUNCATE_PARTITION  
( p_table_owner IN varchar2 ,
  p_table_name IN VARCHAR2  ,
  p_high_value IN varchar2     ) IS


v_control varchar2 (200); 


BEGIN

SELECT  'ALTER TABLE '|| p_table_name || ' TRUNCATE PARTITION ' || (  

WITH DATA AS (

select table_name,

       partition_name,

          trim ( 

                     extractvalue (

                       dbms_xmlgen.getxmltype (

                       'select high_value from all_tab_partitions 
                                  where table_name='''

                                || table_name

                                || ''' and table_owner = '''

                                || table_owner

                                || ''' and partition_name = '''

                                || partition_name

                                || ''''),

                             '//text()') )

          high_value_in_date_format

  FROM all_tab_partitions

WHERE table_name = p_table_name AND table_owner = p_table_owner
)

SELECT partition_name FROM DATA  

WHERE  high_value_in_date_format = p_high_value )  AS DYN_TASK 

INTO v_control  

FROM dual ;


EXECUTE IMMEDIATE( v_control  ); 


EXCEPTION WHEN OTHERS THEN NULL;


END TRUNCATE_PARTITION; 

To run this procedure ;

call  TRUNCATE_PARTITION('GUNES' ,'GUNES_PARTITION'  ,'20190399' );