把truncate表的操作封闭到存储过程给其他用户调用
truncate非分区表
CREATE OR REPLACE Procedure SYS.truncate_nopartition_tab
(p_owner in varchar2, --owner
p_tab in varchar2 --table name
)
As
Sqlddl Varchar2(1000);
Begin
Sqlddl := 'truncate table '||p_owner||'.'||p_tab||'';
Dbms_Output.Put_Line(Sqlddl);
Execute Immediate Sqlddl;
End;
/truncate分区表的某个分区
CREATE OR REPLACE Procedure SYS.truncate_partition_tab
(
p_owner in varchar2, --owner
p_tab in varchar2, --table name
p_part in varchar2 --partition
)
As
Sqlddl Varchar2(1000);
Begin
Sqlddl := 'alter table '||p_owner||'.'||p_tab||' truncate partition ('||p_part||') update indexes';
Dbms_Output.Put_Line(Sqlddl);
Execute Immediate Sqlddl;
End;
/测试
-- 建非分区表
create table report.test0601 (id int,name varchar2(100));
insert into report.test0601 values(2,'b');
insert into report.test0601 values(1,'a');
commit;
-- 建分区表
create table report.test0601part (id int,name varchar2(100),createtime date)
COMPRESS FOR OLTP TABLESPACE report
PARTITION BY RANGE (createtime) INTERVAL (NUMTODSINTERVAL(1, 'day'))
(partition p202105 values less than(to_date('2021-06-01', 'yyyy-mm-dd')))
;
insert into report.test0601part values(2,'b',sysdate);
insert into report.test0601part values(1,'a', to_date ( '2021-06-02 18:31:34' , 'YYYY-MM-DD HH24:MI:SS' ) );
insert into report.test0601part values(3,'c', sysdate+1 );
insert into report.test0601part values(4,'d', sysdate+2 );
insert into report.test0601part values(5,'e', sysdate+3 );
insert into report.test0601part values(6,'f', sysdate+4 );
commit;
select * from report.test0601 ;
select * from report.test0601part;
select * from report.test0601part partition (SYS_P1115);exec truncate_nopartition_tab('report','TEST0601');
exec truncate_partition_tab('report','TEST0601PART','SYS_P1115');
可以创建同义词
CREATE PUBLIC SYNONYM truncate_nopartition_tab
FOR SYS.truncate_nopartition_tab;
CREATE PUBLIC SYNONYM truncate_partition_tab
FOR SYS.truncate_partition_tab;
GRANT EXECUTE ON "SYS"."TRUNCATE_PART_TAB_FOR_REPORT_R" TO userA;
GRANT EXECUTE ON "SYS"."truncate_nopartition_tab" TO userA;如果是sqlplus执行，格式如下:
begin
truncate_partition_tab('report','TEST0601PART','SYS_P1115');
end;
/
begin
truncate_nopartition_tab('report','TEST0601');
end;
/
删除分区表的分区调用以下存储过程：
truncate_partition_tab('owner','table_name','partition_name')
调用例子：
begin
truncate_partition_tab('report','TEST0601PART','SYS_P1115');
end;
/删除非分区表调用以下存储过程：
truncate_partition_tab('owner','table_name')
调用例子：
begin
truncate_nopartition_tab('report','TEST0601');
end;
/ 
