• I'm truncating a very large table ( 210+ million rows ).
  • It's been hours since it started and I have no idea when it will be done.
  • There're several tables like that, that I want to truncate.
  • The database is Ora9i.


  • How can I do to make truncates faster ?
  • Also, Is there a way to see the progress of the truncate operation ?
  • 4
    Most probably the truncate is waiting for a lock or something similar. Check v$session or v$lock whether the session is being blocked by some other session. Btw: Oracle 9 has been de-supported for a very long time. You should really plan on upgrading to a supported version.
    – user1822
    Sep 16, 2013 at 18:43
  • You have to wait until all extents are deallocated. I think you can try a select extents from dba_segments where owner='TABLEOWNER' and segment_name='TABLENAME' to watch the operation.
    – miracle173
    Sep 16, 2013 at 19:06
  • did you investigate the problem? did you find a blocked session or locked object or a high number of extents?
    – miracle173
    Sep 22, 2013 at 9:15
  • there must be a wait event. check v$session_wait view,combine with v$session,kill the process and try again.
    – user30587
    Nov 16, 2013 at 4:48

2 Answers 2


I assume you are using dictionary managed tablespaces. You have to wait until all extents are deallocated. The following statement should report the number of extents that are to deallocate:

select extents 
  from dba_segments 
  where owner='TABLEOWNER' 
  and segment_name='TABLENAME' 

Index segments and materialized view logs have to be deallocated too, if they exist.

If you drop and recreate the table you will have to wait, too. Dropping the table will take as long as truncation of the table because the number of extents to release is almost the same.

Maybe the following will help: instead of drop and recreate you

1) rename the table TABLENAME to OLDTABLE
2) create a new table with the original name TABLENAME. Now you have an empty table TABLENAME
3) you drop the table OLDTABLE

The advantage of this approach is you can continue your work with table TABLENAME after step 2. But the space occupied by the former table TABLENAME will be release during the execution of step 3. In Linux you can start the job that does step 3 in the background with nohup so that you must not leave your terminal window open. Use nohup sqlplus user/password@tnsname @dropscript.sql &.

Even if you want to restart the database during execution of step 3 this is not a problem because Oracle can handle such situations. As far as I remember it does this in the following way: After the restart of the instance (or if the sessions that does the drop of the table dies for some other reasons) the segment of the table is converted to a temporary segments. Its extents are deallocated by a background process (I think it is smon). The table is removed from the dictionary after the death of session so the tablename OLDTABLE is not visible in the dictionary anymore.

  • 2
    I really wouldn't advocate killing smon, or any other Oracle background process. I could be wrong, but I think killing it might even shut the DB down [abnormally]
    – Philᵀᴹ
    Sep 16, 2013 at 22:04
  • @Phil: Of course smon should not be killed. I do not advocate to kill smon but to kill the shadow process of the session. After killing this shadow process smon will take over the cleaning up of the segment.
    – miracle173
    Sep 17, 2013 at 4:06
  • Sorry, but if you're going to go the trouble of dropping the table you might as well do it cleanly: rename the original table to "delete_me" and create a new table in its place. Then drop "delete_me" at your leisure. Apr 16, 2014 at 18:41
  • Or if you have a license for partitioning: create a partitioned table containing just the single partition. Then use partition exchange with an empty table. Then drop or truncate the exchanged table at your leisure. Apr 16, 2014 at 18:42
  • @Colin 't Hart You are right, renaming the table is the better way. I will change my post in this way as soon as I have the time. Nevertheless killing the session must be handled by the database in an adequate way (at least if the licence per core costs about 50000$). Oracle does this in the way described. I don't remember what was the reason why i did this on some 9i databases in this way. Maybe it was simply necessary to restart the database and I did not want to wait for the drop session to terminate.
    – miracle173
    Apr 22, 2014 at 8:41

What finally solved my problem was turning archive mode off, perform the truncation, then turn archive mode back on.

With archive mode on, the truncation took too long and corrupted the database.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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