[分享]删除系统自动备份表存储过程**

李凯

create or replace function dropbacktable(VINPUT varchar2) return integer as
  Result    integer;
  mytable   varchar2(32);
  cursorsql varchar2(400);
  dropsql   varchar2(400);
  vcount    integer;
  type vcrusor is ref cursor;
  jbktable vcrusor;
begin
  cursorsql := 'select t.table_name from all_tables t where t.owner = ''' ||
               VINPUT ||
               ''' and substr(t.table_name, length(t.table_name), 1) in (''0'',''1'', ''2'', ''3'', ''4'', ''5'', ''6'', ''7'', ''8'', ''9'')';
  open jbktable for cursorsql;
  loop
    fetch jbktable
      into mytable;
    exit when jbktable%NOTFOUND;
    dropsql := 'drop table ' || mytable;
    vcount  := vcount + 1;
    execute immediate (dropsql);
  end loop;
  close jbktable;
  Result := vcount;
  return(Result);
end dropbacktable;


VINPUT传入需要清理的用户实例的名字,传入大写.