dba_222_at_yahoo.com wrote:
> 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
<WITH APOLOGIES>
I can't tell you why but I am suffering from an irresistible urge
to ask if you have any real work to do so I will.
What is the business issue you are addressing?
</WITH APOLOGIES>
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Thu Jun 15 2006 - 18:05:02 CDT
