Deleting UNDOTBS

HI DBAs,

I am a J2EE developer and have a very basic question about UNDO tablespace. I have a 9i database running in NO ARCHIVELOG mode and the UNDOTBS01.DBF file has eaten up more than 6GB of my disk space. There are three other datafiles each of 1 GB added to the UNDO Tablespace. The problem is- I am running out of space.

So can I make the UNDOTBS01.DBF (I think this one is created by default at the first transection) offline and than delete it safely?

I tried this with a development db in same envirnment. It gave me message that I need to enable Media Recovery first. I enabled Archivelog mode and then the file was allowed to make offline. But the db got shutdown and didnot open ! It gave me the message that the UNDOTBS01.dbf needs to be recovered. And then I found that to do that one needs to connect to OEM which I dont have. Under these circumstances, I think it is not at all safe to attempt deleting the UNDOTBS file.

Then I would like to ask you - can I shrink the size ? The file is 85 % used.

**I saw an existing question like this; but the discussion itself brings some questions about those participants**

create a new undo

then modify the default undo to the new one

alter system set undo_tablespace = 'UNDOTBS02';

drop the old one

conn sys/password as sysdba

Identify the current undo tablespace:

set long 1000
select dbms_metadata.get_ddl('TABLESPACE', 'UNDOTBS1') from dual;

DBMS_METADATA.GET_DDL('TABLESP
--------------------------------------------------------------------------------

CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE
'C:\ORACLE\ORADATA\DB10G\UNDOTBS01.DBF' SIZE 26214400
AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE



1 row selected.

Use this information to build a new undo tablespace, remember to change both the tablespace name and the datafile name. Also, don't forget to define a smaller size or there isn't much point doing this:

CREATE UNDO TABLESPACE "UNDOTBS2" DATAFILE
'C:\ORACLE\ORADATA\DB10G\UNDOTBS02.DBF' SIZE 26214400
AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

Tell the system your switching to another undo tablespace:

ALTER SYSTEM SET undo_tablespace=UNDOTBS2;

Drop the original undo tablespace:

DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

Especially on Windows, you might find the datafiles associated with the undo tablespace remain on disk. If so, restart the instance:

SHUTDOWN IMMEDIATE
STARTUP

Then manually remove the datafiles associated with the initial undo tablespace. These should be listed by the query at the top.

You should now have a smaller undo tablespace.

Cheers

Tim...

Sorry Pando. By the time I'd written the example you'd beat me to the punch. I guess I should requery before posting :)

Cheers

Tim...

Oh !!, Great guys. Such a detailed and prompted reply :) . Lots of Thanks to Tim and Pando & Company.

I am testing this in my dev instance.

Hi,

The procedure that you people mentioned works fine in my dev server. But when I did the same in the actual server it gave me the following error while dropping the old tablespace:

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


I tried after restarting the DB also; didnot work .

Then I did this:

SQL> drop rollback segment "_SYSSMU6$";
drop rollback segment "_SYSSMU6$"
*
ERROR at line 1:
ORA-30025: DROP segment '_SYSSMU6$' (in undo tablespace) not allowed


:confused: What to do ?

alter tablespace x offline;

Check the tablespace has a status of "offline pending", which means that it won't offline it until the active transactions that are using the tablespace have finished processing. One you see the tablespace is offline, then you can drop it.

Hi waitecj,

I had already tried that. I made the sablespace offline and then tried dropping it. But it didnot work.

did you actually create a new one and assign that to be the new undo tablespace for the database?

Can't you just shrink the existing one after the re-boot?

SQL> ALTER DATABASE
2 DATAFILE 'D:\ORADATA\Hanky\SYSTEM\UNDOTBS01.DBF' RESIZE 1500M;

Database altered.

SQL>


Another thing to look into is WHY did it grow this big?

I checked the spfile and init.ora.
The undo_management parameter is set to AUTO.
In spfile the undo_tablespace was pointing to my new tablespace while in init.ora it was pointing to my old one. I changed it, restarted the db.

Checked the status in dba_tablespaces. The old one is marked offline there.
But still I get the same error if I try to drop it. It doesnot allow me to drop that active rollback sagment also! :confused:


Aaha, - Mr davey23uk - Didn't you notice the **.....** note in my first post ?
Please stay away..

Try this.
alter tablespace undotbs1 online;
drop tablespace undotbs1 including CONTENTS AND DATAFILES;

Hi engiri,
I had already tried that. Din't work

The below details might help us.
show parameter undo
select * from dba_tablespaces where tablespace_name='UNDOTBS1';
select * from dba_data_files where tablespace_name='UNDOTBS1';
SELECT STATUS,RELATIVE_FNO,TABLESPACE_NAME,COUNT(*) FROM DBA_ROLLBACK_SEGS GROUP BY STATUS,RELATIVE_FNO,TABLESPACE_NAME;

Make sure the undo tablespace is set to undotbs2
alter system set undo_retention=50 scope=both;
--make sure the undotbs1 is online.
Do restart the database two times.(Normal)
try drop now.
If not post the result for the commands in the previous post.
Increase the retention later.

I already tried setting undo_retention=1 and restarting the DB. But doesnot work.



SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- -------------
undo_management string AUTO
undo_retention integer 900
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS1




SQL> select * from dba_tablespaces where tablespace_name='UNDOTBS';

TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
------------------------------ ---------- -------------- ----------- -----------
MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING EXTENT_MAN
----------- ------------ ---------- --------- --------- --------- ----------
ALLOCATIO PLU SEGMEN
--------- --- ------
UNDOTBS 4096 65536 1
2147483645 65536 ONLINE UNDO LOGGING LOCAL
SYSTEM NO MANUAL



SQL> select * from dba_data_files where tablespace_name='UNDOTBS';

FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
E:\ORACLE\ORADATA\MYDB\UNDOTBS01.DBF
2 UNDOTBS 6485442560 1583360 AVAILABLE
2 YES 8388608000 2048000 1280 6485377024 1583344

D:\UNDOTBS\UNDOTBS02.DBF
13 UNDOTBS 1048576000 256000 AVAILABLE
13 NO 0 0 0 1048510464 255984

FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------

D:\UNDOTBS\UNDOTBS03.DBF
14 UNDOTBS 1048576000 256000 AVAILABLE
14 NO 0 0 0 1048510464 255984

D:\UNDOTBS\UNDOTBS04.DBF
15 UNDOTBS 1048576000 256000 AVAILABLE

FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
15 NO 0 0 0 1048510464 255984




SQL> SELECT STATUS,RELATIVE_FNO,TABLESPACE_NAME,COUNT(*) FROM DBA_ROLLBACK_SEGS GROUP BY STATUS,RELATIVE_FNO,TABLESPACE_NAME;

STATUS RELATIVE_FNO TABLESPACE_NAME COUNT(*)
---------------- ------------ ------------------------------ ----------
OFFLINE 2 UNDOTBS 4
ONLINE 1 SYSTEM 1
ONLINE 16 UNDOTBS1 10
PARTLY AVAILABLE 2 UNDOTBS 1




If you notice in the last query one rollback segment is partly available, which
is not allowing to drop that tablespace. So I tried this:


SQL> alter session set undo_suppress_errors=TRUE;
Session altered.

SQL> alter rollback segment "_SYSSMU6$" offline;
Rollback segment altered.

Then tried dropping the tablespace; But in vain.

What is your undo tablespace now?
You have not changed your undo tablespace as suggested before by one Pando and others.
You are still using UNDOTBS1 as your undo tablespace.

You have not executed the queries correctly.
I requested information for UNDOTBS1 but you have executed for UNDOTBS

Make the UNDOTBS2 or any other newly created tablespace as
current undo tablespace and make your SPFILE also recognise by using scope=both.

Revert with details.

fine, ill stay away. I know the answer to your problem, but you are obviously so intelligient and knowledgable you can figure it out yourself

No no. Its a confusion. See - My original undo tablespace was UNDOTBS.
And the one i created newly is - UNDOTBS1. Since I need to drop UNDOTBS (not UNDOTBS1) , I executed your queries for UNDOTBS. If you have doubt, following is what I did and documented as per Tim and Pando's advice:



Step 1:
CREATE UNDO
TABLESPACE "UNDOTBS1"
DATAFILE 'D:\UNDOTBS\UNDOTBS05.DBF' SIZE 200M
REUSE AUTOEXTEND
ON NEXT 5120K MAXSIZE 16383M

Check in the Enterprise manager consol if the tablespace is created.

Step 2: Fire a COMMIT query to ensure that all transections are commited.

Step 3: Tell the system to use the new tablespace
ALTER SYSTEM SET undo_tablespace=UNDOTBS1;

Step 4: Restart the DB. Just for safty.

Step 5: Drop the table old space
DROP TABLESPACE UNDOTBS INCLUDING CONTENTS AND DATAFILES;

Step 6: Restart the DB. Check if it opens properly. Fire some DDL query.

Step 7: Delete the old datafiles from the filesystem. If not sure about the filename check from the view dba_data_files.

Step 8: Restart the DB.

I got stucked in step 5

I completely disagree with steps 4, 6 and 8. There is no reason to restart the db when switching undo tablespaces.

If the old one doesn't free up (drop), just give it some time. Don't panic. You may need to wait for it to age out the data (undo_retention seconds).

Step 7 - I thought 9i deleted the os file too? humm...

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.