Re: Wrong initial extent in XXX_SEGMENTS for LMT. Minor annoyance, will oracle ever fix this ?

On Thu, 8 Apr 2004 15:45:36 -0400, G Dahler <yellow-shark_at_spamex.com> wrote:

> I know it's probably not THAT important, but who knows ?
>
> When you create a table or an index in a locally managed tablespace, and
> you
> specify an INITIAL extent, oracle ignores the initial extent parameter,

Not quite. If you ask for, say, an initial extent of 627K in a 64K-extent tablespace, you will be given 10 64K extents. You will be given 640K, in other words, which is as near as the tablespace can get to actually respecting your request. So, it is not true to say it "ignores" the parameter. It pays a good deal of attention to it, but is constrained in how it actually goes about allocating it to you.

> and
> depending wether the locally managed tablespace extent allocation is
> autoallocate or uniform, you end up with a number of extents to
> accomodate
> the size you specified.

See. You knew it all along. Please don't use words like "ignore" when it clearly, and you know it, doesn't "ignore" it.

> The problem is that in the INITIAL_EXTENT you find when you query
> DBA_SEGMENTS is the one you specified, but if you look at the actual
> extents, you'll notice that the initial extent is NOT what you specified.
>
> With the NEXT extent, it's not really a problem, it is simply ignored.
>
> Normally, when you create an object in a locally managed tablespace, you
> simply omit the extent clauses and you end up with the correct values in
> initial and next (next, in the case of autoallocate tablespaces)
>
> But when you import tables from dictionary managed tablespaces (or
> probably
> tables exported witht he compress=y, default option) into a locally
> managed
> tablespace, you have lots of discrepancies between the actual extent
> sizes
> and the initial extent reported by DBA_SEGMENTS.
>
> Why don't they fix the import utility so that it drops the "INITIAL"
> parameter when it encounters a locally managed tablespace ?
>
> It's not DANGEROUS per se, but it would make things clearer. I mean, you
> might have some sort of script that does some processing according to the
> initial extent of objects as determined from DBA_SEGMENTS, and in this
> case,
> it would have incorrect input to work on.
>
> I see no easy way of fixing that sort of import problems other than
> pre-creating the tables before import, without specifying any extents
> clauses (A pain in the lower back) or simply live with it.

Living with it is much easier, and probably recommended. Whilst dictionary-managed tablespaces are still a possibility, we have to live with the fact that certain views need to be able to report for them. Meanwhile, we (should) know that for locally-managed tablespace, the only authorative source of information about extent sizes is the tablespace itself, and that accordingly dba_tablespaces is the place to go and look. Or dba_extents, of course.

So to answer your specific question as contained in your posts' header, yes I am sure they will fix it, when they finally get around to completely abolishing data dictionary managed tablespace (and wait a version after that to be sure!).

Regards
HJR

-- 
-------------------------------------------
Dizwell Informatics: http://www.dizwell.com
  -A mine of useful Oracle information-
          -Windows Laptop Rac-
    -Oracle Installations on Linux-
===========================================