truncate privs

[mooks]

Hi

Is it possible to assign truncate privs to another user?

I tried the following and it doesnt work.

grant truncate on table A to user A;

[hrishy]

Hi

grant the drop table privelege to user A .Then it will work.

regards
Hrishy

[pnrdba]

Nope, u can't.

SQL> create table dummy_fac as select * from m_fac;

Table created.

SQL> grant truncate on dummy_fac to adm2;
grant truncate on dummy_fac to adm2
*
ERROR at line 1:
ORA-00990: missing or invalid privilege

[mooks]

Hi

I do not want to drop and re-create the table. I just want to clear the table. As there are mnay records inside therefore I would like to use truncate instead of delete.

In the first place, is there a grant truncate in Oracle??

Thanks!

[jovery]

There is no TRUNCATE privilege, in order to do this you will need to grant the DROP ANY TABLE privilege to the user if they don't own the object.

Regards

[jmodic]

Originally posted by mooks
In the first place, is there a grant truncate in Oracle??

No, there is not. The only system-provided method is the one suggested by hrishy (GRANT DROP ANY TABLE TO ...). For a more safe alternative, check
http://www.dbasupport.com/forums/sho...threadid=20154

[mooks]

In a procedure, cannot just use the command truncate table right? There will be an error.

create procedure abc as
begin
truncate table a;
end;
/

[jmodic]

Nope, you'll have to use dynamic SQL.

[nagarjuna]

execute immediate '************';