Undo tablespace

[jflack]

Hi,
Our undo table space UNDOTBS01.DBF ahs recently jumped to 2.6 GB of data after the machine crashed repeatedly. Is there any way of cleaning up what is held in the file or should I be leaving it alone?
I do need the space its taking up as the disk is now almost full (56k free) and Oracle is running extremely slowly.
Thanks.

[davey23uk]

Have you tried resizing it down?

If you cant do that.

1 set undo_management = manual in spfile / init.ora file
2 restart database
3 drop tablespace undo_tbs including contents and datafiles;
4 create a new tablespace with the correct size (with the same as that which you dropped)
5 put undo_management back to auto
6 restart

[KenEwald]

I haven't had any success shrinking the datafile(s).

Here's what I do (no restart required):

create undo tablespace undotbs02 datafile 'yourfilenamegoeshere' autoextend on next 100m maxsize 6000M;

alter system set undo_tablespace='UNDOTBS02';

drop tablespace undotbs01 including contents and datafiles;

You may not be able to drop this until existing sessions close and free up the offline pending segments.

Check your undo_retention and make sure it's set right. Set it too low and you'll get ora-1555 snapshot too old errors. Too high and you'll end up with a muy largo unto tbs again.

Best of luck.

[DaPi]

Originally posted by KenEwald
. . . autoextend on next 100m maxsize 6000M;

If 2.6GB is "too big" then the maxsize presumably should be set lower - of course it depends on what undo retention is realistically required.

[KenEwald]

Good observation... I agree 100% with the correction.

Here's what I do

If he isn't happy with 2.6 gigs, he definately wouldn't want it growing past 6gb. he he...

[davey23uk]

i would of suggested that, but he said 56k of space left, no room to create a new one first

[jflack]

Lads,
Thanks for those comments they all helped.
I ended up buying a new drive, it became the easiest option after talking to the users.
Cheers,
Jonathan

[gandolf989]

Originally posted by jflack
Hi,
Our undo table space UNDOTBS01.DBF ahs recently jumped to 2.6 GB of data after the machine crashed repeatedly. Is there any way of cleaning up what is held in the file or should I be leaving it alone?
I do need the space its taking up as the disk is now almost full (56k free) and Oracle is running extremely slowly.
Thanks.

Have you tried a new disk array? Is the database crashing when a volumne runs out of space? You can also try to look at your data and see if it is being stored efficiently. DBMS_SPACE is a good place to look.

[KenEwald]

easiest option after talking to the users

Database Administration by POLLING THE MASSES .. interesting methodology .. hey, works for politicians (he he)

[DaPi]

Originally posted by KenEwald
Database Administration by POLLING THE MASSES .. interesting methodology .. hey, works for politicians (he he)

Not so stupid . . . if you have to balance cost of down-time and/or DBA overtime against the cost of disk, then those who pay the bill should have say.