Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: dropping Undo Tablespace

RE: dropping Undo Tablespace

From: <jim.silverman_at_thomson.com>
Date: Thu, 31 May 2007 15:29:10 -0400
Message-ID: <E2157B37A63D2E4B9B00575021FC84D6014B2276@tshusmian2msg01.ERF.THOMSON.COM>


Unfortunately, I can't answer your question. However, the situation you describe perfectly illustrates my loathing for using AUTOEXTEND ...



Jim Silverman
Senior Systems Database Administrator
Solucient, LLC - A Thomson Company
Telephone: 734-669-7641
FAX:            734-930-7611 
E-Mail:         jim.silverman_at_thomson.com 
		
		From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Godwin vincent
		Sent: Thursday, May 31, 2007 2:53 PM
		To: oracle-l_at_freelists.org
		Subject: dropping Undo Tablespace
		
		Hi all,
		        
		Hope all is well.
		 
		I have an issue with dropping undo tablespace. I am
running Oracle 9i on Solaris 9. On one of our production database, our UNDO tablespace datafiles have autoextend ON, so they have increased in size to about 30G now and have filled up the file system. To get around this problem, i have created a new UNDO tablespace and switched the database to use this 2nd UNDO tablespace. The undo_retention is set to 7200 (sec). It has already been 2 days since i performed the switch and my alert log is still being flooded with " Undo Tablespace 1 Moved to Pending Switch-Out state" . Following is detailed process that i followed,                  

                SQL> create undo tablespace undotbs_2 datafile '/u10/oradata/testdb/undo_00.dbf' size 10240M autoextend off;

                SQL> alter system set undo_tablespace=undotbs_2 scope=both;                  

                Now, i fired the below query to see if any undo segments of the first undo tablespace(undotbs_1) are still online,                  

                SQL> select a.name, b.status from v$rollname a, v$rollstat b where a.name in (select segment_name from dba_segments

                          where tablespace_name='UNDOTBS_1') and a.usn=b.usn;                  

		NAME                 STATUS
		----------                  ----------

_SYSSMU20$ UNKNOWN
_SYSSMU37$ UNKNOWN
_SYSSMU4$ PENDING OFFLINE
I have getting this from previous two days. I gone
through the metalink id: 341372.1 that speaks about this Switch out state and gave two queries to find as what transactions are currently using the Undo tablespace. Following is the output from these two queries,                  

                SQL> select ADDR, XIDUSN,STATUS, START_TIME from v$transaction;

		ADDR               XIDUSN STATUS START_TIME
		-----                        -------    ------
----------
		00000001E347B320   20    ACTIVE  05/27/07 09:13:25
		00000001E1231E38   34    ACTIVE  05/31/07 16:22:31 
		00000001E7VD4F48   37    ACTIVE  05/27/07 23:15:09
		00000001E8DFARE8   16   ACTIVE  05/30/07 05:33:54
		00000001E1006928    4    ACTIVE  05/29/07 11:02:07
		SQL>SELECT a.usn,a.name,b.status,e.sid, e.serial#
,d.addr FROM v$rollname a, v$rollstat b,dba_rollback_segs c, v$transaction d,v$session e WHERE a.usn=b.usn AND a.name=c.segment_name AND a.usn=d.xidusn AND d.addr=e.taddr AND b.status in ('PENDING OFFLINE','UNKNOWN');
		USN  NAME        STATUS           SID  SERIAL#  ADDR
		---      -------             --------               ---
----         -----
		20  _SYSSMU20$   UNKNOWN          22    67
00000001E347B320   
		37  _SYSSMU37$   UNKNOWN           16  13425
00000001E7VD4F48   
		4   _SYSSMU4$    PENDING OFFLINE   34   2121
00000001E1006928    
		It has been almost 2 days these 3 transactions have
started and still running. I wanted to drop the first UNDO tablespace ones all transactions have been completed and all segments are offline but these 3 transactions are preventing this. The only option i see here is to kill the session (22,16,34), which will offline all the undo segments and then drop the first undo tablespace. This is a production box, so just wanted to confirm with you. Is it safe to go ahead and kill the session? is there any other work around in this case for dropping the first undo tablespace.
		Thanks,
		Godwin.
--
http://www.freelists.org/webpage/oracle-l
Received on Thu May 31 2007 - 14:29:10 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US