在普通用户A下(已赋予了删除用户A表的权限),希望删除用户A下某张表的分区(分区是list by t_date的),我希望删除所有t_date小于7天前的分区。目前我是这样做的:v_date := to_char(to_date(sysdate, 'yyyymmdd') - 7,
'yyyymmdd');
v_sql := 'select distinct data_date from tab1 where t_date <= ' || v_date;
open cur for v_sql;
loop
exit when cur%notfound;
fetch cur
into v_date;
v_sql := 'alter table tab1 drop partition part_users' || v_date;
execute immediate v_sql;
commit;
/*end if;*/
end loop;
由于表数据量很大,所以感觉distinct那里很慢很慢,求优化语法,谢谢:)
'yyyymmdd');
v_sql := 'select distinct data_date from tab1 where t_date <= ' || v_date;
open cur for v_sql;
loop
exit when cur%notfound;
fetch cur
into v_date;
v_sql := 'alter table tab1 drop partition part_users' || v_date;
execute immediate v_sql;
commit;
/*end if;*/
end loop;
由于表数据量很大,所以感觉distinct那里很慢很慢,求优化语法,谢谢:)
SELECT * FROM user_tab_partitions t WHERE t.table_name = upper('你的表名');
另外如果要作drop的话,最好先lock table in exclusive mode wait...,否则假如该表正在执行dml,那么你的程序将会报错。