Re: Calculation of remaining space in LMT's...

Spendius wrote:
> Hi,
> In 10g is there no other way to get info about remaining
> room in locally managed tablespaces (with a calculation
> *taking in account* the eventual AUTOEXTEND mode of
> datafiles) than using DBMS_SERVER_ALERT procedures
> and then SELECTs against DBA_ALERT_HISTORY ?
>
> There are unfortunately no such views as
> V$TEMP_SPACE_HEADER and
> V$TEMP_EXTENT_MAP (that underlyingly query X$KTFTHC
> and X$KTFTME structures) for regular tablespace datafiles...
>
> By the way does running SELECTs against these views
> cause Oracle's reads in temp. files space/bitmap headers ?
>
> Thanks.
>

Querying DBA_FREE_SPACE will show you the amount of free space in your LMT. Then querying DBA_DATA_FILES will show you how much space the datafile can autoextend. The sum of the two will be your total available space, assuming that your data file can actually extend to its maxsize.

A query similar to the following might be what you are looking for:

SELECT SUM(fs.bytes)+SUM(df.maxbytes-df.bytes) AS total_space FROM dba_data_files df, dba_free_space fs WHERE df.tablespace_name=fs.tablespace_name AND df.tablespace_name='USERS';

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown