Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
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.

Take a look at the queries described in or to find out which locks might be blocking.

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
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 or for some examples. Google is your friend here. – ConcernedOfTunbridgeWells Aug 19 '09 at 13:42

When using SAN storage, the filesystemio_options parameter setting should be reviewed. Setting the parameter to SETALL or ASYNCH alleviates log file sync waits during the truncate execution.

On a busy system, running additional DB writers is recommended as well.

share|improve this answer

Your Answer


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.