RE: Locally Managed Tablespaces - Questions.

> Hi Listers,
>
> I am in the process of setting up a new Oracle database on
> 9.2.0.3.0. I want to use locally managed tablespaces, but im
> not sure about the extent and segment management options.
> Should I use uniform extents? If so how do i determine a good
> size for these, or is the Oracle Default good enough.

I would say definitely use uniform extent sizes. You don't want to reorg later after all. As to what sizes to use I would suggest that the sizes oracle uses in its own auto-allocate policy (64k,1m,64m,256m) seem reasonable, provided only that these are multiples of a) the multiblock read count* block size - because that is what oracle will request at a time and b) what the io subsystems can actually provide in one read.

> Also
> what about sement space management, what benefits can this
> provide me, any potential pitfalls i should watch out for?

ASSM is designed to avoid problems of freelist contention. If you don't expect to see it why use ASSM? I'd be nervous because a) the docs refer to t as a feature designed with RAC in mind and b) its in version 1.1 (or thereabouts). This doesn't constitute proof that its bad (the dev db I created with ASSM has had no issues at all 9.2.0.2) just a reason to be a bit suspicious.

> Also should i make my system tablespace locally managed also?

I'd say yes. LMTs are good (and likely mandatory from 10)

> Does this mean i then cant create dictionary managed tablespaces?

Yes it means exactly that, though you can under certain circumstances plug them in as transportable tablespaces.

Cheers

Niall

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Niall Litchfield
  INET: niall.litchfield_at_dial.pipex.com

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: ListGuru_at_fatcity.com (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).