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

Posted on 2004-11-20
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
    LVL 2

    Expert Comment

    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
    LVL 6

    Author Comment

    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,


    LVL 2

    Expert Comment

    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
    LVL 6

    Author Comment

    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,

    LVL 2

    Expert Comment

    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.
    LVL 6

    Author Comment

    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?

    LVL 2

    Expert Comment

    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?
    LVL 2

    Accepted Solution


    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.
    LVL 6

    Author Comment

    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.
    LVL 2

    Expert Comment

    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.
    LVL 6

    Author Comment

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

    LVL 2

    Expert Comment

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

    LVL 6

    Author Comment

    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

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    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 …
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    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 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.

    734 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

    22 Experts available now in Live!

    Get 1:1 Help Now