Unable to extend rollback segments
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Unable to extend rollback segments

  1. #1
    Join Date
    Mar 2010
    Posts
    8

    Unable to extend rollback segments

    How to extend rollback segment ?

    ALTER ROLLBACK SEGMENT RBS_04 STORAGE (OPTIMAL 500M);

    query was success.

    but when I try to find a total rollback tablespace size it is showing old value ?

    was it extended or not ?
    and

    how to find allocated space for all rollback segments ?

    After that i try to fire a below query--

    prd176> ALTER ROLLBACK SEGMENT RBS_04 STORAGE (MAXEXTENTS 65530);
    ALTER ROLLBACK SEGMENT RBS_04 STORAGE (MAXEXTENTS 65530)
    *
    ERROR at line 1:
    ORA-02221: invalid MAXEXTENTS storage option value

    how to check valid maxextents ?

    finally :

    customer got ORA-01555 error, and database having rollback segments ( not undo tablespace ) , becuase of rollback segment RBS_04 , customer got that error, so how to extend the segment RBS_o4., first I need to find the allocated space for RBS_04 but how ?

    prd176> SELECT SEGMENT_NAME,TABLESPACE_NAME,FILE_ID,MAX_EXTENTS,MIN_EXTENTS
    FROM DBA_ROLLBACK_SEGS
    2 3 ;
    Tablespace
    SEGMENT_NAME Name FILE_ID MAX_EXTENTS MIN_EXTENTS
    -------------------- -------------------- ---------- ----------- -----------
    SYSTEM SYSTEM 1 505 2
    RBS_01 ROLL_T01 2 32765 1
    RBS_02 ROLL_T01 2 32765 1
    RBS_03 ROLL_T01 2 32765 1
    RBS_04 ROLL_T01 2 32765 1
    prd176> @tbspace_info
    Tablespace Size Used Free Pct. File TS Ext
    Name (MB) (MB) (MB) Used Count Type Mgmt
    ------------------------------ -------- -------- ------- ------- ----- ---- -----
    USERS_T01 41 0 41 0.30 1 PERM LCL-A
    TOOLS_T01 76 0 76 0.16 1 PERM LCL-A
    SYSAUX 501 352 149 70.22 1 PERM LCL-A
    TEMP_T01 500 299 201 59.72 1 TEMP DICT
    GLOBAL2_DATA_T01 1001 679 322 67.79 1 PERM LCL-A
    SYSTEM 600 275 325 45.90 1 PERM DICT
    GLOBAL2_INDEX_T01 901 513 389 56.88 1 PERM LCL-A
    ROLL_T01 1000 21 980 2.05 1 PERM LCL-U
    EMADOCS_DATA_T01 9626 7041 2585 73.15 6 PERM LCL-A
    DMS_AUDIT_DATA_T01 56508 51871 4637 91.79 15 PERM LCL-A
    Share on Google+

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    This is dumb. You have sysaux tablespace - that is indicative of at least 10g, where undo has been present since 9i, so if you are still using rollback segments, 1) that is stupid, and 2) you deserve whatever problems you encounter using them. I mean, the whole point of going to automated undo was to avoid situations like yours.

    What possible reason do you have (and the correct answer is none) for still using RBS?
    Share on Google+

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    I'm in agreement with Stecal's underlying concepts even if I would word it in a different way

    Putting in the rear mirror the fact that you shouldn't be using RBS, increasing the size of RBS_04 will solve nothing, transaction will most likelly use a different RBS next time it executes.

    Root cause of ORA-01555 is not the size of the RBS per-se but the inability of getting a consistent read. To minimize occurrence of this issue while you plan your exit from RBS try this...
    1- Create a new, large RBS, call it RBS_BATCH
    2- Assign offending transaction to RBS_BATCH
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
    Share on Google+

  4. #4
    Join Date
    Mar 2010
    Posts
    8
    I Am expecting Answers Only I am not expecting Useless suggestions ?
    If everyone knows everything what are the uses of forums , please understand this thing ...!

    The Person Who knows Answer, Only they will give Answers the rest of people are joining in the forums and giving useless suggestions and wasting our time ..


    Please Dont post this type of Answers.

    Thanks.
    Share on Google+

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by gssr141 View Post
    ... wasting our time
    Agreed. I do regreat wasting my time on you
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
    Share on Google+

  6. #6
    Join Date
    May 2002
    Posts
    2,645
    Quote Originally Posted by gssr141 View Post
    I Am expecting Answers Only I am not expecting Useless suggestions ?
    If everyone knows everything what are the uses of forums , please understand this thing ...!

    The Person Who knows Answer, Only they will give Answers the rest of people are joining in the forums and giving useless suggestions and wasting our time ..


    Please Dont post this type of Answers.

    Thanks.
    Defend your usage of rollback segments. There is no defense, that is why using them in a 10g environment is a waste of time. If you want to beat your head against the wall by using RBS, go ahead. The person who wants to learn will ask why instead of expecting to be spoon fed answers without wondering why (or why not) it is correct.

    "Please Dont post this type of Questions."
    Share on Google+

  7. #7
    Join Date
    Mar 2010
    Posts
    8
    Hi ,

    User got ORA-01555 error , And My database is runnong with rollback segments ?
    User got following error ? ORA-01555 'UNABLE TO EXTEND ROLLBACK SEGMENT RBS_O4'

    After that I try like this :

    prd176> ALTER ROLLBACK SEGMENT RBS_04 STORAGE (MAXEXTENTS 65530);
    ALTER ROLLBACK SEGMENT RBS_04 STORAGE (MAXEXTENTS 65530)
    *
    ERROR at line 1:
    ORA-02221: invalid MAXEXTENTS storage option value

    What I have to do ?
    Please give solution , means complete command ?

    here I am pasting my db information;

    prd176> SELECT SEGMENT_NAME,TABLESPACE_NAME,FILE_ID,MAX_EXTENTS,MIN_EXTENTS
    FROM DBA_ROLLBACK_SEGS
    2 3 ;
    Tablespace
    SEGMENT_NAME Name FILE_ID MAX_EXTENTS MIN_EXTENTS
    -------------------- -------------------- ---------- ----------- -----------
    SYSTEM SYSTEM 1 505 2
    RBS_01 ROLL_T01 2 32765 1
    RBS_02 ROLL_T01 2 32765 1
    RBS_03 ROLL_T01 2 32765 1
    RBS_04 ROLL_T01 2 32765 1

    Size of ROLL_T01 tablespace size is 1GB.

    Please give me the complete solution?
    Share on Google+

  8. #8
    Join Date
    May 2002
    Posts
    2,645
    Your question has been answered: don't use rollback segments, and don't spam your question on other sites.
    Share on Google+

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width