Objects in the temp tablespaces in 9i

Dear experts,

In 8i and before, I could always tell the size of the objects created in the temp tablespace with:

Select SUBSTR(OWNER, 1, 10) OWNER,

   substr(SEGMENT_NAME, 1, 25) segment_name,     sum(bytes) sum_of_bytes,
    sum (blocks) sum_of_blocks,
    count(1) extent_count
from dba_extents
where TABLESPACE_NAME = 'TEMP'
group by OWNER, segment_name
order by sum(bytes), segment_name

The object names would be system generated.

In 9i, this no longer works. I can find out a number of things about the temp tablespace usage, but not the size of the objects. I can:

Find the total amount of temp tablespace used:

Select
TABLESPACE_NAME,
Sum(USED_EXTENTS) USED_EXTENTS,
Sum(TOTAL_EXTENTS) TOTAL_EXTENTS,
(Sum(USED_EXTENTS) / Sum(TOTAL_EXTENTS) ) percent_full
>From v$sort_segment

Group by TABLESPACE_NAME

Sorts by user:

Select
USERNAME ,
TABLESPACE,
SEGTYPE,
Sum(extents ) sum_extents,
Sum( blocks) sum_blocks,
Count(*)
>From v$sort_usage

Group by
USERNAME ,
TABLESPACE,
SEGTYPE Find the sorts to disk:

SELECT NAME, VALUE
FROM V$SYSSTAT
WHERE NAME IN ('sorts (memory)', 'sorts (disk)');

But, how can we find the size of the objects in the TEMP tablespace? Is there such a concept in 9i?

Thanks a lot Received on Thu Jun 15 2006 - 17:52:39 CDT