Re: locally managed tablespaces and free space

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 advance

Total 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 Received on Fri Dec 08 2006 - 09:35:29 CST