Truncate table takes time
I am using Oracle 10g. I have a table with 7 columns. No index on anyone of them. It contains approx 25,000,000 rows. I am using the following command to truncate the table.
TRUNCATE TBALE table name.
It take ages to truncate this table. What could be the reason?
How many extents does the table have ?
please show the actual command...
Does your "actual" truncate table command included "drop storage" option?
If that's the case I'm with hrishy; most probably affected table has a zillion extents.
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
PAVB, I am not using drop storage option.
Unfortunately, I do not have access to DBA_EXTENTS view. But I could get the following stats from ALL_TABLES
Initial Extent = 65536
Next Extent = NULL
Min Extent = 1
Max Extent = 2147483645
Blocks Used = 314176
Empty Blocks = 8
get the values from all_extents then.
Originally Posted by darshilm
Click Here to Expand Forum to Full Width