Re: UNDOTBS01.DBF file size

Jim wrote:
> >An area no one has yet considered is the value for the undo_retention
> >parameter; this directly affects the size of the UNDO tablespace.
> >Possibly this is set larger than necessary; a quick chat with the DBA
> >should put this issue to rest as he/she can explain the rationale
> >behind the undo tablespace size.
> >
>
> the is no (gulp) dba here. where can i find the undo-retention
> parameter in Oracle10? which file? i can't find init.ora file
> anywhere on the system.

http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10739/create.htm#sthref391

>
> i also ran dbmsuadv.sql file hoping it would give me some insights.
> thanks.

Disk space is cheap. If you needed that much at one point, you may need it again. Of course, the exception is not allowing the undo to grow excessively due to something stupid like an accidental cartesian join. You can put a maximum on how large the file will be allowed to grow, along with autoextend (when the file is much smaller).

To fix your problem:
When there are no transactions active:
Create new undo tablespace with smaller size. Set new tablespace as undo_tablespace.
Drop the old tablespace.

jg

--
@home.com is bogus.
http://gprime.net/images/circleillusion/
"Stare at this dot for 11 hours straight.  When you look away, you will
see the dot everywhere!  Amazing, but true!" - Mad magazine, circa
1965.