up vote 1 down vote favorite
share [g+] share [fb]

Usually, truncating a table takes 5-10 seconds. But when several people work on the same DB instance (but different tables), the operation can take more than an hour. How do I debug this?

link|improve this question
88% accept rate
feedback

1 Answer

up vote 4 down vote accepted

Locks on the table or on data dictionary resources used by the 'truncate table' command could cause this. The system could possibly be waiting around for a resource to be released before it can do the truncate.

Check whether the session is blocked waiting for a lock to be released.

link|improve this answer
Can you tell me some SQL that I can run to see what is happening? – Aaron Digulla Aug 19 '09 at 13:36
1  
I don't have any to hand but there are lots of Oracle resources on the web with scripts that do this type of thing. Take a look at the queries described in orafaq.com/node/854 or dba-oracle.com/t_locked_rows_user_locks.htm for some examples. Google is your friend here. – ConcernedOfTunbridgeWells Aug 19 '09 at 13:42
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.