The Oracle (tm) Users' Co-Operative FAQ

I have done a truncate on a table, and it takes hours to run - what is going on ?


Author's name: Connor McDonald

Author's Email: connor_mcdonald@yahoo.com

Date written: August 27, 2001

Oracle version(s): 7.3+

I have done a truncate on a table, and it takes hours to run - what is going on ?

Back to index of questions


Whilst the truncate command is normally instantaneous, a quick consideration of what it is doing reveals why they sometimes take a long time. The 'truncate' command must do two things in order for a table to be reduced to "zero" size (ie a single extent with a reset high water mark).

We can presume that moving or resetting the high water mark is a relatively painless operation, given that the database is always doing this as tables grow. However, if your table is in hundreds/thousands of extents, then freeing them up can take some time. In particular, when using dictionary managed tablespaces, the two system tables FET$ and UET$ need to be updated. For example, if we perform

SQL> select extents from user_segments where segment_name = 'BLAH'
  2  /

   EXTENTS
----------
        15

SQL> alter session set sql_trace = true;

Session altered.

SQL> truncate table blah;

Table truncated.

and then look at the trace file, we'll see

select length 
from
 fet$ where file#=:1 and block#=:2 and ts#=:3

insert into fet$ (file#,block#,ts#,length) 
values
 (:1,:2,:3,:4)

delete from uet$ 
where
 ts#=:1 and segfile#=:2 and segblock#=:3 and ext#=:4

delete from fet$ 
where
 file#=:1 and block#=:2 and ts#=:3

and these operations are SERIAL, namely, only one session can be performing this at a time. So if you have anything on your database that could be "attacking" FET$ and UET$ (for example, sorting in a permanent tablespace, dropping/adding objects frequently), then you will get these kinds of problems occurring.


Further reading: Some versions of 8.0 would crash when a truncate is terminated with Ctrl-C, so take care


Back to top

Back to index of questions