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

Question

我们的数据库中的好几张表，每天一个分区，但我们需要按日期清除数据，现在用“alter table 表名 truncate partition 分区号 update indexes”语句手动清除，很烦，要去查所需日期的分区号，写进去，再逐条执行。有没有大神能帮助编写只需表名称和删除日期，就直接按分区号清除数据的语句，非常感谢！！！
简化来说就是，只输入要清除表名、起止日期，即可执行“alter table 表名 truncate partition 分区号 update indexes”语句，就行了，不需要每张表去找分区号，非常感谢大神帮忙！！

Answer 1

把你做的封装成procedure

Answer 2

可以试下 定义一个job，定时执行删除的procedure

Answer 3

但是怎么才能才到需要删除日期的分区号呢，从view里查很慢

Answer 4

由于楼主没给出分区名称的命名规范。这里假设为: P+yyyymmdd，如：P20171225
新建一个存储过程，代码请自行测试。create or replace procedure p_partition_truncate
(
   table_name   in   varchar2,   --表名称
   dtime        in   varchar2    --起始日期
)
is
   v_sql varchar2(1000);
begin
   v_sql := 'alter table '||table_name||' truncate partition '||'P'||dtime||' update indexes';
exception
   when others then
   raise;   
end;

Answer 5

存储过程修改下：create or replace procedure p_partition_truncate
(
   table_name   in   varchar2,   --表名称
   dtime        in   varchar2    --起始日期
)
is
   v_sql varchar2(1000);
begin
   v_sql := 'alter table '||table_name||' truncate partition '||'P'||dtime||' update indexes';
   execute immediate v_sql;
exception
   when others then
   raise;   
end;

Answer 6

这个任务做比较合适，这个存储过程，shell调用执行

Answer 7

dba_tab_partitions

Answer 8

CREATE OR REPLACE PROCEDURE P_PARTITION_TRUNCATE
(
  TABLE_NAME    IN VARCHAR2, --表名称
  DTIME         IN VARCHAR2, --起始日期
  TIME_COL_NAME IN VARCHAR2 --日期列名称
) IS
  V_SQL            VARCHAR2(1000);
  V_ROWID          ROWID;
  V_SUBOBJECT_NAME USER_OBJECTS.SUBOBJECT_NAME%TYPE;
BEGIN
  V_SQL := 'select max(rowid) from ' || TABLE_NAME || ' where ' ||
           TIME_COL_NAME || '=''' || DTIME || '''';
  EXECUTE IMMEDIATE V_SQL
    INTO V_ROWID;
  SELECT MAX(SUBOBJECT_NAME)
  INTO   V_SUBOBJECT_NAME
  FROM   USER_OBJECTS T
  WHERE  T.DATA_OBJECT_ID IN (SELECT DBMS_ROWID.ROWID_OBJECT(V_ROWID)
                              FROM   DUAL);
  V_SQL := 'alter table ' || TABLE_NAME || ' truncate partition ' ||
           V_SUBOBJECT_NAME || ' update indexes';
  --DBMS_OUTPUT.PUT_LINE(V_SQL);
  EXECUTE IMMEDIATE V_SQL;
EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END;
不知道分区名的情况下,可以通过rowid来确认所属分区的, 代码用的日期是字符串型的, 如果是其他类型,稍微修改一下即可