How i Can to delete or to redude UNDOTBS01.DBF

[hiramindiaz]

Hello:
I have a problem with this file because his size is almost 16Gb and my server is full.
I need the way o the steps for delete o reduce this file, in my database i have only 25000 record and somebody tell me that this file is for rollback.
Thanks for you time and i wait for your answer.

 

[KenCunningham]

Hi. There is a good thread here:

http://www.experts-exchange.com/Databases/Oracle/Q_20942461.html

Hope this helps.

I want to be good, is that not enough?

 

[SantaMufasa]

Ken,

For those of us cheapskates that prefer not to pay to have an "Expert Sex Change" <grin>, could you please post a profile of the solution that appears at that link?

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[KenCunningham]

Yes, Dave, I've seen that before! Incidentally, I didn't pay anything at all for the proffered solution:

Whatever, the accepted response is:

Failing the above for whatever reason, you can:

1. create undo tablespace undotbl01_new datafile '/path/to/newdatafile' size 100m autoextend on; -- new undo

2. alter system set undo_tablespace = undotbl01_new scope=both;

3. drop tablespace undotbs01; -- drop old undo

I have modified the size parameter in the create and additionally added autoextend on as this I think is useful for undo.

For completeness and to give credit where it's due, I should add that the accepted solution was contributed by 'vc01778'.





I want to be good, is that not enough?

 

[SantaMufasa]

Yes, the above steps do most of what is necessary to get rid of an outrageously sized UNDO tablespace file. But please add a Step 4:

4) At the operating-system level, issue a command to erase the old (outrageously sized) undo tablespace file. Note: some operating systems will not allow erasure of the now-extraneous file until you bounce the database instance.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[KenCunningham]

Agreed Dave. Too often one tends to think of these things as 'goes without question'.

I want to be good, is that not enough?