temporary space usage
- From: genegurevich@xxxxxxxxxxxxxxxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 13 Jun 2007 07:48:33 -0500
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
