Re: locally managed tablespaces and free space

On Dec 8, 10:35 am, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> mis2o wrote:
> > hi guys,
>
> > i would like to ask how it is with locally managed tablespaces in
> > Oracle and free space.
> > I work as a support specialist and we often receive a case like this:
> > "Cannot extend temp segment in tablespace XYZ by 128", where XYZ is a
> > data tablespace, not a temporary one. i already know that the numeric
> > value is displayed in oracle blocks, which means that when
> > DB_BLOCK_SIZE is 8KB, it is correct to multiply the value by 8 and get
> > the size in kilobytes.
>
> > i'm interested in this: if the tablespace is a locally managed one and
> > i find out that there is enough free space in this tablespace by
> > "select sum(BYTES/1024/1024) Free_MB from dba_free_space WHERE
> > tablespace_name = 'TS_NAME';" will Oracle be able to use all of this
> > space for allocating extents or just the contiguous part? (i know this
> > was the case with Dictionaty Managed Tablespaces).
> > so which is relevant for me in this case? sum(bytes) or max(bytes) from
> > dba_free_space?
>
> > thank you for your anwer in advanceTotal free space isn't the same as contiguous free space, which you are
> NOT returning with the query above, and it is contiguous free space
> which is the problem. Oracle is reporting correctly when you receive
> such errors, and know that any segment created in a tablespace is a
> temporary segment until the process creating that segment completes.
>
> David Fitzjarrell- Hide quoted text -- Show quoted text -

Being that the OP said LMT tablespaces then if the free space is contiguous or not may not matter.

For uniform extent tablespaces I just keep track of how many free extents remain and how quickly I use extents (number per week). Since all allocated extents will be the same size the location of the free extents does not matter, contiguous or not.

For an auto-allocate tablespace I suggest looking for 1- free extents equal to the largest size in use in the tablespace and 2- if this size is below 64M that a free extent (or two) equal to the next size increment exists.

I find that the above guidelines keep me out of trouble.

HTH -- Mark D Powell -- Received on Fri Dec 08 2006 - 10:17:39 CST