Undo tablespace size

[fordikon]

hello:

The undo tablespace size is more than 5GB and my disk space is filling up fast.

I tried resizing the datafile and it fired with the ORA-03297.
and i guess it affects the performance.

I tried recreating new undo tablespace and assigning that and tried resizing old one. i got the same ORA-03297 error message.

how do we do it??.

[abhaysk]

did u create undo TS with datafile size UNLIMITED?

[fordikon]

yes .. it is given as UNLIMITED and it has been removed now.
how do we shrink it?.

[mzahid]

You cannot shirk segments in undo tablespace. Fastest and safest way to accomplish this task is to create new undo tablespace with datafile size limit set as required.

Switch database to use new undo tablespace. Drop old undo tablespace if ok to do.

See Oracle9i DBA guide for details on managing undo tablespace in 9i.

Hope this helps.

[adewri]

http://asktom.oracle.com/pls/ask/f?p...6894817116500,

[fordikon]

i have tried creating new UNDO tablespace (with NO autoextend) and deleted the old one. I tried to do import , during import it fired an error message saying "unable to extend....", then i enabled "EXTEND" of undo datafile and again it started growing..

how do we stop this undo tablespace growing ???.

[mzahid]

Try import using commit=y option. This way all transaction get commited while import is running.

[fordikon]

thanks.. there it gives error "enable to extend" for UNDO and TEMP tablespace even where there is ay insert/update happens .. how do we solve this problem of UNDO and TEMP tablespace growing???.