Win2000/8.1.7.3.0/SQL

From: Vergara, Michael (TEM) <mvergara_at_guidant.com>
Date: Mon, 19 Aug 2002 14:23:35 -0800
Message-ID: <F001.004B96B3.20020819142335@fatcity.com>

Hi Gurus:

I have this SQL, see...

col segment_name format a30           heading "Segment Name"
col maxseg       format 9,999,999.9   heading "Largest|Segment (MB)"
col segment_type format a15           heading "Segment|Type"
col pct_increase format 999           heading "Pct|Incr"
col next_ext     format 9,999,999.9   heading "Next|Extent (MB)"
col owner        format a12

prompt Generating First Troubled Objects Listing...

select   a.owner, a.tablespace_name,
         a.segment_name, a.segment_type,
         a.next_extent/1048576 next_ext, a.pct_increase,
         max(b.bytes)/1048576 MAXSEG
from     sys.dba_segments   a,
         sys.dba_free_space b,
         (select max(fs.bytes) maxbytes,
                 fs.tablespace_name tsname
          from   sys.dba_free_space fs
          group by fs.tablespace_name)
where    a.tablespace_name = b.tablespace_name
and      a.tablespace_name = tsname (+)
and      a.next_extent > maxbytes
group by a.owner, a.tablespace_name, a.segment_name,
         a.segment_type, a.next_extent, a.pct_increase

order by a.next_extent desc, owner, segment_name /

...and it works great everywhere. Well, almost everywhere, and there's the rub. Thie query works on my 7.3, 8.0, 8i, 8iR2, and most 8iR3 databases. In this one database, on Windows 2000, it blows out the TEMP tablespace. Every time. And this is the only system where that happens.

There are only 12 tablespaces, and 15 datafiles.

Any ideas why this would fill up the TEMP space?

Thanks,
Mike

---
===========================================================================
Michael P. Vergara
Oracle DBA
Guidant Corporation



-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com


-- 

Author: Vergara, Michael (TEM)
  INET: mvergara_at_guidant.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Mon Aug 19 2002 - 17:23:35 CDT