[RastogiKamesh]

Hi,
I am using Oracle9i rel-2 on SUN Solaris 8.0 (SPARC). I have created my database using CREATE DATABASE command. I have some problem with UNDO tablespace management.

For UNDO tablespace, i have set the relevant parameters in my INIT.ORA file like this:
undo_managerment = AUTO
undo_retention = 10800
undo_tablespace = undo

Now, Creating my Database like this....(The database name is 'abc' here). Pls. check the clause for UNDO Tablespace in my command below:

CREATE DATABASE abc
CONTROLFILES REUSE
...........
DATAFILE ................................................(F or SYSTEM Tablespace)
DEFAULT TEMPORARY TABLESPACE..........(For Temporary Tablespace)
UNDO TABLESPACE undo DATAFILE '/mnt41/abc_undo1.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 512K MAXSIZE UNLIMITED
LOGFILE........................................... ........(Different redo log files in groups).

The Initial size of UNDO TS was 200MB. Now, i see my UNDO Tablespace size has grown like anything. It is approx. 1.9GB. I believe it is because of Autoextend ON clause. But, as i know that the UNDO tablespace should be truncated automatically. Basically, i may like to know two points please.

1. How & what might be the reason UNDO tablespace size has grown upto this point...When need of more space might have come.?
2. If i change the clause to Autoextend OFF then will it create any problem during my database operation. Is it advisable to do it or not..??

Kindly help pls. Thanks a lot in advance pls.

Regards,

[AlanP]

If your undo tablespace has grown too much I would suggest reducing the undo_retention time as it has to maintain rollback info for that period of time for flashback queries. The other thing to do would be to try and find out what is using so much rollback, could be large index rebuilds or large reports which might be rewritten to use less rollback or better scheduled at a quiet time.

If you just reduce the size of the undo tablespace and there is insufficient space then I suspect you'll probably get ora-1555 or other related errors.

Alan

[RastogiKamesh]

Hi Alan,
Thanks for ur suggestion. I hv already checked my Undo retention value. But. after so much period of time, it should delete the info from undo tablespace...Is not it..?? Does it do automatically or we have to do something.? Anyhow, i will once again see & try to modify my undo retention value....(i would make it a bit lower or even some default value if there).

Yes...Even checking report etc. might be a good way to find out how this much rollback information is being generated.

Thanks for ur suggestion & tips.

Regards,

[AlanP]

You are right in that it does delete old data from the undo tablespace (or rather it will overwrite it) but Oracle never shrinks the actual datafile (unless you do a alter database to resize the datafile). So the best thing to do is to reduce the undo retention time to say 2 hours. Or alternatively if you find that there are big batch jobs running which generate this undo then make sure they dont run at the same time, make them run one after the other.

Alan

[RastogiKamesh]

Thanks Alan. I will do that.
Bye,

Regards.