Oracle删除分区truncate,利用Truncate清空Oracle各个分区的资料

/*

In applications, users need truncate privileges on third party tables. The owner of the table can truncate his/her own tables. A user with DBA role granted can truncate a third party table. Any user with "drop any table" system privilege can truncate a third party table.

It is not recommended in Production environments to grant powerful roles like DBA role or privileges like "drop any table" privilege to accomplish this purpose.

This procedure need to be created in each schema. The schema owner need to grant execute permission to the user, and the delete permission on specified tables.

Then the user can execute the procedure to truncate tables User A owns the table temp_jp.

conn uwclass/uwclass

CREATE TABLE t1 AS

SELECT * FROM all_tables;

CREATE OR REPLACE PROCEDURE trunc_tab(p_tabname IN VARCHAR2) AUTHID DEFINER AS

/**********************************

AUTHOR JP Vijaykumar

ORACLE DBA

**********************************/

v_num    NUMBER(10):=0;

v_owner  VARCHAR2(30);

v_user   VARCHAR2(30);

sql_stmt VARCHAR2(2000);

BEGIN

SELECT username

INTO v_owner

FROM user_users;

SELECT sys_context('USERENV','SESSION_USER')

INTO v_user

FROM dual;

sql_stmt:='TRUNCATE TABLE ' || v_owner || '.' || p_tabname;

IF (v_owner = v_user) THEN

execute immediate sql_stmt;

ELSE

SELECT COUNT(*)

INTO v_num

FROM all_tab_privs

WHERE table_name = UPPER(p_tabname)

AND table_schema = v_owner

AND grantee = v_user

AND privilege in 'DELETE';

IF (v_num > 0) THEN

execute immediate sql_stmt;

ELSE

RAISE_APPLICATION_ERROR(-20001,'Insufficient privileges.');

END IF;

END IF;

EXCEPTION

WHEN OTHERS THEN

RAISE_APPLICATION_ERROR(-20001,'Insufficient privileges');

END trunc_tab;

/

GRANT execute ON t1 TO abc;

conn abc/abc

exec uwclass.trunc_tab('T1');

conn uwclass/uwclass

GRANT delete ON t1 TO abc;conn abc/abc

exec uwclass.trunc_tab('T1');

conn uwclass/uwclassSELECT * FROM t1;