-
Shrink UNDO tablespace - Oracle 10.2 RAC
I am totally new to oracle (just joined as a junior trainee DBA). I am slightly intimidated will the documentations I read everyday. Any kind of explanation will be highly appreciated.
I have a couple of instances each on 2 servers. Two 2 node RAC clusters on 2 servers. 10.2.0.2.0 on RHEL 3.
In database1:
TABLESPACE_NAME GB
---------- --------------------
UNDOTBS1 4.90625
UNDOTBS2 15.9136736
Instance1:
NAME TYPE VALUE
--------------------------- ----------- ---------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
Instance2:
NAME TYPE VALUE
------------------------- ----------- ---------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
In database2:
TABLESPACE_NAME GB
---------- ---------------------
UNDOTBS1 15.2107041
UNDOTBS2 11.0957684
Instance1:
NAME TYPE VALUE
----------------------------- --------------- ---------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
Instance2:
NAME TYPE VALUE
--------------------------- ------------- -----------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
From what I read last few weeks, in oracle 10g, if we use automatic undo management and undo retention, oracle will automatically allocate and deallocate segments when required and we do not need to bother about it growing once we set a proper size. But later read that if we do not set the MAX_FILE_SIZE (i think this is what it is), it will keep growing and allocate segments all over the file system and occupy space.
these are my questions:
1. When undo TS is 15GB, does it mean that these 15GB is taken from the disk space and sitting here "useless".
2. Do I have to shrink it (to re-claim space)?
3. What are the steps to do it? I read in one form thread that I can create a new tablespace and change that to be the undo tablespace and drop the old one.
4. what other information about my DB or server should I provide to complete my question?
Can someone kindly explain the procedure? Reference to a documentation will also help. But if someone can list the steps, that will be really kind.
-
-
Thanks much
I thought I did a lot of online research but sure did miss this post. But I think, I have to do some more reading to get a real grasp of undo_retention and undo generation etc and how such things go together.
-
Does anyone know if something similar to the tip on the Oracle forum might work on the Temp tablespace? Both our Temp and Undo are growing too large and I'd like to reduce it as well.
Thanks,
Rob
-
you can drop and add tempfiles
-
and ofcourse while adding file do set a max file size.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
wouldnt it be better to work out why they are getting bigger?
-
I agree, and I think I know why it got big, and I know how to prevent it from getting big in the future. But it is already big and I need to know how to fix that.
-
Undo tablespace would grow and grow if you want it. What restricts its growth is
- Undo_retention
- max_size of datafile
Now Oracle will shrink it when it comes under space pressure and when you retention time has been exceeded.
if it has space it would continue to grow ignoring retention time.
Looking for the greatest evil in the world? Look in the mirror.
-
oracle will never shrink the undo tablespace on its own
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
|