Re: Win2000/8.1.7.3.0/SQL

The order by is dong a sort and you have insufficient sort space configured so it is doing it to disk and that is the temp tablespace or whatever is allocated as the users temporary tablespace. I suspect that this database has the smallest SGA for any of the databases you have tested.
You might like to investigate the SORT_AREA_SIZE parameter or maybe just look at increasing the available memory to the instance.

Cheers

--


=================================================

Peter McLarty               E-mail: Peter.Mclarty_at_mincom.com
Technical Consultant        WWW: http://www.mincom.com
APAC Technical Services     Phone: +61 (0)7 3303 3461
Brisbane,  Australia        Mobile: +61 (0)402 094 238
                            Facsimile: +61 (0)7 3303 3048


=================================================

A great pleasure in life is doing what people say you cannot do.

    - Walter Bagehot (1826-1877 British Economist)


=================================================

Mincom "The People, The Experience, The Vision"



=================================================


This transmission is for the intended addressee only and is confidential 
information. If you have received this transmission in error, please 
delete it and notify the sender. The contents of this e-mail are the 
opinion of the writer only and are not endorsed by the Mincom Group of 
companies unless expressly stated otherwise. 






"Vergara, Michael (TEM)" <mvergara_at_guidant.com>
Sent by: root_at_fatcity.com
20-08-2002 08:23 AM
Please respond to ORACLE-L

 
        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Fax to: 
        Subject:        Win2000/8.1.7.3.0/SQL


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).



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Peter.McLarty_at_mincom.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).