-
undo tablespace growing big
My undo tablespace was created as part of thi screate database statement:
CREATE DATABASE orsp
MAXINSTANCES 1
MAXLOGHISTORY 1
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
DATAFILE '&vDATAFILES\system01.dbf' SIZE 300M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
UNDO TABLESPACE "UNDOTBS" DATAFILE '&vDATAFILES\undotbs01.dbf' SIZE 800M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
CHARACTER SET WE8MSWIN1252
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('&vFIRSTREDO\redo01.log', '&vSECONDREDO\redo01B.log') SIZE 10M,
GROUP 2 ('&vFIRSTREDO\redo02.log', '&vSECONDREDO\redo02B.log') SIZE 10M,
GROUP 3 ('&vFIRSTREDO\redo03.log', '&vSECONDREDO\redo03B.log') SIZE 10M,
GROUP 4 ('&vFIRSTREDO\redo04.log', '&vSECONDREDO\redo04B.log') SIZE 10M;
I noticed that the size of the undo file grew in two months to 8.5G. Is it normal?
Thanks,
-
some people are doing some long running queries making it grow that big
-
I know it can grow, my question is what can I do to shrink it once in a while? Also, undo tablespace being that big affects the overall performance of the system or not? Is it worth having multiple files for this tablespace?
-
One cause may be having undo_retention too big.
-
DO NOT SET AUTO EXTEND ON.
Tamil
-
Originally posted by tamilselvan
DO NOT SET AUTO EXTEND ON.
Tamil
and get unable to extend tablespace error, much better!
-
alter rollback segment rbs1 shrink;
-
Originally posted by kris123
alter rollback segment rbs1 shrink;
not with undo management in 9i im afraid
-
Just as an add on
IMO; the undo and temp size should go hand in hand. Also, if you have them set for auto extend you're setting yourself up for failure. Lets put it this way if you have a process out there that sucks up more than 2 gig of temp space (or undo) you should have your gun in hand and be on your way down to developers cube who wrote the process / code.
Oracle it's not just a database it's a lifestyle!
--------------
BTW....You need to get a girlfriend who's last name isn't .jpg
-
but if this in a a production system your useers do not want their long running queries stopping with an error.
If its not causing a problem with disk space or whatever, let it grow monitor it and fix things that cause problems
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|