problem with rollback segments
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: problem with rollback segments

  1. #1
    Join Date
    Nov 2001
    Posts
    27

    Exclamation

    ok, here it is :

    I have to create 4 tables. Here is the space I need for each of my tables.

    table_1 - 2M
    table_2 - 1M
    table_3 - 75M
    table_4 - 1M

    So, thats gives me a total of about 80M.

    I have created a tablespace for all 4 of my tables.

    Here is the syntax for creating my tablespace.

    CREATE TABLESPACE IDOT DATAFILE '/usr/local/oracle/8i/IDOT01.dbf'
    SIZE 100M REUSE DEFAULT STORAGE (INITIAL 80M NEXT 5M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0) PERMANENT;

    Now if I am not mistaken, if I create my own tablespace I have to create my own rollback segment.

    But, in order to create my own rollback segment I should create a tablespace for it.

    So, here is my tablespace for my rollback segment.

    CREATE TABLESPACE IDOT_RBS DATAFILE '/usr/local/oracle/8i/IDOT01_RBS.dbf' SIZE 100M REUSE DEFAULT STORAGE
    (INITIAL 80M NEXT 5M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0) PERMANENT;

    My rollback segment tablespace is created exactly the same way as my first tablespace.

    Now here is how I created my rollback segment.

    CREATE ROLLBACK SEGMENT IDOT_RBS1 TABLESPACE IDOT_RBS STORAGE (INITIAL 80 NEXT 80M MAXEXTENTS UNLIMITED);

    Then I have to put my rollback segment ONLINE ...

    ALTER ROLLBACK SEGMENT IDOT_RBS1 ONLINE;


    Ok, so now I start to create my tables.

    Here is how I create my first table.

    CREATE TABLE table_1 (
    field_1
    field_2 ...

    storage (initial 10M next 1M pctincrease 0) tablespace IDOT;

    My initial storage is more than enough for my data. I have no problems creating this table.

    Here is my second table.

    CREATE TABLE table_2 (
    field_1
    field_2 ...

    storage (initial 3M next 1M pctincrease 0) tablespace IDOT;

    Once again my storage is more than enough for my data ... BUT, I am getting an ERROR message.

    It says,

    ORA-01658: unable to create INITIAL extent for segment in tablespace IDOT

    Could anyone tell me why I am getting this message? If anyone has any idea please reply, I have been working on this problem for over a week and still I can't figure it out.

    Pleeeeeeeeeeeease .... anyone HELP !

  2. #2
    Join Date
    Mar 2001
    Posts
    286
    "ORA-01658: unable to create INITIAL extent for segment in tablespace IDOT"

    If you got this error message, that means that you do not have enough contiguous space for the initial extent for the segment.

    Try this, enlarge the datafile in IDOT tablespace.

    Try this: Can you create a talbe using small initial extent?


  3. #3
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    hi,
    There is no problem with ur rollback segment creation..
    CREATE TABLESPACE IDOT DATAFILE '/usr/local/oracle/8i/IDOT01.dbf'
    SIZE 100M REUSE DEFAULT STORAGE (INITIAL 80M NEXT 5M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0) PERMANENT;

    change ur maxextents dont give unlimited give some size ,because once u give ut maxextents unlimited then oracle will inturn takes care of ur extents size so we might not know when it is getting filled up . Considering the performacne issue dont give unlimited extent size.Once the maximun specified extent size is reahce u can alter the size of the extent.

    eg.
    if ur initla exent is 5K and next is 5k...ur first exent size will be 5 once the first exent gets filled the next extents size will be 10 and the next one will we 15 .. this will exetnet till ur extends accoring to ur max exetnd size so if u give max extents unlimited u dont have the control over it!!

    so remove the max extents unlimited from all the tablepace and try to give some size !!!

    TRy this way ....if u have any queries revert back

    Thanks,
    Jegan
    Cheers!
    OraKid.

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,967
    The way you have the tablespace sized you will allocate 80M for all four tables. First look at automatic allocation if you are running 8i/9i if not then look at the following

    CREATE TABLESPACE IDOT DATAFILE '/usr/local/oracle/8i/IDOT01.dbf'
    SIZE 100M REUSE DEFAULT STORAGE (INITIAL 2M NEXT 2M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0) PERMANENT;

    Also if you have public rollback segments you do not need to create new rollback segments for every tablespace. So if you don't have enough rollback then look at the following.

    CREATE TABLESPACE IDOT_RBS DATAFILE '/usr/local/oracle/8i/IDOT01_RBS.dbf' SIZE 100M REUSE DEFAULT STORAGE
    (INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0) PERMANENT;

    CREATE ROLLBACK SEGMENT IDOT_RBS1 TABLESPACE IDOT_RBS STORAGE (MINEXTENTS 16 OPTIMAL 16M);

    Also if you specify the right default storage on your tablespace then you don't need to specify storage parameters whenever you create a table in that tablespace. You do have a typo on your rollback creation you specified an inital extent of 80 which is 80 bytes. I think that you will find that Locally managed tablespaces are faster, easier to setup and require less maintenance. Also you may want to use the DBA Studio in 8i/or the Enterprise Manager Console in 9i. This would make it easier and keep you from wasting a week.


  5. #5
    Join Date
    Nov 2001
    Posts
    27
    OK, THANK YOU ALL FOR THE HELP !!

    I have found a simple solution to my problem which seems to work beautifully.

    However, I'm not sure if this solution is the most efficient one for my problem. If anyone has any warnings about doing this please let me know.

    I simply turned ON the AUTOEXTEND option for my tablespace.

    CREATE TABLESPACE IDOT DATAFILE '/usr/local/oracle/8i/IDOT.dbf' SIZE 300M REUSE AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED;

    And also for the rollback segment tablespace.

    CREATE TABLESPACE IDOT_RBS DATAFILE '/usr/local/oracle/8i/IDOT_RBS.dbf' SIZE 300M REUSE AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED;

    And this is how I created the rollback segment :

    CREATE ROLLBACK SEGMENT IDOT_RBS1 TABLESPACE IDOT_RBS STORAGE (INITIAL 150M NEXT 150M MAXEXTENTS UNLIMITED);

  6. #6
    Join Date
    Mar 2001
    Posts
    286
    Hey, you!

    Did you really try this to create your tables? I have no problem creating tables:

    SQL> CREATE TABLESPACE IDOT DATAFILE '/export/home/u01/oradata/koya/IDOT01.dbf'
    2 SIZE 100M REUSE DEFAULT STORAGE (INITIAL 80M NEXT 5M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0) PERMANENT;

    Tablespace created.

    1 CREATE TABLE table_1 (
    2 field_1 number)
    3* storage (initial 10M next 1M pctincrease 0) tablespace IDOT
    SQL> /

    Table created.
    1 CREATE TABLE table_2 (
    2 field_1 number)
    3* storage (initial 10M next 1M pctincrease 0) tablespace IDOT
    SQL> /

    Table created.

    ******************
    However, if you forget to "overwrite" the default storage, you will:

    1 CREATE TABLE table_3 (
    2 field_1 number)
    3* tablespace IDOT
    SQL> /
    CREATE TABLE table_3 (
    *
    ERROR at line 1:
    ORA-01658: unable to create INITIAL extent for segment in tablespace IDOT



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