Re: create local tablespace sanity check

Mike,

Short answer is you are not hosed and the tables will grow by extending just like normal.

Going to a locally managed tablespace with uniformed size extents doesn't stop your tables occupying more than one extent. What it means is that all tables in that tablespace must be made up of extents of that size (Using your example you cannot create a table with other than 256K initial or next parameters). The setting of uniform relates to the tablespace and has no relation to your actual datafile settings other than noted below.

Note, you will not quite get the full 2G/256K worth of extents in your datafile as Oracle allocates some space in the tablespace for the extent management, hence the locally managed. Also F.Y.I. locally managed does not require uniformed size.

Kind Regards,

Fraser McCallum
MVP Oracle Administration
www.brainbench.com

"Mike Jay" <mikejay_at_mitre.org> wrote in message news:3BEABADC.F6FEE0B5_at_mitre.org...
> Hi folks,
>
> Is what I think I am doing what I am doing?
>
> Intent:
>
> My intent is to make a tablespace with an initial size of 20M where the
> datafile will stay below the 2G threshold with uniform extents of 256K
> via local management.
>
> Environment:
>
> Solaris 5.8 (assume 32-bit limitations)
> Oracle 8.1.6
>
> Actual SQL statement:
>
> CREATE TABLESPACE theTablespace
> DATAFILE 'theDataFile.dbf' SIZE 20M
> AUTOEXTEND ON MAXSIZE 2000M
> EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K;
>
> The UNIFORM SIZE 256K is I must confess a best guess based on the
> dictionary managed tablespace upon which the local is based.
>
> In the maxdatafiles thread some time back, there was an article that
> indicated the MAXSIZE had to in actual fact be some number less than 2G,
> but I have yet to find it.
>
> One other question specific to local tablespaces, if the size of the
> table exceeds 256K what happens, does it just use two extents? Or am I
> hosed?
>
> Dictionary statement:
>
> CREATE TABLESPACE theDictionaryTablespace
> DATAFILE 'theDictionaryDataFile.dbf'
> SIZE 20M AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED
> DEFAULT STORAGE (INITIAL 50K
> NEXT 100K
> PCTINCREASE 0
> MINEXTENTS 1
> MAXEXTENTS UNLIMITED);
>
> Having come from the HP-UX world with 8.0.5, the use of local extent
> management is new.
>
> I have looked at the Oracle docs and HJR's Admin Tips, but I am missing
> an obvious gotcha here?
>
> Thanks,
> mikejay
Received on Thu Nov 08 2001 - 11:32:02 CST