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;