Re: Objects in the temp tablespaces in 9i

From: Brian Peasland <oracle_dba_at_nospam.peasland.net>
Date: Sat, 17 Jun 2006 03:39:08 GMT
Message-ID: <J0zJHE.1rz@igsrsparc2.er.usgs.gov>

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
>

You can query DBA_SEGMENTS for your TEMP tablespace, but all you will find is segments owned by SYS with funny, system-generated names. These are sort segments that the system manages on behalf of the users.

To determine who is using these sort segments, consider querying V$TEMPSEG_USAGE. HTH,
Brian

-- 
===================================================================

Brian Peasland
oracle_dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

Received on Fri Jun 16 2006 - 22:39:08 CDT