Yes, I remember that table, and the need to truncate it periodically. Because TRUNCATE is a DDL statement, it is preceded by an implicit commit which would have released the exclusive lock that you were holding. A waiting session then immediately obtained a lock on the table, and the TRUNCATE failed because of that lock. The TRUNCATE statement would have succeeded if there had been no other session waiting for you to release the exclusive lock.

We have a process where an in process table is constantly growing and shrinking, and can get as high as several hundred thousand rows, and the fall back to zero rows. Over time, this pushes the high water mark up, and we take a hit on a perticular process that is forced to do a full table scan. (You may remember this related to some consulting you did for us not too long ago.)

So, anyhow, I noticed it was time to truncate the table again. The problem is that throughout the day, that table sees activity. So, when activity on that table was lulled, I did a "lock table my_tab in exclusive mode". Now, exclusive being the highest level (6) lock, I thought this meant I literally had exclusive access to the table. Yet, when I tried the truncate, after acquiring the lock, I got an "ORA-00054 resource busy and acquire with NOWAIT specified".

Why? I thought the exclusive lock meant I had exclusive access to the table. Can you clue me in on what I'm missing??