oracle中truncate其他用户的表必须要有drop any table权限,如果没有此权限,可以采用下面的方法
1.创建存储过程
create or replace procedure truncate_tbl
(table_name in varchar2)
authid definer
as
cursor_id integer;
begin
cursor_id := dbms_sql.open_cursor;
dbms_sql.parse(cursor_id, 'TRUNCATE TABLE ' || table_name, dbms_sql.v7);
dbms_sql.close_cursor(cursor_id);
exception
when others then
dbms_sql.close_cursor(cursor_id);
raise;
end truncate_tbl;
(table_name in varchar2)
authid definer
as
cursor_id integer;
begin
cursor_id := dbms_sql.open_cursor;
dbms_sql.parse(cursor_id, 'TRUNCATE TABLE ' || table_name, dbms_sql.v7);
dbms_sql.close_cursor(cursor_id);
exception
when others then
dbms_sql.close_cursor(cursor_id);
raise;
end truncate_tbl;
2.授予存储过程的执行权限
grant execute on truncate_tbl to XXX;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/35510/viewspace-680182/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/35510/viewspace-680182/