четверг, 16 января 2014 г.

truncate table partition by period start_date - end_date

EXPLAIN PLAN
SET STATEMENT_ID = 'partition.testtab.1' FOR SELECT * FROM TRANSACTION
WHERE TRAN_DATE BETWEEN to_date('01.05.2013', 'dd/mm/yyyy')
AND to_date( '30.05.2013','dd/mm/yyyy');
WITH
  pre_conv AS
  (
    SELECT
      CAST ( to_clob(COLUMN_VALUE) AS VARCHAR2(3999)) AS xml_plan
    FROM
      TABLE( XmlSequence( EXTRACT( DBMS_XPLAN.Build_Plan_Xml( 'PLAN_TABLE',
      'partition.testtab.1' ), '/plan/operation/partition' ) ) ) t
    WHERE
      ROWNUM=1
  )
  ,
  part AS
  (
    SELECT
      regexp_replace( xml_plan, '(.*start=")(\d+)(".*)', '\2') start_pos,
      regexp_replace( xml_plan, '(.*stop=")(\d+)(".*)','\2') stop_pos
    FROM
      pre_conv
  )
SELECT
   'ALTER TABLE '
  || t.table_name
  ||' TRUNCATE PARTITION '
  || t.partition_name
  || ' UPDATE INDEXES;'
  /*3.0*/
FROM
  user_tab_partitions t,
  part t2
WHERE
  t.table_name = 'TRANSACTION'
AND partition_position BETWEEN t2.start_pos AND t2.stop_pos;

another solution
create or replace PROCEDURE                 TRUNCATE_PARTITION
(IN_TABLE_OWNER IN VARCHAR2
,  IN_TABLE_NAME IN VARCHAR2
,  IN_DATE IN DATE)
IS
v_partition_name varchar2(50);

BEGIN
    --    v_partition_name:='';
     BEGIN
                SELECT partition_name
                INTO v_partition_name
                FROM          
                    ( 
                          SELECT table_name
                                      , partition_name
                                      , to_date(trim('''' from
                                             regexp_substr(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()'),
                                             '''.*?''')),
                                   'syyyy-mm-dd hh24:mi:ss') high_value_in_date_format
                    FROM all_tab_partitions
                    WHERE 1=1
                        AND table_name = IN_TABLE_NAME
                        AND table_owner = IN_TABLE_OWNER
                        ) SQ
                 WHERE high_value_in_date_format=LAST_DAY(IN_DATE)+1;
                 EXCEPTION WHEN NO_DATA_FOUND THEN  v_partition_name:='';
       END;      

         
         IF LENGTH(v_partition_name)>0 THEN
            EXECUTE IMMEDIATE 'ALTER TABLE '|| IN_TABLE_OWNER ||'.'|| IN_TABLE_NAME ||' TRUNCATE PARTITION ' || V_PARTITION_NAME || ' UPDATE INDEXES'; /*2.0*/
         END IF;
         
END TRUNCATE_PARTITION;

пятница, 10 января 2014 г.