| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Used/Free Temporary Space
Vivek - The first check is to ensure the username you are running the query
under has the correct temporary tablespace defined. This is not the default
in Oracle and is easy to overlook.
Once you've eliminated this, if you still have the error, you should run EXPLAIN PLAN on your query and post the results here.
Yes, indexes often cure performance problems for all sorts of query
issues. I can't say definitely in your situation without more information.
Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com <mailto:dwilliams_at_lifetouch.com>
-----Original Message-----
Sent: Monday, September 23, 2002 7:53 AM
To: Multiple recipients of list ORACLE-L
Hi
OLDALH Table has Total 100 Million Rows . Each SQL Run may fetch about 2 Million Rows (for Respective SOL_ID Value) from OLDALH Table
solgam is a Master Table Containing about 5 Million Rows
Thanks
-----Original Message-----
Sent: Friday, September 20, 2002 1:43 AM
To: Multiple recipients of list ORACLE-L
Vivek - For the username you are executing this query under, what is the designated temporary tablespace? How many rows (approximately) are you expecting this query to retrieve?
Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com <mailto:dwilliams_at_lifetouch.com>
-----Original Message-----
Sent: Thursday, September 19, 2002 1:18 PM
To: Multiple recipients of list ORACLE-L
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).
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 Mon Sep 23 2002 - 09:18:26 CDT
 |
 |