删除大量过期分区

最新推荐文章于 2021-04-03 02:47:21 发布

cml2016

最新推荐文章于 2021-04-03 02:47:21 发布

阅读量147

点赞数

点击(此处)折叠或打开

--以天分区时
--过程
create or replace
PROCEDURE MONITORMSG_DROPPART(
DaysAmount NUMBER --删除间隔日期
)
AS
v_SqlExec VARCHAR2(2000); --DDL语句变量
--先查找DaysAmount之前的表分区
cursor cursor_part is
select partition_name from user_tab_partitions
WHERE table_name= \'MONITORMSG\' AND to_date(SUBSTR(partition_name,12),\'YYYYMMDD\')< sysdate- daysamount order by partition_name; -----substr这个地方视具体情况而定
cursor_oldpart cursor_part%rowType;
begin
open cursor_part;
loop
fetch cursor_part into cursor_oldpart;
exit when cursor_part%notfound;
v_sqlexec:=\'ALTER TABLE MONITORMSG DROP PARTITION \'||cursor_oldpart.partition_name;---删除多少天前的分区
DBMS_Utility.Exec_DDL_Statement(v_SqlExec);
end loop;
close cursor_part;
END MONITORMSG_DROPPART;
/*
declare
DaysAmount NUMBER;
begin
MONITORMSG_DROPPART(340);
end;
/
----用这种游标的方法也可以实现
declare
v_sql varchar2(1024);
cursor c1(DaysAmount in number) is select PARTITION_NAME from USER_TAB_PARTITIONS where TABLE_NAME =\'MONITORMSG\'and
to_date(substr(partition_name,12),\'yyyymmdd\')
v_cur c1%rowtype;
begin
open c1(&n); ----也就是上面的精简版
loop
fetch c1 into v_cur;
exit when c1%notfound;
v_sql := \'alter table MONITORMSG drop partition \' ||v_cur.partition_name;
execute immediate v_sql;
end loop;
close c1;
end;
--删除多少月前
--过程
create or replace
PROCEDURE MONITORMSG_DROPPART(
monsAmount NUMBER --删除间隔日期
)
AS
v_SqlExec VARCHAR2(2000); --DDL语句变量
--先查找monsAmount之前的表分区
cursor c1 is select PARTITION_NAME from USER_TAB_PARTITIONS where TABLE_NAME =\'MONITORMSG\'and
months_between(trunc(sysdate,\'mm\'),trunc(to_date(substr(partition_name,12),\'yyyymmdd\'),\'mm\'))>=monsAmount order by partition_name ;
cursor_oldpart c1%rowType;
begin
open c1;
loop
fetch c1 into cursor_oldpart;
exit when c1%notfound;
v_sqlexec:=\'ALTER TABLE MONITORMSG DROP PARTITION \'||cursor_oldpart.partition_name;---删除多少月前的分区
DBMS_Utility.Exec_DDL_Statement(v_SqlExec);
end loop;
close c1;
END MONITORMSG_DROPPART;
declare
DaysAmount NUMBER;
begin
MONITORMSG_DROPPART(40);
end;
/
--游标方法
declare
v_sql varchar2(1024);
cursor c1 is select PARTITION_NAME from USER_TAB_PARTITIONS where TABLE_NAME =\'MONITORMSG\'and
months_between(trunc(sysdate,\'mm\'),trunc(to_date(substr(partition_name,12),\'yyyymmdd\'),\'mm\'))>=40 order by partition_name ;
v_cur c1%rowtype;
begin
open c1; ----也就是上面的精简版,月数也可以改成自己填，只需要稍微变更一下代码成天数那种形式即可
loop
fetch c1 into v_cur;
exit when c1%notfound;
v_sql := \'alter table MONITORMSG drop partition \' ||v_cur.partition_name;
execute immediate v_sql;
end loop;
close c1;
end;
*/