Solved

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

Posted on 2004-09-13
3
638 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
make null the repeated levels 2 51
oracle date format checking 7 59
format dd/mm/yyyy parameter 16 60
oracle query 3 35
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

710 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