Jonathan, I agree with you in principal but I checked with my colleague and he agrees with me that in the past that truncating then dropping a large table resulted in a significant reduction in the time required to eliminate a large (few hundred megabyte) table. I have not done this for a couple of releases so it may no longer be beneficial. But then we no longer depend on dictionary management for most of our tablespaces and the sort tablespace is now of type temporary so the average level of demand on the ST lock is much lower for us than it once was. This should be true for most modern installations. If I can find the time and a system with some spare space I can borrow for a couple hours I will see if I can set up a test and then post. -- Mark D Powell -- -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Jonathan Lewis Sent: Thursday, May 06, 2004 11:03 AM To: oracle-l@xxxxxxxxxxxxx Subject: Re: truncate a table with many extents Shouldn't make a significant difference. The undo and redo would only be about the data dictionary. There may be some versions of Oracle where the different actions produce a different number of updates on seg$ or tsq$, though, but that's just a random thought. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated May 1st ----- Original Message ----- From: "Powell, Mark D" <mark.powell@xxxxxxx> To: <oracle-l@xxxxxxxxxxxxx> Sent: Thursday, May 06, 2004 3:45 PM Subject: RE: truncate a table with many extents If I am not mistaken dropping the table should be much worse than using truncate because the use of drop would greatly increase the amount of undo that Oracle has to keep track of. -- Mark D Powell -- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------