Re: autoextend size vs. extent size in LMT

"Ed Stevens" <spamdump_at_nospam.noway.nohow> wrote in message news:3d075d9a.96364364_at_ausnews.austin.ibm.com...
> Platform Win2k, Ora 8.1.7
>
> I have read the DOCS and done a search of the group archives and did not
find a
> definitive answer to this. Doesn't mean it's not there, but if it is I
> overlooked it . . .
>
> ASSUMING one is going to use AUTOEXTEND, what are the mechanics of
extending,
> vis a vis the extent size defined for a uniform extent LMT? For instance,
given
> this TS definition:
>
> CREATE TABLESPACE mydata
> DATAFILE 'E:\ORADATA\orcl\orcl_mydata_01.DBF'
> SIZE 14368K
> AUTOEXTEND ON NEXT 4K MAXSIZE UNLIMITED
> EXTENT MANAGEMENT LOCAL UNIFORM SIZE 32K
> LOGGING
> ONLINE
>
> If the file is full when another table extent is needed, will he get the
> necessary 32k (as specified for the TABLESPACE) in one operation, or will
go by
> the 4k specified for the FILE and repeat as necessary until he has enough
space
> to satisfy the 32k?
>
> Whatever the answer, is there a way to demonstrate and verify?

Very good question. I imagine (but would need to setup a test) that you should be able to get sql from logminer along the lines of

ALTER DATABASE DATAFILE <BLAH> RESIZE <NEWSIZE>; either one or 8 times.

I'm not aware of any way to do it otherwise.
>
>
> And what other suggestions would you have if one were committed to using
> AUTOEXTEND in a production environment?

Don't use Maxsize unlimited. (and bear in mind unlimited actually means extend until you hit the max number of blocks allowable in a datafile.) This is one of the problems with autoextend. people tend to think it stops them worrying about sizing datafiles correctly and monitoring free space, but in fact you still need to monitor both the size of the datafile and the available free space to stop the datafile hitting its max size and then failing to extend, Given this I really don't see why people don't use fixed sizes - though it should be noted my colleagues don't necessarily agree with me so we have a mix. In addition if all your datafiles are (multiples of) the same size then it makes moving them around devices to spread IO a lot easier. (And you don't unexpectedly hit 4gb limits grrrrrr)

HTH

--
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

******************************************