[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 665
  • Last Modified:

RBS01.dbf .... 3.0 gb & RBS02.dbf 7.0 gb.... RBS Datafile Increasing

Dear Gurus,

(ORacle 8.1.5)

I  had few days ago faced problem of Rollbacksegment  RBS Error....Unable to extend...

I happened to add RBS02.DBF.

Now the status is the file size is rapidly increasing.

It is by now 7 GB when created of 2 gb.

MY RBS01.dbf is also of 3 GB

I happen to add RBS02.DBF only because inspite of Increasing the Rollback Segments RBS01...RBS24 sizes by another 32636 K ...

I happen not to solve my RBS error of Unable to extend RBS 24 by ...
all my RBS are Online...and by adding RBS02.dbf my Problem has got resolved.

However  I want to trim this files......to lat's say upto 2 GB both...


I have 1.5 gb of free space on database and is cause of Conecern now...

Assit me for the same.

I am online on MSN as  sushil_t48@hotmail.com

0
trivedisushil
Asked:
trivedisushil
1 Solution
 
schwertnerCommented:
This is for 9i. So some statements will not work, like  ... SET RETENTION ....
It will be good to make cold backup and full export before this.


Undo Tablespace File has grown to 2.2 Gig. Can it be reduced?



--reduce the retnetention to 5 minutes


ALTER SYSTEM SET UNDO_RETENTION = 5;

--create a new ts for a temp undo


CREATE UNDO TABLESPACE undotbs_02
DATAFILE '/u02/ora_data/oradata/dbeta/undo02.dbf' SIZE 2M REUSE AUTOEXTEND ON;


--set the undo ts to new ts
ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_02;


--wait for original undo ts to be freed up (no online segments).

select segment_name,status,tablespace_name from dba_rollback_segs
where status='ONLINE' and tablespace_name='UNDOTBS1';


--offline old ts

alter tablespace UNDOTBS1 offline;


-- drop old ts

drop tablespace UNDOTBS1 including contents and datafiles;

--recreate new default ts

create undo tablespace UNDOTBS1
datafile '/u02/ora_data/oradata/dbeta/undotbs01.dbf' size 200m reuse autoextend on next 10m;

--repoint undo to default again
ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS1;


--wait for temp undo ts to be freed up (no online segments).
 
select segment_name,status,tablespace_name from dba_rollback_segs
where status='ONLINE' and tablespace_name='UNDOTBS_02';

--offline the temp undo tablespace

alter tablespace undotbs_02 offline;

--drop temp undo

drop tablespace undotbs_02 including contents and datafiles;

--reset retnetion to normal default (whatever you use in init.ora)

ALTER SYSTEM SET UNDO_RETENTION = 10800;
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now