Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

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.

share|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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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