oracle清空一个分区,利用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;