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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
