Re: Truncate table 00054

From: Neil Chandler <neil_chandler_at_hotmail.com>
Date: Thu, 28 Apr 2011 17:25:27 +0100
Message-ID: <BLU0-SMTP399CF49A71CEEEFB9F2138859B0_at_phx.gbl>

A truncate is DDL rather than the DML of a delete. Any select will put a high level lock down on the table (to prevent the table being modified while it is being used). If there were any outstanding transactions of any sort, including a select in SQL*Plus, it will prevent a truncate as it needs an exclusive table level lock.

Regards

Neil.
sent from my phone, on a train, so unable to double check.

On 28 Apr 2011, at 15:37, Hemant K Chitale <hemantkchitale_at_gmail.com> wrote:

>
> Something in a DDL (TRUNCATE) Trigger ?
>
> On Thu, Apr 28, 2011 at 9:31 PM, <Joel.Patterson_at_crowley.com> wrote:
> Air pocket. Does anyone have any insight to this issue. I feel I am overlooking a fundamental – or its to early in the morning.
>
> As the owner of the table, a colleague was unable to truncate a table. (He committed his session first), receiving a Resource Busy, ORA-00054.
>
> I was able to delete table and commit easily from another session (as non owner).
>
> There are no foreign keys, it is neither a parent nor child. No unique or primary constraints, and no indexes. (Some check constraints (not null only)).
>
> This lock query returned no rows.
> SELECT sid, DECODE( block, 0, 'NO', 'YES' ) BLOCKER,
> DECODE( request, 0, 'NO', 'YES' ) WAITER
> FROM v$lock
> WHERE request > 0 OR block > 0
> ORDER BY block DESC;
>
> The delete worked before more investigation could be done. (Trying to force revealing a blocker) – production db.
>
> My search criteria for metalink and google is not returning what I need yet. (of course I rarely search metalink well). Searching with oracle-l or ask tom came close but no cigar.
>
>
> Best Regards,
>
>
>
> Joel Patterson
> Database Administrator
> 904 727-2546
>
>
>
>
>
> --
>
> Hemant K Chitale
> http://hemantoracledba.blogspot.com
> http://hemantscribbles.blogspot.com
> http://web.singnet.com.sg/~hkchital

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Apr 28 2011 - 11:25:27 CDT