Home » SQL & PL/SQL » SQL & PL/SQL » truncate via different schema owner (10g)
truncate via different schema owner [message #425885] Mon, 12 October 2009 22:10 Go to next message
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.
Re: truncate via different schema owner [message #425893 is a reply to message #425885] Mon, 12 October 2009 23:40 Go to previous messageGo to next message
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

Re: truncate via different schema owner [message #425897 is a reply to message #425893] Mon, 12 October 2009 23:53 Go to previous messageGo to next message
beetel
Messages: 96
Registered: April 2007
Member
Thank u, that was helpful!
Re: truncate via different schema owner [message #425899 is a reply to message #425885] Mon, 12 October 2009 23:55 Go to previous message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>Can another schema truncate a table which it does not own? If so, what privilege should I give???
EXECUTE with a stored procedure
Previous Topic: This returns "table does not exist" but reads correctly in my DBMS_OUTPUT. HELP! thx...
Next Topic: Job scheduling
Goto Forum:
  


Current Time: Sat Dec 03 16:06:04 CST 2016

Total time taken to generate the page: 0.05329 seconds