Oracle自动生成按分区号清除数据的脚本

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

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

没有DB权限可以换为USER_TAB_PARTITIONS表

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82

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，插入三条记录分别在三个分区中。

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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的分区。

1 2 3 4 5 6 7 8 9 10 11 12

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