oracle数据库自动按分区号清除数据的脚本

大致思路是由DBA_TAB_PARTITIONS查询TABLE_NAME和HIGH_VALUE，来确定PARTION_NAME，通过SQL生成alter table语句。

由于DBA_TAB_PARTIONS的HIGH_VALUE字段是LONG，这里创建一个自定义包long_help，将LONG转换为VARCHAR2类型。

create or replace package long_help
authid current_user
as
function substr_of
( p_query in varchar2,
p_from  in number,
p_for   in number,
p_name1 in varchar2 default NULL,
p_bind1 in varchar2 default NULL,
p_name2 in varchar2 default NULL,
p_bind2 in varchar2 default NULL,
p_name3 in varchar2 default NULL,
p_bind3 in varchar2 default NULL,
p_name4 in varchar2 default NULL,
p_bind4 in varchar2 default NULL )
return varchar2;
end;
/
create or replace package body long_help
as
    g_cursor number := dbms_sql.open_cursor;
    g_query  varchar2(32765);
procedure bind_variable( p_name in varchar2, p_value in varchar2 )
is
begin
    if ( p_name is not null )
    then
        dbms_sql.bind_variable( g_cursor, p_name, p_value );
    end if;
END;
FUNCTION substr_of
( p_query in varchar2,
  p_from  in number,
  p_for   in number,
  p_name1 in varchar2 default NULL,
  p_bind1 in varchar2 default NULL,
  p_name2 in varchar2 default NULL,
  p_bind2 in varchar2 default NULL,
  p_name3 in varchar2 default NULL,
  p_bind3 in varchar2 default NULL,
  p_name4 in varchar2 default NULL,
  p_bind4 in varchar2 default NULL )
return varchar2
as
    l_buffer       varchar2(4000);
    l_buffer_len   number;
begin
    if ( nvl(p_from,0) <= 0 )
    then
        raise_application_error
        (-20002, 'From must be >= 1 (positive numbers)' );
    end if;
    if ( nvl(p_for,0) not between 1 and 4000 )
    then
        raise_application_error
        (-20003, 'For must be between 1 and 4000' );
    end if;
    if ( p_query <> g_query or g_query is NULL )
    then
        if ( upper(trim(nvl(p_query,'x'))) not like 'SELECT%')
        then
            raise_application_error
            (-20001, 'This must be a select only' );
        end if;
        dbms_sql.parse( g_cursor, p_query, dbms_sql.native );
        g_query := p_query;
    end if;
    bind_variable( p_name1, p_bind1 );
    bind_variable( p_name2, p_bind2 );
    bind_variable( p_name3, p_bind3 );
    bind_variable( p_name4, p_bind4 );
    dbms_sql.define_column_long(g_cursor, 1);
    if (dbms_sql.execute_and_fetch(g_cursor)>0)
    then
        dbms_sql.column_value_long
        (g_cursor, 1, p_for, p_from-1,
         l_buffer, l_buffer_len );
    end if;
    return l_buffer;
end substr_of;
END;
/

创建一个分区表PART_TABLE，插入三条记录分别在三个分区中。

CREATE TABLE PART_TABLE
(
   TABLE_ID NUMBER(8),
   SUB_DATE DATE
)
PARTITION BY RANGE(SUB_DATE)
INTERVAL(NUMTODSINTERVAL(1，'DAY'))
(
  PARTITION P1 VALUES LESS THAN(TO_DATE('2017-12-25','YYYY-MM-DD'))
);
INSERT INTO PART_TABLE VALUES(1,DATE '2017-12-24');
INSERT INTO PART_TABLE VALUES(1,DATE '2017-12-25');
INSERT INTO PART_TABLE VALUES(1,DATE '2017-12-26');
COMMIT;

通过SQL生成alter table语句，截断第一个分区。

这里需要注意查询条件是HIGH_VALUE，条件为2017-12-25时，截断的是最大值为2017-12-25的分区。

SELECT 'alter table PART_TABLE truncate partition ' || PARTITION_NAME ||
       ' update INDEXES;'
  FROM (SELECT TABLE_OWNER,
                TABLE_NAME,
                PARTITION_NAME,
                LONG_HELP.SUBSTR_OF('SELECT HIGH_VALUE
FROM   DBA_TAB_PARTITIONS WHERE TABLE_OWNER=:TABLE_OWNER 
AND TABLE_NAME=:TABLE_NAME
AND PARTITION_NAME=:PARTITION_NAME', 1, 4000, 'TABLE_OWNER', TABLE_OWNER, 'TABLE_NAME', TABLE_NAME, 'PARTITION_NAME', PARTITION_NAME) HIGH_VALUE
           FROM DBA_TAB_PARTITIONS)
 WHERE TABLE_NAME = 'PART_TABLE'
   AND HIGH_VALUE LIKE '%2017-12-25%';

删除分区及分区数据：
alter table 表名 drop partition 分区名;
只删除分区数据，分区还在：
delete from 表名 partition(分区名);