Re: system tablespace extents question

The system tablespace breaks every "good" design rule in existence (it's dictionary managed, pctincrease is set, it has the maximum number of extents set depending on blocksize, and it (should be) autoextend).

It's *supposed* to do all those things. It's designed with all those things in mind.

You get in there and start fiddling with any or all of those things at your peril.

Leave it well alone.

As for your question : 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 ?

...you'll get 1M extents. Segment extent settings always over-ride tablespace ones (that's why the clause for the table is "storage" and for the tablespace it's "default storage" -the tablespace settings are only what to use by default when you are silent on the subject at the segment level). That's for dictionary managed tablespaces, anyway. Locally managed tablespace are almost completely oblivious to what you say at the segment level, and their settings reign supreme.

My advice is always to forget setting extent sizes at the table level, even if you've got dictionary-managed tablespaces. Doing so causes confusion as to which setting applies (!), and also leads directly to tablespace fragmentation. Set a good default at the tablespace, and let it flood through to the segments.

Regards
HJR "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 personally have some information that this is not possible, and if it
was,
> it would not be wise to make such
> changes to the system tablespace, to avoid data dictionary fragmentation.
> But I am not sure.
> Most docs suggest adding a new datafile to the system tablespace, or
resize
> the old file.
>
> 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 ?
>
> ThanX in advance,
> Jan=)
>
>
>
>
>
Received on Wed Jul 24 2002 - 15:34:53 CDT