Oracle segments only appearing in a few datafiles that are part of the tablespace RBS

Question experts;

I have an Oracle rollback tablespace, it comprises of about 20 rollback segments which are online, we  have created them accordingly after examining the application that it runs under and its transaction needs.

Now this is the problem, if I fire this statement in PL/SQL:

SQL> SELECT owner, s.status, segment_name, f.file_name, f.bytes/1024/1024 "Size
  2  MB"
  3  FROM   dba_rollback_segs s, dba_data_files f
  4  WHERE  s.file_id = f.file_id;

I get a dispersement of rollback segments across only 4 of the ten files that are defined for the RBS tablespace. In other words, why isnt ORacle dispersing the segments across more of the files available? To make the situation a bit more interesting, there is a file within the tablespace that does not appear in the above query that is nearly full? How can this be? The RBS tablespace is only used for rollback segments and nothing else.

Any ideas?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

If you are using Oracle 9i or above the simple answer is to abandon explicit rollback segments entirely as they are now essentially obsolete.  You can set up an undo tablespace and all your problems go away.  You aren't trying to load balance I/O are you?

If you are using a lower version, or want explicit rollback segment handling in 9i/10g for some reason, you can look at the XACTS column of V$ROLLSTAT.  Apparently rollback segments are allocated based upon the lowest number of active transactions.  If the XACTS column of V$ROLLSTAT for the unused segments is zero then they should be used.  If they are non-zero then this suggests that there are some unresolved transactions still using them (are you using distributed transactions?).  If all else fails drop the unused segments and recreate them.  Life is too short.

Extents in rollback segments are used in a circular fashion, so they cannot be "full" like table segemnts, for example.

Hope this helps
btuttAuthor Commented:
Yes thank you, but what about the file that is not identified by the query listed. Its size is 3GIG and I get an ORA-03297 "fie contains used data beyond the RESIZE value. What is this file full with data when it is not defined for any rollback segment within that tablespace and the tablespace has no other purpose? In other words, the following query:

SELECT distinct segment_name,segment_type
FROM   dba_extents
WHERE  tablespace_name='RBS'
AND    segment_type <> 'ROLLBACK';

returns no rows.

Additionally, you are right the v$rollstat view contains both 1 and 0 for ACCT. The only row that contains a value of 1 looks like the following:


   SHRINKS      WRAPS    EXTENDS  AVESHRINK  AVEACTIVE STATUS              CUREXT     CURBLK                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
---------- ---------- ---------- ---------- ---------- --------------- ---------- ----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
         3        383          0   75497472  906183203 ONLINE                  76        847                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            

Can you help me reduce the size (or delete) this file and the meaning of this output?

Thanks in advance,


Ok, you seem like a pretty switched on guy.  So this is either a really subtle problem,
or a problem that is so easy you can't see it.  I have suffered from the latter myself
many a time, so let me see if I can be a second "pair of eyes".  Forgive me if we go through the basics.


1) Can you send me the output of:

select file_name,file_id from dba_data_files where tablespace_name='RBS' ;

2) Tell me the file_id of your unused file.  Let me assume that it is 99 for our
purposes here.

3) send me the output of:

select segment_name from dba_rollback_segs where file_id=99 and tablespace_name='RBS' ;

Let us assume that there is one segement name and its name is 'FRED'

4) send me the output of:

select xacts from v$rollstat s,v$rollname r where r.usn=s.usn and name='FRED' ;


select status from v$transaction t,v$rollname r where xidusn=usn and name='FRED' ;

Let us see how far that takes us
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

btuttAuthor Commented:
OK, here you are:

select segment_name from dba_rollback_segs where  tablespace_name='RBS' ;

SQL> select file_name,file_id from dba_data_files where tablespace_name='RBS';





The file that I am talking about has an id of 4 so:

select segment_name from dba_rollback_segs where file_id=4 and tablespace_name='RBS' ;
And now this is the thing, this query contains no rows for file_id=4, the only file_id's that are present are

SO moving on to your next query:
Remember that the segment is not defined for this file anywhere but I will give you the whole query comprising of
select name, xacts from v$rollstat s,v$rollname r where r.usn=s.usn. That query returns:

NAME                                XACTS                                      
------------------------------ ----------                                      
SYSTEM                                  0                                      
R1                                      0                                      
R2                                      0                                      
R3                                      0                                      
R4                                      0                                      
R5                                      0                                      
R6                                      0                                      
R7                                      0                                      
R8                                      0                                      
R9                                      0                                      
R10                                     0                                      

NAME                                XACTS                                      
------------------------------ ----------                                      
R11                                     0                                      
R12                                     0                                      
R13                                     0                                      
R14                                     0                                      
R15                                     1                                      
R16                                     0                                      
R17                                     1                                      
R18                                     1                                      
R19                                     0                                      
R20                                     0                                      
R21                                     0                                      

22 rows selected.

In the same vane I will submit you the final query you requested namely:

select, status from v$transaction t,v$rollname r where xidusn=usn,
which yields:

NAME                           STATUS                                          
------------------------------ ----------------                                
R15                            ACTIVE                                          
R17                            ACTIVE                                          
R18                            ACTIVE

Kind regards,

Ok fine,

I have just realised that I do not know the command you are trying to run that is causing the ORA-03297 problems.  To get rid of this file you should type:


Then you should be able to delete this file from the OS.  I do not know a way of really dropping a datafile from a tablespace short of dropping the whole tablespace and starting again, but this should be a good workaround.

Once this is done you can then add a new datafile with a smaller size if you want.

Are you using a lower version of Oracle than 9i?  If not then I strongly advocate using an undo tablespace.  I note that all of your datafiles are in H:\ERK2 so there is no load balancing issue.  If you use an undo tablespace you never will never have to worry about managing rollback segments ever again.
btuttAuthor Commented:
Sorry Tony, you are right I did not define that at all. I was simply trying to reduce the size of the file when I got the 03297 error, so I hope that clears that issue up. And yes I would like very much to use undo tablespace when all allocations would be done automatically, but that would require testing this in a production environment, something that unfortunately would not happen at this point (long story). The current version of our oracle instance is:

select * from v$version;

 Oracle9i Enterprise Edition Release - Produc
PL/SQL Release - Production
CORE       Production
TNS for 32-bit Windows: Version - Production
NLSRTL Version - Production

Again thank you for all your help. Is there no indication from the select name, xacts from v$rollstat s,v$rollname r where r.usn=s.usn. query as to why only a strict subset of the files within the RBS tablespace are being used for rollback segments?

Ok, I confess to being preoccupied with your problem.  I have an interview for a contract tomorrow and here I am after midnight (my timezone) trying to figure this thing out.  I am sure that if I was in the room with you we could work this out in 5 minutes, but we are not.  That is life.

What I missed up to now (silly me) is that you have 20 rollback segments and 12 datafiles.  The extents in the 20 rollback segments are allocated in just four of the twelve datafiles.  Ok.

This has taken quite some time because I have been making false assumptions as to what you are trying to achieve, so let me apologise for that and get back to your original questions.

It seems to me that you have two questions.

1) Why are only four of your datafiles used and

2) Why can't you reduce the size of one of your "unused" datafiles

Question 1

Whether you are talking about rollback segments, tables, indexes or whatever Oracle does NOT automatically allocate extents to datafiles in a round robin fashion.  It will completely fill one datafile before moving onto the next.  With a table, you can force allocation of extents to multiple datafiles by manually allocating extents with the ALTER TABLE ALLOCATE EXTENT clause.  I know of no way to do this for rollback segments.  So until your transaction load is sufficiently high that you need all the space in your first four datafiles to hold undo data, no extents will be allocated in the fifth and higher datafiles.  However, since all your datafiles are in the same directory I am forced to ask the question: why do you care?  Of course, you could create 20 tablespaces with one datafile and one rollback segment in each.  Then all you would need is twenty concurrent transactions that each had DML activity to cause all of your datafiles to be used.  One other thing.  You need to make sure that all your rollback segments are specified in the ROLLBACK_SEGMENTS initialisation parameter if they are to be brought online at startup.  This has nothing to do with your question, I just thought I would mention it.

Question 2

People sometimes get confused about the concept of "High Water Marks (HWM)".  For example, the "Oracle 9i DBA fundamentals 1" study guide from Sybex (a most excellent book - unlike volume 2 which is not) states on page 212: "When decreasing the file size, oracle returns an error if it finds data beyond the new file size.  You cannot reduce the file size beyond the high-water mark in the file".

Actually, the high-water mark is associated with something like a table, and not a tablespace or datafile.  You can lower the HWM of a table by truncating it.  There is something like a HWM for a datafile, but there is no equivalent of "truncate" to reduce it.  My experience is that once an an extent is EVER allocated to a datafile the associated space can NEVER be reclaimed by a resize.  I have solved this problem in the past by creating a brand new tablespace and dropping the old one.

Why was an extent allocated to datafile 4 in the past?  Who knows.  Maybe the rollback segments grew and then shrunk because of the presence of an OPTIMAL clause when the rollback segment was created.  Maybe somebody created some almighty table in the tablespace, realised their error and then moved or dropped it.  Who knows?  It may be impossible to find out now, but the point is that this state of affairs does not mean that the database is corrupt in any way.

I hope this has helped.  The bottom lines are:

1) Whether Oracle uses 4 or 20 datafiles will have no impact on performance when all datafiles are in the same directory anyway (hopefully on a striped device, yes?)

2) The only ways to recover the space from the unused datafiles are to 1) drop the tablespace and then recreate both the tablespace and the rollback segments or 2) take the datafiles offline.  After either of these processes you can delete the underlying OS files.

Does this make sense?

Do I take it from the silence that you no longer consider my comments useful?  If I have made a mistake please let me know.  I have a thick skin!

If I have been of any help I would appreciate some points.

If I can be of any further assistance please let me know.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
btuttAuthor Commented:
Not at all, just some really busy times at work! OK this is what I originally had an inkling of and will take your advice.

Thanks very much, this case can be closed.
Ok Thanks.

Two last things:

1) Please do not forget to give me the points.

2) My colleague suggests ALTER TABLESPACE xxx COALESCE as a potential way to
allow you to resize files.  I haven't tried it, but I doubt if it will do any harm.
btuttAuthor Commented:
Yes I have already tried this with no effect, thank you though.

Ok, but could you give me the points please?  Just take one of my answers and accept it.

btuttAuthor Commented:
I have done so Tony, thank you. Just a little bit ignorant of the interface since I am so new :) I have closed this also
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.