Solved

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

Posted on 2004-09-13
3
610 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 47

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

758 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now