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?
why do you want to resize it
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 ;
Click Here to Expand Forum to Full Width