Home » RDBMS Server » Server Administration » RBS tablespace is not releasing space (9.2.0.8.0)  () 1 Vote
RBS tablespace is not releasing space [message #564683] Mon, 27 August 2012 10:00 Go to next message
sathik123
Messages: 37
Registered: December 2010
Location: chennai
Member
Hi,

In My database rollback segment space is not releasing space even though, there is no transaction is using RBS. RBS tablespace size is around 70GB. Can any one please help me in this regards. Unfortunately still our environment is running in 9i due to application code

SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string MANUAL
undo_retention integer 1800
undo_suppress_errors boolean FALSE
undo_tablespace


Regards
iamsathk
Re: RBS tablespace is not releasing space [message #564684 is a reply to message #564683] Mon, 27 August 2012 10:10 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

It is normal & expected behavior to "not release space".
Why did you make RBS so large to start with?
Re: RBS tablespace is not releasing space [message #564685 is a reply to message #564683] Mon, 27 August 2012 10:11 Go to previous messageGo to next message
John Watson
Messages: 4515
Registered: January 2010
Location: Global Village
Senior Member
I have vague memories of setting an OPTIMAL size for rollback segments, and using a command something like ALTER ROLLBACK SEGMENT....SHRINK TO OPTIMAL to reduce their size. But I see no reason for you not to use automatic undo management with 9i. OK, there were a few bugs - but even so, it was far superior to manual rollback segments.
Re: RBS tablespace is not releasing space [message #564686 is a reply to message #564684] Mon, 27 August 2012 10:13 Go to previous messageGo to next message
sathik123
Messages: 37
Registered: December 2010
Location: chennai
Member
one of datafile in RBS tablespace is in autoextend on Mode. I thing it is keep growing. is it because of autoextend on it is not releasing space ?
Re: RBS tablespace is not releasing space [message #564687 is a reply to message #564686] Mon, 27 August 2012 10:17 Go to previous messageGo to next message
sathik123
Messages: 37
Registered: December 2010
Location: chennai
Member
I read in one of the forum, If RBS used then undo_management will be MANUAL, is it ?
Re: RBS tablespace is not releasing space [message #564688 is a reply to message #564687] Mon, 27 August 2012 10:24 Go to previous messageGo to next message
John Watson
Messages: 4515
Registered: January 2010
Location: Global Village
Senior Member
So don't use rollback segments. Join the twentyfirst century!
Re: RBS tablespace is not releasing space [message #564690 is a reply to message #564688] Mon, 27 August 2012 10:27 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
>one of datafile in RBS tablespace is in autoextend on Mode.
>I thing it is keep growing. is it because of autoextend on it is not releasing space ?

Yes & it is doing exactly configured.
Re: RBS tablespace is not releasing space [message #564691 is a reply to message #564690] Mon, 27 August 2012 10:32 Go to previous messageGo to next message
sathik123
Messages: 37
Registered: December 2010
Location: chennai
Member
Can you please help how to reclaim the space, Becuase there is no transaction is using RBS. But tablespace usage is 100%(70GB)
Re: RBS tablespace is not releasing space [message #564692 is a reply to message #564691] Mon, 27 August 2012 10:34 Go to previous messageGo to next message
John Watson
Messages: 4515
Registered: January 2010
Location: Global Village
Senior Member
I've already told you the command. All you have to do is check the syntax. I'm noy looking it up for you.
Re: RBS tablespace is not releasing space [message #564695 is a reply to message #564691] Mon, 27 August 2012 10:43 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Create an undo tablespace, restart the database usin it, drop your rollback segments tablespace, enlarge the undo tablespace as needed.

Regards
Michel
Re: RBS tablespace is not releasing space [message #564697 is a reply to message #564695] Mon, 27 August 2012 10:52 Go to previous messageGo to next message
sathik123
Messages: 37
Registered: December 2010
Location: chennai
Member
Please let me know how to set optimal value for Rollback segment, and how to get the optimal value set for rollback segment. There is no value set for rollback_segments parameter, last week we reboot the DB. and today when i check the database.there is seven RBS segments are in online out of 48. My doubt is how it became online automatically, why other RBS segment are still in offline..
Re: RBS tablespace is not releasing space [message #564701 is a reply to message #564697] Mon, 27 August 2012 11:19 Go to previous messageGo to next message
sathik123
Messages: 37
Registered: December 2010
Location: chennai
Member
any one can help me on this..
Re: RBS tablespace is not releasing space [message #564703 is a reply to message #564701] Mon, 27 August 2012 12:04 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I have said what I have to say.
I will not give advice on obsolete feature.
Use the correct one. Full stop.

Regards
Michel
Re: RBS tablespace is not releasing space [message #564711 is a reply to message #564703] Mon, 27 August 2012 15:14 Go to previous messageGo to next message
Flyby
Messages: 144
Registered: March 2011
Location: Belgium
Senior Member
undo tablespace is the way to go as Michel emphasized.
Re: RBS tablespace is not releasing space [message #564958 is a reply to message #564711] Wed, 29 August 2012 14:04 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
iamsathk,

I highly recommend using undo segments over rollback segments. I used to redefine the rollback segments with the following script that builds the alter commands for you. Maybe after you redefine the rollback segments into another tablespace you will be able to resize the rollback tablespace down to size. Then implement undo segments.

set lines 150
set pages 0
set feedback off
spool rbsprod.lst;
select 'alter rollback segment '||segment_name||' offline;'
from dba_segments where (tablespace_name like '%ROLLBACK%'
or tablespace_name like '%RBS%') and bytes/1024/1024>.0300 and segment_name not like '%BIG%' order by segment_name;
select 'drop rollback segment '||segment_name||';'
from dba_segments where (tablespace_name like '%ROLLBACK%'
or tablespace_name like '%RBS%') and bytes/1024/1024>.0300 and segment_name not like '%BIG%'  order by segment_name;
select 'create rollback segment '||segment_name||
' storage (initial '||initial_extent||' next '||next_extent||' maxextents '||max_extents||' minextents '
||min_extents||') tablespace '||tablespace_name||';'
from dba_segments where (tablespace_name like '%ROLLBACK%'
or tablespace_name like '%RBS%') and bytes/1024/1024>.0300 and segment_name not like '%BIG%'  order by segment_name;
select 'alter rollback segment '||segment_name||' online;'
from dba_segments where (tablespace_name like '%ROLLBACK%'
or tablespace_name like '%RBS%') and bytes/1024/1024>.0300 and segment_name not like '%BIG%'  order by segment_name;
spool off;
set pages 50
set wrap off
set lines 100
set feedback on
Re: RBS tablespace is not releasing space [message #566115 is a reply to message #564958] Tue, 11 September 2012 07:17 Go to previous message
sathik123
Messages: 37
Registered: December 2010
Location: chennai
Member
rollback_segments parameter was not set properly.Becase of this rollback segment did not come in online..Thanks for help.it is very useful info i got...Thanks for the help..
Previous Topic: TNS errors in Listener Log File
Next Topic: client libraries are inaccessible
Goto Forum:
  


Current Time: Fri Aug 29 17:40:20 CDT 2014

Total time taken to generate the page: 0.09866 seconds