1

I've never truncated a huge table like that. How long can it take? or will be a millisecond or so like usually? Can I have some issue during this operation?

this table has 1.4TB size plus 4.8TB of lob segment.

Also, the database is in archivelog mode, Should I worry about? Also, there is a dataguard of this database.

2
  • Is the table partitioned, Subpartitioned? Are there indexes? Truncating 10,000 subpartitions will take longer. Commented Nov 26, 2021 at 22:58
  • Another question might be - what to do with all the space that gets freed? It might be useful (if possible) to move everything else to a new tablespace and then drop the old one (including contents and datafiles).
    – Iľja
    Commented Nov 29, 2021 at 14:02

1 Answer 1

3

Assuming you are on a supported version of Oracle then a truncate of a table is usually pretty much instant no matter what size. The existence of a lob shouldn't make any difference here.

That said, it does require CKPT to confirm that the dirty buffers for the table have been flushed to disk and all clean buffers are invalidated. This could take some time if the table is heavily modified. You'll see this time under the event enq: RO - fast object reuse. This is typically the slowest part of the truncate (if it is indeed slow). It will be faster with less buffers in the cache associated with the table. It will only block usage of the table being truncated so it is not much to worry about, although if you do need to checkpoint a lot of data then expect a larger IO load which could impact other sessions wanting to do IO (if you reach capacity limits).

You also need to obtain a TM lock on the table in exclusive mode. By default you'll timeout immediately if you are unable to obtain this lock (it's DDL so comes under ddl_lock_timeout) so this is usually not going to be seen.

6.4 TB is a lot of data to just forget about though, this is the sort of thing where you might want to make sure you do something like only rename the table and then wait a decent amount of time before you can be sure the data is not needed.

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.