Re: Maxextents unlimited on LMT ?

"Howard J. Rogers" <dba_at_hjrdba.com> wrote in message news:aggtd8$se7$1_at_lust.ihug.co.nz...
> I wish I could work out what the essential point of this test is
<snip>
> Is the point that quota unlimited appears to halve the table creation
time,
> because of all the checking Oracle has to do otherwise? (Forgive me, I've
> had a bad day).

The point is that extent size *does* or rather *can* have an impact on performance (as I hope I demonstrate below). This is from the same system as below but typing *aided* by some Old Speckled Hen.

SQL> create tablespace dummy
  2 datafile 'c:\temp\dummy.dbf' size 2001m   3 extent management local uniform size 64m;

Tablespace created.

SQL> set timing on
SQL> create user x identified by x;

User created.

Elapsed: 00:00:00.00
SQL> create table x.p(x number)
  2 tablespace dummy
  3 storage(initial 99m next 99m minextents 10); create table x.p(x number)
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'DUMMY'

Elapsed: 00:00:00.08
SQL> alter user x quota 1000m on dummy;

User altered.

Elapsed: 00:00:00.01
SQL> create table x.p(x number)
  2 tablespace dummy
  3 storage(initial 99m next 99m minextents 10); create table x.p(x number)
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'DUMMY'

Elapsed: 00:00:00.00
SQL> alter user x quota 1500m on dummy;

User altered.

Elapsed: 00:00:00.00
SQL> create table x.p(x number)
  2 tablespace dummy
  3 storage(initial 99m next 99m minextents 10);

Table created.

Elapsed: 00:00:00.01
SQL> drop user x cascade;

User dropped.

Elapsed: 00:00:04.01
SQL>
> If so, this re-inforces my argument I've advanced for a long while that
much
> as I may think developers have odorous breath and sweaty palms, they are
> part of the team, are (allegedly) professionals, and don't deserve to have
> quotas slapped on them by upstart DBAs. I've long advocated unlimited
quotas
> for everyone. And that's me being serious.

I must admit I agree. Moreover on the development systems they work on they tend to have DBA privs in our environment. The staging area (when we use it :-( ) limits privs as part of testing.

> On the other hand, any developer who created a 100M table in a 32K-extent
> tablespace would soon have sweaty palms for quite a different reason.

If performance is unaffected why would you care? manageability?

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************