Re: drop tablespace temp INCLUDING CONTENTS hang

Hi,

    I do not know what do you means by saying hangs.Drop a non-temporary temp tablespace does take much time if it was ever used heavily.

    I remember when I came to current company they do not used temporary tablespace on 8172 db. Drop the old temporary tablespace took about 30 minutes.But I waited and it did finish:)

    Allocate extents and deallocate extents via dictionay is protected by a space management enqueue, if this takes rather long time, there maybe some session want to allocate extents fail.

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: Thursday, September 18, 2003 2:54 AM

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

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