Re: Discrepancy between Free Temp Tablespace in Sql Query vs. Enterprise Manager

"Pradeep Inamati" <pinamati_at_yahoo.com> wrote in message news:ebdb7ec8.0406161024.1da78061_at_posting.google.com...
> On my database, I perform the following SQL query to check the free
> TEMP tablespace :
>
> select a.tablespace_name,(bytes - bytes_used) free_space
> from
> (select TABLESPACE_NAME,sum(BYTES_USED) bytes_used from
> v$temp_extent_pool group by TABLESPACE_NAME) a,
> (select tablespace_name,sum(BYTES) bytes from v$temp_extent_map group
> by tablespace_name) b
> where a.TABLESPACE_NAME=b.TABLESPACE_NAME;
>
> It returns with TEMP 5925502976 bytes.
>
> However, when I check through Oracle Enterprise Manager it tells me
> that the TEMP tablespace is 99% utilized. Can anyone explain the
> discrepancy. Is my SQL incorrect ?

What discrepancy?

You've selected from 'temp_extent_pool' and 'temp_extent_map'. Have a check up on what the word "extent" means in Oracle: space allocated to a segment. So your v$ views are reporting how much space has been allocated or used up, and OEM is reporting on temp space utilisation... sounds like they're both doing exactly the same thing to me.

By the way, if your temporary tablespace is only 99% used, it is probably too big. Temp is supposed to be 100% used. (OK, I'm joking and exaggerating a little... 99% is good enough. Point is, please don't start thinking you have a space utilisation problem on TEMP because it reports as being 99% full. It's designed that way).

Regards
HJR Received on Wed Jun 16 2004 - 15:35:34 CDT