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

Posted on 2004-11-20
Medium Priority
Last Modified: 2008-01-09
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?

Question by:btutt
  • 7
  • 6

Expert Comment

ID: 12635778
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

Author Comment

ID: 12636370
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,



Expert Comment

ID: 12638900
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.


Author Comment

ID: 12639980
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 t.name, status from v$transaction t,v$rollname r where xidusn=usn,
which yields:

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

Kind regards,


Expert Comment

ID: 12645778
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.

Author Comment

ID: 12646357
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?


Expert Comment

ID: 12650694
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?

Accepted Solution

Tony_Hasler earned 750 total points
ID: 12695634

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.

Author Comment

ID: 12706853
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.

Expert Comment

ID: 12709212
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.

Author Comment

ID: 12737311
Yes I have already tried this with no effect, thank you though.


Expert Comment

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


Author Comment

ID: 12756302
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

Featured Post

Technology Partners: 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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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 how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

839 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