RE: dropping Undo Tablespace
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 31 May 2007 16:43:14 -0400
Autoextend can be a valuable tool as long as you ALWAYS set a maximum
size on an auto-extend data file.
-- Mark D Powell --
Phone (313) 592-5148
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
jim.silverman@xxxxxxxxxxx
Sent: Thursday, May 31, 2007 3:29 PM
To: godwin.ror@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: dropping Undo Tablespace
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@xxxxxxxxxxx
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Godwin vincent
Sent: Thursday, May 31, 2007 2:53 PM
To: oracle-l@xxxxxxxxxxxxx
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.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
