Solved

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

Posted on 2004-09-13
3
631 Views
Last Modified: 2010-05-18
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
Comment
Question by:trivedisushil
3 Comments
 
LVL 48

Accepted Solution

by:
schwertner earned 50 total points
ID: 12042380
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question