Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Maxextents unlimited on LMT ?
In article <3D2B34C3.1E50_at_yahoo.com>, Connor says...
>
snip
>
>So the quote was:
>
>"The number of extents in a locally managed tablespace has absolutely
>none (very negligible in the worst of cases) impact on performance"
>
>Could you pass this test onto the development lead...
>
>SQL> create tablespace dummy
> 2 datafile 'e:\orasw\db1\dummy.dbf' size 2001m
> 3 extent management local uniform size 32k;
>
>SQL> create user x identified by x;
>
>User created.
>
>SQL> alter user x quota 1000m on dummy;
>
>User altered.
>
>SQL> set timing on
>SQL> create table x.p ( x number ) tablespace dummy
> 2 storage ( initial 99m next 99m minextents 10 );
>
>Table created.
>
>Elapsed: 00:02:04.57
>SQL> drop table x.p;
>
>Table dropped.
>
>Elapsed: 00:01:92.14
>system_at_db1>
>
>2 MINUTES!!!! WHAT A SHAMBLES!
>
>When they put the quota system in a bitmap as well, then I'll be
>convinced :-)
>
>Cheers
>Connor
>--
>==============================
>Connor McDonald
>
>http://www.oracledba.co.uk
>
>"Some days you're the pigeon, some days you're the statue..."
Hmm, not sure what you ran this on (maybe you've turned off the FAST=TRUE parameter!). Here's what I get on my PC with 9.2:
SQL> create tablespace dummy
2 datafile 'c:\temp\dummy.dbf' size 2001m
3 extent management local uniform size 32k;
create tablespace dummy
*
ERROR at line 1:
ORA-01119: error in creating database file 'c:\temp\dummy.dbf' ORA-27044: unable to write the header block of file OSD-04008: WriteFile() failure, unable to write to file O/S-Error: (OS 112) There is not enough space on the disk.
Damn ran out of space. Clean up and rerun:
SQL> / Tablespace created.
SQL> create user x identified by x;
User created.
SQL> alter user x quota 1000m on dummy;
User altered.
SQL> set timing on
SQL> create table x.p ( x number ) tablespace dummy
2 storage ( initial 99m next 99m minextents 10 );
Table created.
Elapsed: 00:01:08.02
SQL> drop table x.p;
Table dropped.
Elapsed: 00:00:04.07
SQL> drop tablespace dummy including contents and datafiles;
Tablespace dropped.
Note the difference in timings. What version were you on?
HTH. Additions and corrections welcome.
Pete
SELECT standard_disclaimer, witty_remark FROM company_requirements; Received on Tue Jul 09 2002 - 15:49:35 CDT