Re: system tablespace extents question

"Jan Gelbrich" <j_gelbrich_at_westfalen-blatt.de> wrote in message news:ahmchm$ubno6$1_at_ID-152732.news.dfncis.de...
> Hello,
>
> I was asked if it is possible to alter the system tablespace in a way that
> e.g. the existing 180 extents (max is 249)
> become 1 extent, in order to reduce the number of extents.

I don't understand this question objects have extents not tablespaces.

> The next question that came up to me is, when dealing with other
tablespaces
> where schema objects are put in:
>
> When we create a new tablespace, we set storage parameters like initial
> extent and next extent.
>
> When we create a schema object like a table, we also set those storage
> parameters.
> But, the schema objects are assigned to
> a tablespace (different from system tablespace of course), and if we set
> storage parameters for the object that
> differ from storage parameters of the tablespace, that suddenly starts to
> confuse me.
> Before that I thought I understood the matter, now I feel that I donīt
...8|
>
> What happens e.g. if initial and next extent of a tablespace is lets say
> 100k, and for a table in that tablespace
> we set 1M ?

It depends.

If you have a dictionary managed tablespace then the objects you create use the values you specify in creating the object unless you miss them out in which case they use the values defined at tablespace level. So in a dictionary managed tablespace your table will get 1m extents as you specified at object creation.

In 8i and above you can specify that extent management is done at the tablespace level not in the data dictionary. In this case the values you specify at object creation only determine how many extents you will get not the size of them. Basically you will get enough extents of the size the tablespace thinks they ought to be to get the amount of space specified by your INITIAL clause on the create statement.

You can specify locally management of tablespaces in two ways.

1. Uniform allocation . This is the easiest to understand (and the value I recommend).

Here you issue

create tablespace blah datafile '/u01/blah.dbf' size 1001m EXTENT MANAGEMENT LOCAL UNIFORM SIZE 100k;

to create the tablespace with 100k extents.

then create table foo(bar number) tablespace blah STORAGE (INITIAL 1000K, NEXT 1000K); will get 10 extents of 100k each determined by INITIAL/EXTENT_SIZE = 1000/100.

Every extent in this tablespace will be 100k in size leading to the great advantage of not needing to defragment and coalesce.

2. Automatic Allocation. Here extents are allocated to segments from a predetermined range of extent sizes.

If you specify extent management auto you will still get sufficient extents to allocate 1000k or just over (using my example) but the exact number of extents will be harder to predict.

Automatic extent management should give some performance boost but , because extents end up different sizes you still have the need to reorganise periodically.

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

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