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

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 ?

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

• Free up any used extents
• Reset the HWM on the remaining single extent

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