Re: system tablespace extents question

Jan

The second question is answered most easily - the storage clause specified for the tablespace acts a default for all the storage values and the values not specified when creating a table in that tablespace use these defaults. Some values for a table may be specified and the remainder use the defaults or all can default.

With regards to the first question: When you say there are 180 extents in the system tablespace do you mean you have one object with that many extents or do you have 180 extents scattered among all the dictionary objects you have created. If it's the latter you don't really have a problem.

If you do have an object with 180 extents it might well be that a user has created an "application" table in this tablespace with a storage clause that overrides the system tablespace defaults (I say this because the default PCTINCREASE 50 on system tablespace would normaly keep extents managable for all except large tables though these would typically exceed total space in system tablespace (typically a few 100MBs) ).

Use this to identify potentially rogue tables: - select owner, table_name from dba_tables where tablespace_name ='SYSTEM'
and owner not in ('SYS','SYSTEM','OUTLN')

If this turns out to be the case then move the rogue table to that appropriate tablespace.

Andy

"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 - 09:54:51 CDT