RE: drop tablespace temp INCLUDING CONTENTS hang

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
>
>
>
>
> --
> 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).
>
>

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

-- 
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).