RE: Temp Tablespace

RE: SAME and separating disk and index tablespacesHi:

I had this situation last month and I finally bounced the db. "drop tablespace temp INCLUDING CONTENTS" might work if you want to wait long enough. I tried this and the db just hang for 30 min before I killed it. My temp ts was 10G.

Here is a copy of an old email I sent to the list last month:

Yes, you are right, I should have looked at ts# = 2. BTW, I tried several ways to resolve this issue by doing these and none of them worked,

1. alter tablespace temp default storage(pctincrease 0); -- a tip from Steve Adam's site
2. alter session set events 'immediate trace name DROP_SEGMENTS level 3';
3. oradebug wakeup 6; -- 6 is sid for SMON process
4. Create a small table with a primary key constraint then drop the constraint. Iwas hoping that shadow process will change the associated index to a temporary segment and post smon to clear it up.
5. create a large table that will fail:

CREATE TABLE junk ( c1 varchar2(1) ) tablespace DATA storage ( initial 2000M next 2000M) ;

Finally I had to bounce the instance, that cleared up the segment in temp tablespace, so that it could be dropped.

Guang

-----Original Message-----

zhu chao

Sent: Tuesday, September 16, 2003 10:25 PM

To: Multiple recipients of list ORACLE-L

Hi, guang:

First, I think step 5,6,7 are not necessary. Why bother change back to

that named tablespace? To user, temporary tablespace name is totally

transparent to them and to the application.

You should use drop tablespace temp including contents, but the query

you used maybe is wrong. your temp tablespace is TS#=2, but your query is

:select count(*) from fet$ where ts# = 3; (you are using 3 here).

Regards

zhu chao

msn:chao_ping_at_163.com

www.cnoug.org

• Original Message -----

To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>

Sent: Wednesday, September 17, 2003 4:49 AM

> Hi:

>

> Oracle 8173 on Solaris 2.8.

>

> I am trying to convert temp tablespace to LMT. My plan is to

>

> 1.create another temp tablespace temp123

> 2.move all users to temp123

> 3.alter tablespace temp offline

> 4.drop tablespace temp

> 5.re-create temp tablespace as LMT

> 6.move all users to temp

> 7.drop tablespace temp123.

>

> But I am having problem in Step4. I found that there are still some

objects

> in temp ts even if I put it offline:

>

> SYS_at_remax-SQL> select count(*) from dba_segments where

> tablespace_name='TEMP';

>

> COUNT(*)

> ----------

> 1

>

> "drop tablespace temp" resulted in "ORA-01549 tablespace not empty, use

> INCLUDING CONTENTS option". However when I did

>

> drop tablespace temp INCLUDING CONTENTS;

>

> the sqlplus session just hang and I kept getting the same count(*) from

the

> following query (from another session)

>

> SYS_at_remax-SQL> select count(*) from fet$ where ts# = 3;

>

> COUNT(*)

> ----------

> 214

>

>

> I eventually killed the orginal sqlplus session.

>

> I did "ALTER TABLESPACE TEMP coalesce" and it did not seem to help.

>

> So my question is: How can I drop an offlined temp tablespace when there

is

> still segment(s) in it?

>

> I don't know if SMON would clean up the temp ts automatically (Some doc

says

> SMON would not do to TEMP ts). I also read that I could issue

>

> alter session set events 'immediate trace name DROP_SEGMENTS level 3';

>

> to mimic SMON, becuase

>

> SYS_at_remax-SQL> select name, ts# from ts$ where NAME='TEMP';

>

> NAME TS#

> ------------------------------ ----------

> TEMP 2

>

>

> But I am not ready to try it on our production system. Has anyone tries

this

> or has any suggestions? I don't want to bounce db. Would the objects in

TEMP
> ts be cleaned up by SMON if I wait for a couple of days?

>

> TIA.

>

> Guang

--

Author: zhu chao

INET: chao_ping_at_vip.163.com

Fat City Network Services -- 858-538-5051 http://www.fatcity.com

San Diego, California -- Mailing list and web hosting services

---------------------------------------------------------------------



  -----Original Message-----
  From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com]On Behalf Of
Mercadante, Thomas F
  Sent: Thursday, October 09, 2003 10:14 AM
  To: Multiple recipients of list ORACLE-L
  Subject: Temp Tablespace


  All,

  Is there a way to clear a TEMP tablespace defined as Temporary?  We had a
couple of long-running jobs  that have totally clogged up the TEMP
tablespace (54 Gig worth) and it doesn't seem to be releasing the space.  I
know a db restart will clear it.

  Any other ideas?

  8.1.7.4 by the way.

  thanks

  Tom Mercadante
  Oracle Certified Professional


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Guang Mei
  INET: gmei_at_incyte.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).