Deleting UNDOTBS

Hi Ken,

I know restarting and checking the DB is not necessary and those steps should be be skipped by a DBA. But looking at the Oracle DB behaviour and DB administration being not my area of expertise I would like to ensure that after doing some major modification the DB opens properly.

Secondly, oracle 9i doesnot delete the physical datafiles in windows.


Well lets get back to the problem. Can anyone tell me - how to drop an undo
tablespace if there is some active rollback segments in it

select * from v$parameter where name like 'undo%'
Please post results.

It'll be retained till undo_retention time has passed and it becomes expired. Your undo_retention is probably a huge number of seconds.

Here's some good info:
DROP TABLESPACE undo02 INCLUDING CONTENTS AND DATAFILES;
· The Undo tablespace to be dropped cannot be in use.
· The clause INCLUDING CONTENTS AND DATAFILES causes the contents (segments) and datafiles at the operating system level to be deleted.
· If it is active, you must switch to a new Undo tablespace and drop the old one only after all current transactions are complete.
· The following query will display any active transactions. The PENDING OFFLINE status indicates that the Undo segment within the Undo tablespace has active transactions. There are no active transactions when the query returns no rows.

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 = 'UNDO01')
AND a.usn = b.usn;

NAME STATUS
------------------ ----------------
_SYSSMU2$ PENDING OFFLINE

Oh, and "DROP TABLESPACE undo02 INCLUDING CONTENTS AND DATAFILES;" doesn't delete the datafiles in MS-Windows?

Humm...

Here's a good resource:

Managing Undo Data

Ken, I know that one needs to wait for the pending transections before trying to drop a tablespace.
Also learnt that I should set the undo_retention parameter to very low and wait until those
transections are over and undo_retention is ignored id the transection is going to take more time than the
specified.

After some workaround I found that there are some active segments dated to 12th Dec 2003 in that undo tablespace.
So I set undo_retention to 5 secs, hanged on for a few minutes and restared the Db. But no outcome! Following
are the information that you wanted:

select NAME, TYPE, VALUE, ISDEFAULT,ISSES_MODIFIABLE, ISSYS_MODIFIABLE
ISMODIFIED,ISADJUSTED,DESCRIPTION from v$parameter where name like 'undo%'


NAME TYPE VALUE ISDEFAULT ISSES_MODIFIABLE ISMODIFIED ISADJUSTED
----------------------------------------------------------------------------------------------------------
undo_management 2 AUTO FALSE FALSE FALSE FALSE
undo_tablespace 2 UNDOTBS FALSE FALSE IMMEDIATE FALSE
undo_suppress_errors 1 FALSE TRUE TRUE IMMEDIATE FALSE
undo_retention 3 5 FALSE FALSE IMMEDIATE FALSE


SQL> SELECT a.name, b.status
2 FROM v$rollname a, v$rollstat b
3 WHERE a.name IN (SELECT segment_name
4 FROM dba_segments
5 WHERE tablespace_name = 'UNDOTBS')
6 AND a.usn = b.usn;

NAME STATUS
------------------------------ ----------
_SYSSMU10$ ONLINE
_SYSSMU6$ ONLINE
_SYSSMU7$ ONLINE
_SYSSMU8$ ONLINE
_SYSSMU9$ ONLINE



Following are the active segments:


select SEGMENT_NAME, TABLESPACE_NAME, STATUS, COMMIT_WTIME from DBA_UNDO_EXTENTS where status!='EXPIRED'

SEGMENT_NAME TABLESPACE_NAME STATUS COMMIT_WTIME
_SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:08:39
_SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:08:42
_SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:08:42
_SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:08:45
_SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:08:45
_SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:08:49
_SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:08:49
_SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:08:52
_SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:08:54
_SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:08:54
_SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:08:58
_SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:08:58
_SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:09:00
_SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:09:00
_SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:09:03
_SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:09:03
_SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:09:33
_SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:09:33



Following are the no of bytes that is needed:

1* select SEGMENT_NAME, TABLESPACE_NAME, STATUS, COMMIT_WTIME from DBA_UNDO_EXTENTS where status!=
SQL> SELECT (UR * (UPS * DBS)) + (DBS * 24) As "Bytes"
2 FROM (SELECT value As UR
3 FROM v$parameter
4 WHERE name = 'undo_retention'),
5 (SELECT (SUM(undoblks)/SUM(((end_time -
6 begin_time) * 86400))) As UPS
7 FROM v$undostat),
8 (SELECT value As DBS
9 FROM v$parameter
10 WHERE name = 'db_block_size');

Bytes
----------
98304


HOW DO I GET RIGD OF THESE SEGMENTS?

Your active undo tablespace is UNDOTBS.

You can't drop the active undo tablespace.

From your previous post:

Quote:

---

SQL> DROP TABLESPACE UNDOTBS INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE UNDOTBS INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU6$' found, terminate dropping
tablespace

---

Did you create another one, then switch to it? Maybe we need to start over. Something is obviously not right here. If it's got active rollback segments in it after the undo retention time, it's active. You're looking for no segments or pending offline segments - the query is in the link I posted last.

By the way, be careful of setting undo_retention too low. Set it longer than your longest transaction or the max size you want your undo tablespace to grow. Yours is fine if your transactions don't last more than 5 seconds.