Deleting UNDOTBS

[TomNJerry]

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**

[pando]

create a new undo

then modify the default undo to the new one

alter system set undo_tablespace = 'UNDOTBS02';

drop the old one

[TimHall]

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...

[TimHall]

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...

[TomNJerry]

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.

[TomNJerry]

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


What to do ?

[waitecj]

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.

[TomNJerry]

Hi waitecj,

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

[davey23uk]

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

[Mr.Hanky]

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?