Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2003
    Location
    Jagdishpur
    Posts
    146

    Unanswered: UNDO Tablespace problem in ORA9i Database

    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,
    - KR

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    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

  3. #3
    Join Date
    Apr 2003
    Location
    Jagdishpur
    Posts
    146
    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,
    - KR

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    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

  5. #5
    Join Date
    Apr 2003
    Location
    Jagdishpur
    Posts
    146
    Thanks Alan. I will do that.
    Bye,

    Regards.
    - KR

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •