Re: Temp Tablespace

Hi Sunil,

The problem is that temporary bytes can appear either in dba_data_files or dba_temp_files, depending how you defined your temporary tablespace(s). Thus,

select sum(bytes) "Total temp space in DB" from (select tablespace_name,bytes from dba_data_files union all
select tablespace_name,bytes from dba_temp_files) where tablespace_name in (select tablespace_name from dba_tablespaces where contents = 'TEMPORARY');

gives the total temp space in your DB (provided all datafiles/tempfiles are online).

To find the space which is currently allocated for temporary segments in those tablespaces:

select nvl(sum(bytes),0) from dba_segments where tablespace_name in
(select tablespace_name from dba_tablespaces where contents = 'TEMPORARY')

• Kenneth Koenraadt

On Mon, 21 Oct 2002 21:06:37 +0530, "Sunil" <sunil_franklin_at_hotmail.com> wrote:

>If I need to find out how much TEMP tablespace I have do I see
>v$temp_space_header.bytes_free or dba_temp_files.bytes
>
>Thanks,
>Sunil.
>
>
>
>
>
Received on Tue Oct 22 2002 - 04:21:04 CDT