Re: interpreting tablespace settings

"Imprecise" <f_puhan_at_precise.com> wrote in message news:f_puhan-AB0D04.16431409062002_at_vienna7.his.com...
> In article <23ded962.0206091124.32f0b2e_at_posting.google.com>,
> tstoneman4_at_hotmail.com (T Stoneman) wrote:
>
> > I was wondering if someone could help me.
> >
> > I am trying to create a simple table and it returned the error:
> >
> > ORA-01658: unable to create INITIAL extent for segment in tablespace
> > TEST
> >
> > I went through google, and it turns out that I needed to increase the
> > size of my tablespace. So that's fine, but I was wondering if someone
> > could help me understand why my tablespace did not automatically
> > resize....
>
> Your tablespace will not resize itself automatically if you haven't
> specified AUTOEXTEND ON in your tablespace's storage clause (Oracle
> 8.1+).
>
> The ORA-01658 error indicates possible severe fragmentation within your
> tablespace, as Oracle needs to find CONTIGUOUS blocks in order to
> satisfy your CREATE statement. Failing to find 64K of contiguous space
> in your tablespace, Oracle throws this error.
>
> > My tablespace has the following settings:
> > BLOCKSIZE 8192
> > INITIAL_EXTENT 65536
> > NEXT_EXTENT <blank>
> > MIN_EXTENTS 1
> > MAX_EXTENTS 2147483645
> > PCT_INCREASE <blank>
> > MIN_EXTLEN 65536
> > STATUS ONLINE
> > CONTENTS PERMANENT
> > LOGGING LOGGING
> > EXTENT_MAN LOCAL
> > ALLOCATION_TYPE SYSTEM
> > PLUGGED_IN NO
> > SEGMENT_SPC_MAN MANUAL
>
> Is this a 9i instance? I don't recognize the SEGMENT_SPC_MAN column.
> It also seems strange to me that you would have blank (are they NULL?)
> values.
>

Yes, this is certainly a 9i installation, as Automatic Space Segment Management is a new feature in 9i. It means that determining which blocks within an extent are able to accept a new insert, or are instead considered full, is handles with a bitmap at the head of each extent, and not by a freelist.

As for next extent being blank -expected with locally managed tablespaces like this one that happen to be created without a 'uniform size' clause. In other words, this tablespace has been set to use the 'autoallocate' policy for LMTs -and that has a slightly odd allocation strategy, such that you start with 64K extents, then get 1Mb extents, then 8Mb and so on. The 'next' extent can therefore change, depending on the number of extents you've already acquired for a segment; what's worse, some segments may be acquiring the 64K extents, some the 8M ones and so on: the tablespace cannot therefore report a 'next' size, since there isn't one, invariant for all segments.

> My experience with Oracle has been that the defaults used for extent
> management when not specified during the CREATE stage is NEXT_EXTENT =
> INITIAL_EXTENT and PCT_INCREASE = 50. I can't say this is still true
> for 9i.
>

It's not true for 9i, actually. The default in 9i is for locally managed tablespaces, autoallocated -which gives rise to this exact output from dba_tablespaces.

9i release 2 changes that slightly: dictionary-managed tablespace can't be created at all in that particular release.

> > I'm having some problems grasping what exactly the NEXT_EXTENT column
> > means...If the next_extent is blank, does that mean that I am still
> > using my initial extent? Or if there is a value within next extent,
> > does this mean that my tablespace grew beyond the initial extent and
> > is now using a new extent. Or does it mean that *if* a new extent
> > will be created, it will use this setting, but it doesn't necessarily
> > mean that I in fact am using a new extent.
>
> NEXT_EXTENT is the value, used in conjunction with PCT_INCREASE that
> tells Oracle how to allocate space for objects when the INITIAL extent
> has grown beyond its ability to store information. At object creation
> time, the object is created with the INITIAL_EXTENT value whenever
> possible. Then, when the object needs to extend, Oracle will use the
> value of NEXT_EXTENT for the second extent. Here's where it gets
> interesting... If the value of PCT_INCREASE is greater than 1, Oracle
> will then base future extent growth on the value of the LAST EXTENT
> created TIMES the value of PCT_INCREASE. While a hotly debated issue,
> most DBAs prefer to specify either zero or one for this parameter,
> thereby establishing control over the possible boundless growth extents
> can experience. There are other reasons, but for this discussion, it
> will suffice.

I think most DBAs would accept that setting it to anything other than zero is pretty odd (except where you can't be on hand to manage the database intensively all the time). And setting it to 1 is quite probably the very worst setting that you could pick. You get none of the benefits of extent growth for fast-growing tables, and all the drawbacks of SMON automatically coalescing tablespace free space whenever it feels like it, regardless of the detrimental effect it may have on your users' I/O activity.

>
> Back to your error: Oracle could not allocate space for the INITIAL
> extent. For this reason, the NEXT_EXTENT and PCT_INCREASE do not come
> into play.
>
> > Does the fact that my next_extent and pct_increase are blank indicate
> > that my tablespace will not auto-increase in size? I read through my
> > books, but they all tell me how to create tablespaces, and not how to
> > diagnose or analyze tablespaces that already exist...
>
> Extent growth is controlled by the MIN_EXTENTS and MAX_EXTENTS
> parameters. The indication is that you've specified UNLIMITED for
> MAX_EXTENTS, but this does NOT mean that your tablespace will grow
> beyond its current size. That capability is controlled by the
> AUTOEXTEND ON clause in the TABLESPACE storage clause. You can turn
> this on using the ALTER TABLESPACE command. Personally, I don't like
> this capability, but that's my own problem. :-)
>

Actually, autoextend is an attribute of the datafiles making up a tablespace, not the tablespace itself. The create tablespace statement can be written thus:

create tablespace X datafile 'c:\x.dbf' size 1m autoextend on;

But the autoextension bit refers to the datafile, not the tablespace. Therefore, to allow autoextension to take place after the event, the correct syntax is:

alter datafile 'c:\x.dbf' autoextend on;

However, it's an extremely bad idea just to switch on autoextend without also saying what it should extend *by* -and when it should stop extending. Thus:

alter datafile 'c:\x.dbf' autoextend on next 10m maxsize 100m;

...would be the preferred command. If you happen to think that autoextend is a good idea, that is. Actually, of course, autoextend is a lousy idea for any database that is going to be properly managed with a more or less full-time DBA on hand, and for which performance is a design requirement. In those circumstances, the DBA should size the thing correctly in the first place, or manually resize the files in anticipation of a large workload:

alter datafile 'c:\x.dbf' resize to 100m;

...means that you are in control of when the resizing happens, not Oracle -and you can therefore schedule it for a time when people won't actually be wanting to do work on the database.

Regards
HJR
> --
> The underscore character does not belong in my address. You know the
drill...
> ***
> Anyone sufficiently smart enough to configure and use USEnet for research
should
> be smart enough to Read The Freakin' Documentation!
Received on Sun Jun 09 2002 - 16:39:49 CDT