resizing undotable space datafile
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: resizing undotable space datafile

  1. #1
    Join Date
    Jul 2006
    Posts
    96

    resizing undotable space datafile

    hi friends, my undotablespace datafile size is increased much.
    it is 2.7 GB. I was suggested to decrease the size up to 400 or
    500 mb etc..
    how to resize this datafile. If I use alter database data file....
    resize.. it is give 'it is beyond the used space' error.
    Can u give best solution for this.
    does this size effects performance??
    should i resize it compulsary?

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    why do you want to resize it

  3. #3
    Join Date
    Sep 2001
    Location
    Jurong East/Singapore
    Posts
    70
    Hi do check this ...hth
    1. Verify undo_tablespace (first undo tablespace describe below) and undo_retention (Time in second of the retention default 10800 (3hours) ) parameters in init.ora file.


    2. Create a second undo tablespace

    CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE ‘chemin/undotbs2.dbf’ SIZE 50 M AUTOEXTEND OFF ;


    3. Set the second undo tablespace as default undo

    ALTER SYSTEM SET undo_tablespace = UNDOTBS2 ;


    4. Drop the first undo tablespace

    DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES ;


    5. Create the first undo tablespace and limit the size to 1Go

    CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE ‘chemin/undotbs2.dbf’ SIZE 500 M AUTOEXTEND ON NEXT 5 M MAXSIZE 1000M ;


    6. Set the first undo tablespace as default undo

    ALTER SYSTEM SET undo_tablespace = UNDOTBS1 ;


    7. Drop the second undo tablespace

    DROP TABLESPACE undotbs2 INCLUDING CONTENTS AND DATAFILES ;



    regds,
    peri sekar

Posting Permissions

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



Click Here to Expand Forum to Full Width