How to fix drop table and truncate table commands frozen in Oracle 10g
Read Oracle expert Brian Peasland's explanation of why drop table and truncate table commands may be frozen in Oracle 10g.
I am using an Oracle database 10g R2 on a HP-UX machine. I can't use drop table and truncate table commands in my database. Those commands are frozen until I kill the session. Can you help?
The only way to figure out why the commands are "frozen" is to figure out what the command is waiting for. There could be two possibilities here -- either the session is waiting for a lock to be released, or the session is waiting for an event to occur before it can continue with the command.
On my website is a white paper titled Solving User Problems. Page 3 discusses how to find the SID for your session. Once you've identified the SID of the session that is having the difficulty, then look to pages 6-8 to see if another session is holding a lock. If there is no lock, then look to pages 15-17 to see what wait events are holding up the session.
