I think you ought to refine your test: Create two tables at one extent each, then alternately allocate one extent to each table until you get to a very large number of extents. THEN try dropping one of them.
Remember to set tablespace quotas for the user creating the table. It still won't scare you off, by the way, so you don't have to do it. Check with Connor which version of Oracle introduced the modification that updates tsq$ just once one the drop, rather than once per extent as this does make a difference. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html ____UK_______April 8th ____UK_______April 22nd ____Denmark__May 21-23rd ____USA_(FL)_May 2nd Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html ____UK_(Manchester)_May ____Estonia___June (provisional) ____USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: 04 April 2003 09:23 > Hi Pete, > > Stop using my favourite answer :) > > I'm not suggesting this is particularly scientific but here's a quick > test on dropping a moderate number of extents (9.2 on XP): > > SQL> create tablespace biggish > 2 datafile 'c:\bowie\biggish01.dbf' size 2000M > 3 uniform size 16K; > > Tablespace created. > > SQL> set timing on > SQL> create table lots_of_extents (x number) > 2 storage (initial 1990M) > 3 tablespace biggish; > > Table created. > > Elapsed: 00:00:49.06 > > SQL> select count(*) from user_extents where segment_name > = 'LOTS_OF_EXTENTS'; > > COUNT(*) > ---------- > 127423 > > Elapsed: 00:00:04.01 > > SQL> drop table lots_of_extents; > > Table dropped. > > Elapsed: 00:00:06.08 > > Based on the above, I could drop a table with 1,000,000 extents in > under 1 minute. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
