Hi Jonathan, OK let's give it a go (note that I'm now doing this at home on my not quite so powerful PC with Best of Bowie playing on the CD so it's just me having a play)
SQL> create tablespace biggish 2 datafile 'c:\bowie\biggish01.dbf' size 3000m 3 uniform size 24k; Tablespace created. SQL> set timing on SQL> create table lots_of_extents (x number) 2 storage (initial 1450m) 3 tablespace biggish; Table created. Elapsed: 00:01:48.02 SQL> select count(*) from user_extents where segment_name = 'LOTS_OF_EXTENTS'; COUNT(*) ---------- 61887 Elapsed: 00:00:08.01 SQL> drop table lots_of_extents; Table dropped. Elapsed: 00:00:16.09 SQL> create table lots_of_extents1 (x number) 2 tablespace biggish; Table created. Elapsed: 00:00:00.01 SQL> create table lots_of_extents2 (x number) 2 tablespace biggish; Table created. Elapsed: 00:00:00.00 SQL> begin 2 for i in 1..61886 loop 3 execute immediate 'alter table lots_of_extents1 allocate extent'; 4 execute immediate 'alter table lots_of_extents2 allocate extent'; 5 end loop; 6 end; 7 / PL/SQL procedure successfully completed. Elapsed: 00:19:29.07 SQL> select count(*) from user_extents where segment_name='LOTS_OF_EXTENTS1'; COUNT(*) ---------- 61887 Elapsed: 00:00:04.09 SQL> select count(*) from user_extents where segment_name='LOTS_OF_EXTENTS2'; COUNT(*) ---------- 61887 Elapsed: 00:00:04.08 SQL> drop table lots_of_extents1; Table dropped. Elapsed: 00:00:06.05 SQL> drop table lots_of_extents2; Table dropped. Elapsed: 00:00:05.07 It's actually somewhat faster but in case caching and the such has had an effect ... SQL> create table lots_of_extents (x number) 2 storage (initial 1450m) 3 tablespace biggish; Table created. Elapsed: 00:01:13.00 SQL> drop table lots_of_extents; Table dropped. Elapsed: 00:00:05.06 So it's all about the same.... I have a little experiment in mind that could cause me to reconsider heaps of extents but it might have to wait a day or two. Cheers Richard ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Saturday, April 05, 2003 5:04 AM > > 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). > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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).