Truncate table is one of the easiest command. You can truncate table in your schema. If you have to truncate tables in another schema; you need to have 'DROP ANY TABLE' privileges.
Why you need privilege to truncate table in another schema?
It depends on how we design a system. There are architecture where we maintain different components of organization in different schema but use one single schema to access it may be in reports.
Here, DBAs and organization security dont prefer to grant 'DROP ANY TABLE' privileges to any other schema.Tom Kyte have suggested to use stored procedure to acheive the result. I am not going any different way either.
Design:
1) Procedure 'truncate_tabname' to truncate table in every schema.
2) Grant exeute on the procedure 'truncate_tabname' to main schema.
3) Create wrapper procedure on main schema that calls the procedure 'xx'
STEP1: Create procedure on each schema.Below is script for HR, Repeat for FIN, MANUFACT and SHIP.
create or replace
PROCEDURE hr.truncate_tabname(p_owner IN VARCHAR2
,p_tabname IN VARCHAR2) as
-- Description : Stored procedure to truncate tables.
-- This procedure is called by a wrapper procedure truncate_tab in ADMIN schema.
-- This is a workaround for not granting 'DROP ANY TABLE' privilege to ADMIN and truncate tables in other schemas.
-- Usage : exec truncate_tabname(,);
v_object varchar2(100);
begin
v_object:=p_owner||'.'||p_tabname;
EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || v_object;
DBMS_OUTPUT.PUT_LINE(v_object||' have been truncated.');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(v_object||' - Table may not exist !!!');
END truncate_tabname;
===========================
STEP2: grant execute on hr.truncate_tabname to ADMIN;
===========================
STEP3: Wrapper Procedue
create or replace
PROCEDURE ADMIN.truncate_tab(p_owner IN VARCHAR2
,p_tabname IN VARCHAR2
,p_confirm IN BOOLEAN) as
-- Description : This procedure inturns calls truncate_tabname procedure in each schema.
-- This is a workaround for not granting 'DROP ANY TABLE' privilege to ADMIN and truncate tables in other schemas.
-- Usage : exec truncate_tab(,,);
v_owner varchar2(30);
v_tabname varchar2(100);
v_flag boolean;
v_error varchar2(200);
begin
v_owner:=upper(p_owner);
v_tabname:=upper(p_tabname);
v_flag:=p_confirm;
IF v_flag THEN
CASE v_owner
WHEN 'HR' THEN
hr.truncate_tabname(v_owner,v_tabname);
WHEN 'FIN' THEN
fin.truncate_tabname(v_owner,v_tabname);
WHEN 'MANUFACT' THEN
manufact.truncate_tabname(v_owner,v_tabname);
WHEN 'SHIP' THEN
ship.truncate_tabname(v_owner,v_tabname);
ELSE
DBMS_OUTPUT.PUT_LINE('Sorry, You dont have privilege to truncate tables on '||v_owner);
END CASE;
ELSE DBMS_OUTPUT.PUT_LINE('Please confirm the truncate with FLAG to TRUE and Try again');
END IF;
EXCEPTION
WHEN OTHERS THEN
v_error := substr(sqlerrm, 1,200);
DBMS_OUTPUT.PUT_LINE('Error: '||v_error);
END truncate_tab;
===================
Why you need privilege to truncate table in another schema?
It depends on how we design a system. There are architecture where we maintain different components of organization in different schema but use one single schema to access it may be in reports.
Here, DBAs and organization security dont prefer to grant 'DROP ANY TABLE' privileges to any other schema.Tom Kyte have suggested to use stored procedure to acheive the result. I am not going any different way either.
Design:
1) Procedure 'truncate_tabname' to truncate table in every schema.
2) Grant exeute on the procedure 'truncate_tabname' to main schema.
3) Create wrapper procedure on main schema that calls the procedure 'xx'
STEP1: Create procedure on each schema.Below is script for HR, Repeat for FIN, MANUFACT and SHIP.
create or replace
PROCEDURE hr.truncate_tabname(p_owner IN VARCHAR2
,p_tabname IN VARCHAR2) as
-- Description : Stored procedure to truncate tables.
-- This procedure is called by a wrapper procedure truncate_tab in ADMIN schema.
-- This is a workaround for not granting 'DROP ANY TABLE' privilege to ADMIN and truncate tables in other schemas.
-- Usage : exec truncate_tabname(
v_object varchar2(100);
begin
v_object:=p_owner||'.'||p_tabname;
EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || v_object;
DBMS_OUTPUT.PUT_LINE(v_object||' have been truncated.');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(v_object||' - Table may not exist !!!');
END truncate_tabname;
===========================
STEP2: grant execute on hr.truncate_tabname to ADMIN;
===========================
STEP3: Wrapper Procedue
create or replace
PROCEDURE ADMIN.truncate_tab(p_owner IN VARCHAR2
,p_tabname IN VARCHAR2
,p_confirm IN BOOLEAN) as
-- Description : This procedure inturns calls truncate_tabname procedure in each schema.
-- This is a workaround for not granting 'DROP ANY TABLE' privilege to ADMIN and truncate tables in other schemas.
-- Usage : exec truncate_tab(
v_owner varchar2(30);
v_tabname varchar2(100);
v_flag boolean;
v_error varchar2(200);
begin
v_owner:=upper(p_owner);
v_tabname:=upper(p_tabname);
v_flag:=p_confirm;
IF v_flag THEN
CASE v_owner
WHEN 'HR' THEN
hr.truncate_tabname(v_owner,v_tabname);
WHEN 'FIN' THEN
fin.truncate_tabname(v_owner,v_tabname);
WHEN 'MANUFACT' THEN
manufact.truncate_tabname(v_owner,v_tabname);
WHEN 'SHIP' THEN
ship.truncate_tabname(v_owner,v_tabname);
ELSE
DBMS_OUTPUT.PUT_LINE('Sorry, You dont have privilege to truncate tables on '||v_owner);
END CASE;
ELSE DBMS_OUTPUT.PUT_LINE('Please confirm the truncate with FLAG to TRUE and Try again');
END IF;
EXCEPTION
WHEN OTHERS THEN
v_error := substr(sqlerrm, 1,200);
DBMS_OUTPUT.PUT_LINE('Error: '||v_error);
END truncate_tab;
===================
Thanks to my friend Alson for code review.
