| 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: RE: Oracle TEMPORARY SEGMENT
Jerry,
Your sql will never run on Temp tablespace 'that are truly temporary' in 8.1.7....
Just see the results....
Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production
With the Partitioning option
JServer Release 8.1.7.2.0 - Production
SQL> alter tablespace temp default storage(pctincrease 10);
alter tablespace temp default storage(pctincrease 10)
*
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE
If you use temporary_data_files(truly temp tablespace)for temp tablespace with locally managed tablespace , this will not work...
Regards
Rafiq
Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Date: Tue, 11 Feb 2003 08:39:47 -0800
Hi,
It does work on temporary (that are truly temporary and not permanent) tablespaces in 7.3.4 and 8.1.7. I use it when I don't want to wait for SMON to clean things up. Just yesterday one our 'power users' ran SQL that selected from two tables with no join between them. The resulting Cartesian set would have been over 3 billion records. The same SQL also had a complex group by clause to eliminate all the duplicate records he just created. It locked up after many hours because it filled the 1.2 GB TEMP tablespace. I killed the process and ran my SQL to clean out the temp tablespace quickly. BTW: I rewrote his SQL properly and it took less than a minute to return the 1036 records.
Jerry Whittle
ASIFICS DBA
NCI Information Systems Inc.
jerome.whittle_at_scott.af.mil
618-622-4145
> -----Original Message-----
> From: chao_ping [SMTP:chao_ping_at_vip.163.com]
>
> Whittle Jerome Contr NCI,
> I think your sql should not work on a really TEMPORARY tablespace, and
temporary tablespace do not need to be cleaned either.
>
> Regards
> zhu chao
> msn:chao_ping_at_163.com
> www.happyit.net
> www.cnoug.org(China Oracle User Group)
>
> ======= 2003-02-11 06:49:00 ,you wrote£º=======
>
> >Hi,
> >
> >This is what I use to clean up a temp tablespace. The tablespace is
named 'temp' and it is normally set to PCTINCREASE of 10. You will need to
insert the correct values for your temp tablespace. Altering the tablespace
a little wakes us SMON which will clean things up if it can.
> >
> >alter tablespace temp default storage(pctincrease 0);
> >alter tablespace temp default storage(pctincrease 10);
> >
> >50Mb seems rather small for a temp tablespace.
> >
> >Jerry Whittle
> >ASIFICS DBA
> >NCI Information Systems Inc.
> >jerome.whittle_at_scott.af.mil
> >618-622-4145
> >
> >> -----Original Message-----
> >> From: Zabair Ahmed [SMTP:roon987_at_yahoo.co.uk]
> >>
> >> Is their any way of identifying which user/process is holding onto a
temporary segment. I've got a 50Mb temporary tablespace and there is a
temporary segment which is 47Mb which is not being freed up by SMON.
> >>
> >> And, if i can't identify who is holding onto this TEMP segment, is
their a way in which I can delete it and stop the alert log being flooded
with ORA-1652.
> >>
> >> As I say the SMON is failing to clean up this TEMP segment and it's
been on-going for a number of days and I don't have the option of bouncing
the database.
> >>
> >> Oracle 8.1.7.3 on HP-UX 11.
> >>
> >> TIA
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: M Rafiq INET: rafiq9857_at_hotmail.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 Tue Feb 11 2003 - 11:33:56 CST
 |
 |