Used/Free Temporary Space

From: VIVEK_SHARMA <VIVEK_SHARMA_at_infosys.com>
Date: Thu, 19 Sep 2002 10:18:26 -0800
Message-ID: <F001.004D4148.20020919101826@fatcity.com>

On Issue of Following Query :-

select ollh_acid,decode(oldalh.b2k_type,'ACFFD',oldalh.modify_bod_date , oldalh.another_date) from oldalh,solgam
where oldalh.acid=solgam.acid
and solgam.sol_id = CONSTANT

NOTE - NO Index Exists
acid has a Unique Value in Every Record of solgam Table sol_id is a Value for a Set of solgam.acid Values in solgam Table

Error :-

ORA-01652 "unable to extend temp segment by %s in tablespace %s"

NOTE - TEMPORARY Tablespace of Size 11 GB

Size of OLDALH Table = 6 GB
Size of solgam = 500 MB

Qs Should the Above Query Used Temporary Space ? if so , Why ?

Qs From which View (How) can we find the Total Temporary Space Used OR Free in Temporary Tablespace

Qs Will Creating an Index on oldalh.acid Reduce Usage of Temporary Space ?

Qs What index Creation is Advisable on solgam Table ?

--

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

Author: VIVEK_SHARMA
  INET: VIVEK_SHARMA_at_infosys.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

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 Thu Sep 19 2002 - 13:18:26 CDT