File Size of Undo Tablespace is too large!

[tmcneil]

All,

My undo tablespace, UNDOTBS1.DBF, is over 3.5 GBs. I'm running out of space on my hard drive, down to 3.5 GBS. How can I reduce the size or delete this file and recreate so that it will never get this large?

Todd

 

[SantaMufasa]

Todd,

You have encountered that "A-#1 Reason" that I dispise Automatic UNDO segments/tablespaces. I don't use them. I would do the following:

1) Set your instance parameter, "undo_management=MANUAL", and re-boot,
2) Create a standard tablespace (e.g., "RBS"),
3) "CREATE PUBLIC ROLLBACK SEGMENT..." for as many rollback segments as you wish,
4) "DROP TABLESPACE <undo ts name> INCLUDING CONTENTS;",
5) From the o/s, erase the now-unnecessary files that were supporting the "automatic UNDO" tablespace.

I'm sure that others have differing opinions, but this is what I would do.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[slaing]

Todd -

If your Temp tablespace is 3.5 Gb then your queries have needed to do some pretty big disk sorts.

There could be a variety of reasons why that's the case
- is your system big and 3.5Gb is a reasonable TEMP ts size?
- are there queries with poor joins in them ?
- are you letting users loose with reporting tools such as BO, Crystal or Actuate ?

(I don't actually want answers to those questions, I'm just suggesting that they're questions you should be asking yourself !! )

Anyway, if you reckon a 3.5Gb Temp tablespace is outrageous for your system, then Santa's suggestion will limit the size and you'll soon find out who's running the dodgy queries when they phone up with 'failed to extend' errors.

Personally, I prefer Automatic UNDO since
- it is the Oracle default from 9 onwards,
- it's less hassle
(we don't always have other DBAs to hand to extend it manually)
- occasionally users do have to run big one-off queries
- if it gets really big, it's a 1-minute job to shrink it

1) log on to OEM
2) create a new tablespace TEMP2, as type 'temporary', and checking on 'set as default temporary tablespace', say about 200Mb
3) make sure the datafile is set to autoextend
4) as soon as queries using the original TEMP have finished, drop TEMP and delete the OS file(s) for it.

The best approach will depend on your own circumstances.

Best of Luck

Steve

 

[stefanhei]

Hi Todd,

As Dave said others may have different opinions.
I'm quite happy with undo segments. Your main problem is that for your undo tablespace autoextend is enabled.

You may resize the datafile and turn autoextend off like this:

ALTER DATABASE DATAFILE '/path/to/file/UNDOTBS1.DBF' RESIZE 2GB;
ALTER DATABASE DATAFILE '/path/to/file/UNDOTBS1.DBF' AUTOEXTEND OFF;

Of course this should be done when there is no other activity onthe system.

Stefan

 

[slaing]

Folks,

Please ignore my post here !!

:~/ :~/ :~/

I was thinking about a totally seperate TEMP issue at the time and got it into my head that it was TEMP and not UNDO that was the issue here.


I'll go off and make myself an extra-strong coffee.....