Re: truncate table

We got around the problem by creating a procedure and using the PRAGMA AUTONOMOUS_TRANSACTION which makes the
procedure autonomous (independent).

create procedure ( table_name in varchar2) AS PRAGMA AUTONOMOUS_TRANSACTION;
sql_stmt varchar2(4000);
begin
 /* Truncate table */
sql_stmt := 'truncate table ' || table_name ; execute immediate sql_stmt;
return;
exception
 when others then
 raise_application_error(-20000,'Fail to Truncate Table ' || table_name || ' Error: ' || sqlerrm);
 return;
end;

"Maxim Demenko" <mdemenko_at_gmail.com> wrote in message news:d74r59$v5a$05$1_at_news.t-online.com...
> BigBoote66_at_hotmail.com schrieb:
>> We faced a problem similar to this in our application (although in our
>> case, it was dropping & rebuilding indexes on a live system with a
>> decent amount of activity). We never found an adequate way of
>> preventing queued transactions from interfering with our drop, but
>> there are some work-arounds:
>>
>> 1). The "Just Keep Trying" approach. Do your truncate inside a pl/sql
>> loop, trap the exception that indicates you failed to lock in exclusive
>> mode, and keep trying (with some small wait time to prevent cpu burn)
>> until you succeed. This worked for a while, but as the load on our
>> system increased, it eventually became unusable.
>>
>> 2). Use a second resource (in our case, a dummy table) to serialize
>> access to the table you want to truncate. For example, if the table
>> you want to truncate is MyTable, create an empty table called
>> MyTable_locker, and before you ever access MyTable in your regular
>> code, acquire a Row Share lock on MyTable_Locker, like this:
>>
>> LOCK TABLE MyTable IN ROW SHARE MODE;
>> [your statements affecting MyTable]
>>
>> Then, when you want to do your truncate, acquire an exclusive lock on
>> MyTable_lock, then do the truncate:
>>
>> LOCK TABLE MyTable IN EXCLUSIVE MODE;
>> Truncate table MyTable;
>>
>> This technique still isn't perfect. Even though there won't be any
>> locks queued on MyTable when you invoke the truncate, the implicit
>> commit by the truncate is not part of the truncate statement itself, so
>> it's possible that the lock aquired by the other sessions could occur
>> between the time you implicitly commit and when you actually start
>> truncating.
>>
>> To get around this problem, we put a dbms_lock.sleep(.1) between the
>> lock & the regular statements (don't put it between the lock & the
>> truncate table, though):
>>
>> LOCK TABLE MyTable IN ROW SHARE MODE;
>> dbms_lock.sleep(.1);
>> [your statements affecting MyTable]
>>
>> This pretty much ensures that your truncate will kick in. (You still
>> may want to include the "Keep Trying" logic in #1 above in order to
>> account for the few rare cases where someone manages to sneak by).
>> However, it also puts a .1 second delay into every statement that
>> accesses MyTable. In our case, this wasn't prohibitive, but your
>> mileage may vary.
>>
>
> Well , for such kind of maintenance from 9i onwards there is a beautiful
> feature introduced - ALTER SYSTEM QUIESCE RESTRICTED ( you have to have
> resource manager enabled for it, in my opinion , that *must* be done
> anyway in every bigger production environment )
> may be it is not every situation appliable, but it is in some cases
> definitely very valuable.
>
> Best regards
>
> Maxim
Received on Thu May 26 2005 - 12:12:35 CDT