temporary space usage

From: <genegurevich_at_discoverfinancial.com>
Date: Wed, 13 Jun 2007 07:48:33 -0500
Message-ID: <OF266D4029.D3E1EF0E-ON862572F9.0045DA01-862572F9.00465BF6@discoverfinancial.com>

He everybody:

I am looking into the TEMP tablespace usage in oracle 10.2.0.2. For a long time I have been using the
following SQL to see how much of the TEMP ts is used: ]

SELECT INST_ID "InstID", SUBSTR(TABLESPACE_NAME,1,15) "TS",
       USED_BLOCKS, (USED_BLOCKS*&bs)/1048576 "Used MB",
       FREE_BLOCKS, (FREE_BLOCKS*&bs)/1048576 "Free MB"

  FROM GV$SORT_SEGMENT; Today I have tried this SQL:

SELECT tablespace_name, SUM(bytes_used), SUM(bytes_free)

             FROM   V$temp_space_header
             GROUP  BY tablespace_name;

These 2 SQLs produce different results. The bottom one shows that there is no free bytes in the TEMP tablespace at all. The top one shows that the TEMP ts is free. OEM also shows that OEM is free. When I executed another SQL

select * from large_table order by column

which should use up a lot of temp tablespace, the top query has been showing larger and larger size of used blocks in the TEMP ts, while the bottom query continued showing it at 100% used.

Does the bottom query indicates that the TEMP TS is used by some temporary segments, which will be reused by any
process that needs TEMP ts and therefore is misleading? Or is there another explanation?

thank you

Gene Gurevich

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jun 13 2007 - 07:48:33 CDT