Re: Delete whole table in one go versus batches

In article <PXi89.557$JR1.72798_at_newsfeed.slurp.net>, Ender says...
>
>Coughs, not the owner of the table, access is given to
>insert/select/update/delete for the table. So unless my
>dear DBA grants me to drop any table, I am kind of stuck...
>
>

In addition to Richard's fine doughnut suggestion, a common solution for this is:

connect as_owner_of_table/password

create or replace procedure truncate_t
as
begin

   execute immediate 'truncate table t'; end;
/

grant execute on truncate_t to some_other_user;

connect some_other_user
execute as_owner_of_table.truncate_t;

stored procedures are excellent at encapsulating privileges to whatever level you need/want/desire. For example, that gave someone truncate to that specific TABLE, this:

create procedure truncate_table( p_tname in varchar2 ) is
begin

   execute immediate 'truncate table ' || p_tname; end;

would give them truncate in the schema...

>Richard Foote wrote:
>
>> Hi Ender,
>>
>> Love to hear what those various reasons are ...
>>
>> Love to hear just one.
>>
>> Richard
>>
>> Ender Wiggin wrote:
>>>
>>> Are there any performance gains to delete rows in batches versus one
>>> delete statement if I don't cause the rollback segments extend ? Thnx
>>>
>>> Rows > .5 million
>>>
>>> PS> Cannot use truncate for various reasons...
>

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp