OraFAQ Forum: SQL & PL/SQL » truncate via different schema owner

Home » SQL & PL/SQL » SQL & PL/SQL » truncate via different schema owner (10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

truncate via different schema owner [message #425885]

Mon, 12 October 2009 22:10

beetel
Messages: 96
Registered: April 2007

Member

Can another schema truncate a table which it does not own? If so, what privilege should I give??? thanks in advance.

Report message to a moderator

Re: truncate via different schema owner [message #425893 is a reply to message #425885]

Mon, 12 October 2009 23:40

ayush_anand
Messages: 417
Registered: November 2008

Senior Member

http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/statements_10006.htm#sthref7322

Quote:

Prerequisites

To truncate a table or cluster, the table or cluster must be in your schema or you must have DROP ANY TABLE system privilege.

You cannot grant truncate priv
Instead create a procedure to truncate and grant the priv to execute procedure to the user you what to grant .

use execute immediate to truncate table like

execute immediate 'truncate table ' || P_table_name ;
OR hard code the table to drop only desired table - recommended.

execute immediate 'truncate table my_table ' ;

in your procedure. where p_table_name is parameter.
[Copied from a forum after googling]

[Updated on: Mon, 12 October 2009 23:42]

Report message to a moderator