RE: dropping Undo Tablespace
- From: "Mercadante, Thomas F \(LABOR\)" <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>
- To: <godwin.ror@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 31 May 2007 16:05:02 -0400
Godwin,
What are those sessions? Are they Application connections? If they are
application connections, you can probably kill them if there are no bad
side effects. Not sure of how your applications connect or reconnect
when a session dies.
Tom
--------------------------------------------------------
This transmission may contain confidential, proprietary, or privileged
information which is intended solely for use by the individual or entity to
whom it is addressed. If you are not the intended recipient, you are hereby
notified that any disclosure, dissemination, copying or distribution of this
transmission or its attachments is strictly prohibited. In addition,
unauthorized access to this transmission may violate federal or State law,
including the Electronic Communications Privacy Act of 1985. If you have
received this transmission in error, please notify the sender immediately by
return e-mail and delete the transmission and its attachments.
________________________________
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.
Other related posts:
