topic Re: reducing the rollback segment from oracle in Operating System - HP-UX

reducing the rollback segment from oracle

Danny Pogi — Tue, 12 Nov 2002 09:41:52 GMT

hi guys,

does the rollback segment in oracle clears itself after commiting a transaction?

because i have seen from one of our boxes that the tablespace allocated to RBS is quite big and i want to deallocate some of this to other tablespace. is it safe to remove reduce rbs?

(hp-ux version is 10.20 oracle 8.0.4)

thanks,

danny

Re: reducing the rollback segment from oracle

Ian Lochray — Tue, 12 Nov 2002 10:03:34 GMT

Oracle will not immediately shrink a rollback segment when a transaction is committed. The time taken to automatically shrink depends on the setting of the OPTIMAL storage clause and whether other transactions are reading the data held in the rollback segment.
You can manually shrink a rollback segment by issuing a command like -
ALTER ROLLBACK SEGMENT rbs1 SHRINK TO 100K;

Re: reducing the rollback segment from oracle

T G Manikandan — Tue, 12 Nov 2002 10:38:24 GMT

alter rollback segment xyz shrink to xxx M;

Re: reducing the rollback segment from oracle

Christian Gebhardt — Tue, 12 Nov 2002 11:13:46 GMT

Hi
extract from Oracle-PDF-Doku

Chris

Re: reducing the rollback segment from oracle

Ravi_8 — Tue, 12 Nov 2002 11:53:39 GMT

Hi, Danny

svrmgrl> alter rollback segment shrink to

K -- size in KB
M -- size in MB

Re: reducing the rollback segment from oracle

Jean-Luc Oudart — Tue, 12 Nov 2002 12:49:25 GMT

fiel attached document from Metalink.
I assume you're an Oracle customer and therefore have access (once registered) to this web site.
You may find other information regarding your question

Jean-Luc

Re: reducing the rollback segment from oracle

Andreas D. Skjervold — Tue, 12 Nov 2002 12:51:29 GMT

Hi

Be careful not to reduce the RBS tablespace too much after reducing the rollback segments as this might result in errors when the rollback segment expands due to large transactions.

also you should not set the OPTIMAL size too low as Oracle then will spend more time allocation and deallocationg space for the rollback segments.

Andreas

Re: reducing the rollback segment from oracle

Alexander M. Ermes — Tue, 12 Nov 2002 13:52:17 GMT

Hi there.
Set the OPTIMAL parameter for the rollback segements. That will help, but it might take a while. You can also use the Oracle Enterprise Manager to shrink the rollback segments
( Tools -> Database applications -> Storage Manager -> ORACLE_SID -> rollback segments ).

Reducing the RBS tablespace might be the reason for future problems.
If you run large amounts of data manipulating
Oracle statements, you will need the actual space. We have this problems, because we do a database refresh from an external database every day. Our database is not too big, but the amount of data coming over requires some big RBS tablespace. If you go for automatic committing, do not forget, that you might have trouble, if your job breaks off half way through.
Just my 2 ct.
Rgds
Alexander M. Ermes