Rollback segments: maxextents changed during create?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: Rollback segments: maxextents changed during create?

  1. #1
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317

    Rollback segments: maxextents changed during create?

    Hello guys,

    I'm creating a new TS that holds my rollback segments: LMT with uniform size of 512K. (db : oracle 8.1.7.1.1)
    I'm recreating the rbs's because I want to remove the optimal-clause (I detected a lot of shrinking actions in v$rollstat) and the original TS was DD.

    When creating the rollback segment, I issue a MAXEXTENT 505

    Code:
    CREATE PUBLIC ROLLBACK SEGMENT RBS01
        TABLESPACE "RBS2"
        STORAGE ( INITIAL 512K NEXT 512K MINEXTENTS 10 MAXEXTENTS 505)
    When I checked it afterwards, the rbs is created okay, the initial extents are there (checked with "dba_extents"), but the maxextent-item in DBA Studio has changed to 32765, not 505.

    Why did the maxextent-value change? Is this a fault of DBA Studio?

    Can someone clarify this behaviour?

    Thanks in advance!
    Erik
    Last edited by efrijters; 12-04-2003 at 10:24 AM.
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  2. #2
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Please post the result of the query

    select segment_name,MIN_EXTENTS,MAX_EXTENTS
    from sys.dba_rollback_segs;
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  3. #3
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    Code:
    SQL> select segment_name,MIN_EXTENTS,MAX_EXTENTS 
      2  from sys.dba_rollback_segs;
    
    SEGMENT_NAME                   MIN_EXTENTS MAX_EXTENTS
    ------------------------------ ----------- -----------
    SYSTEM                                   2         505
    RBS0                                     8        8192
    RBS1                                     8        8192
    
    ... etc etc...
    
    RBS8                                     8        8192
    RBS9                                     8        8192
    RBS01                                    1       32765
    RBS02                                    1       32765
    Hmmm .... MINEXTENTS now is 1 in stead of 10...
    Last edited by efrijters; 12-04-2003 at 08:27 AM.
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  4. #4
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    It works perfectly fine for me!!

    Code:
    SQL> CREATE PUBLIC ROLLBACK SEGMENT RBS01     TABLESPACE RBS     STORAGE ( INITIAL 512K 
    NEXT 512K MINEXTENTS 10 MAXEXTENTS 505);
    
    Rollback segment created.
    
    SQL> CREATE PUBLIC ROLLBACK SEGMENT "RBS02"     TABLESPACE RBS     STORAGE ( INITIAL 512K 
    NEXT 512K MINEXTENTS 10 MAXEXTENTS 505);
    
    Rollback segment created.
    
    SQL> select segment_name,MIN_EXTENTS,MAX_EXTENTS  from sys.dba_rollback_segs;
    SYSTEM                                   2         505
    RBS0                                     8        4096
    RBS1                                     8        4096
    RBS2                                     8        4096
    RBS3                                     8        4096
    RBS4                                     8        4096
    RBS5                                     8        4096
    RBS6                                     8        4096
    RBS7                                     8        4096
    RBS01                                   10         505
    RBS02                                   10         505
    
    11 rows selected.
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  5. #5
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    Thanks Thomasps!

    This is wierd.... could this be the result of a setting in the TS?

    Code:
    CREATE TABLESPACE "RBS3" 
        LOGGING 
        DATAFILE 'L:\ORACLE\ORADATA\DB01\RBS31.ORA' SIZE 8M REUSE 
        AUTOEXTEND 
        ON NEXT  8192K MAXSIZE  1024M, 
        'L:\ORACLE\ORADATA\DB01\RBS32.ORA' SIZE 8M REUSE AUTOEXTEND 
        ON NEXT  8192K MAXSIZE  1024M EXTENT MANAGEMENT LOCAL UNIFORM 
        SIZE 512K
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  6. #6
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    I did a COPY-PASTE of your create-statement and again 32768 MAXEXTENTS and 1 for MINEXTENTS...
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  7. #7
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    I did a COPY-PASTE of your create-statement and again 32768 MAXEXTENTS and 1 for MINEXTENTS...
    But look at the maxextents it is 32765!!

    Code:
    SEGMENT_NAME                   MIN_EXTENTS MAX_EXTENTS
    ------------------------------ ----------- -----------
    RBS01                                    1       32765
    RBS02                                    1       32765
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  8. #8
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    Please excuse me. The 32768(32k) is such a popular number...

    But the original question still stands: maxextents doesn't have the right value (altering this value isn't possible.)

    I'll try with a lower value, say 400.

    Be right back...
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  9. #9
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    And again... same result

    Code:
    CREATE PUBLIC ROLLBACK SEGMENT RBS04     
        TABLESPACE RBS3     
        STORAGE ( INITIAL 512 NEXT 512K MINEXTENTS 10 MAXEXTENTS 400)
    
    
    rollback-segment created.
    
    SQL> select segment_name,MIN_EXTENTS,MAX_EXTENTS  
           from sys.dba_rollback_segs;
    
    SEGMENT_NAME                   MIN_EXTENTS MAX_EXTENTS
    ------------------------------ ----------- -----------
    SYSTEM                                   2         505
    RBS0                                     8        8192
    RBS1                                     8        8192
    
    ... etc etc...
    
    RBS8                                     8        8192
    RBS9                                     8        8192
    
    SEGMENT_NAME                   MIN_EXTENTS MAX_EXTENTS
    ------------------------------ ----------- -----------
    RBS03                                    1       32765
    RBS02                                    1       32765
    RBS04                                    1       32765
    I'm confused...
    Last edited by efrijters; 12-04-2003 at 08:26 AM.
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  10. #10
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    CREATE ROLLBACK SEGMENT RBS05
    TABLESPACE RBS3
    STORAGE (MINEXTENTS 10 MAXEXTENTS 400);

    Just curious. We are missing something,,?
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

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