Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Problem

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

Question

  • How can I do to make truncates faster ?
  • Also, Is there a way to see the progress of the truncate operation ?
share|improve this question
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. –  a_horse_with_no_name Sep 16 '13 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 '13 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 '13 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 '13 at 4:48

1 Answer 1

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.

share|improve this answer
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 '13 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 '13 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. –  Colin 't Hart Apr 16 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. –  Colin 't Hart Apr 16 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 at 8:41

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.