Whittle Jerome Contr NCI,
1.Temporary tablespace in oracle8i+ does not need coalesce, and Oracle do not
deallocate used sort extents. This is a feature.
2.There is no pctincrease parameter in TEMPORARY tablespace.
SQL> /
create temporary tablespace test_tmp tempfile '/home/oracle/temp.dd' size 10m default
storage(pctincrease 0)
*
ERROR at line 1:
ORA-25139: invalid option for CREATE TEMPORARY TABLESPACE
Elapsed: 00:00:00.04
[oracle@ish3 oracle]$ sql
SQL*Plus: Release 8.1.7.0.0 - Production on Tue Feb 11 23:31:50 2003
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
SQL> alter tablespace temp default storage(pctincrease 0);
alter tablespace temp default storage(pctincrease 0)
*
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE
Regards
zhu chao
msn:[EMAIL PROTECTED]
www.happyit.net
www.cnoug.org(China Oracle User Group)
======= 2003-02-11 08:39:00 ,you wrote£º=======
>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.
>[EMAIL PROTECTED]
>618-622-4145
>
>> -----Original Message-----
>> From: chao_ping [SMTP:[EMAIL PROTECTED]]
>>
>> 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:[EMAIL PROTECTED]
>> 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.
>> >[EMAIL PROTECTED]
>> >618-622-4145
>> >
>> >> -----Original Message-----
>> >> From: Zabair Ahmed [SMTP:[EMAIL PROTECTED]]
>> >>
>> >> 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: chao_ping
INET: [EMAIL PROTECTED]
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: [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).
