cannot extend rollback segment error
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: cannot extend rollback segment error

  1. #1
    Join Date
    Apr 2002
    Posts
    135

    cannot extend rollback segment error

    hi all,


    I have a table with about 20million records.

    I do a update of all records in a single update statement.

    So i get this unable to extend the rollback segment error

    adding a datafile will solve the problem i guess.

    But is there any way of temporary stopping of writing into rollback segment before updating and resuming it after the update statement.

    thanx

  2. #2
    Join Date
    Jan 2002
    Location
    India
    Posts
    105
    I do a update of all records in a single update statement.
    Instead of directly updaing 20Milion records once can u not brake ur transaction into many update and commit statement ?

    Regards
    Viraj
    ----------
    9i OCA
    A Wise Man Knows How much he doesn't know !!!

  3. #3
    Join Date
    Aug 2001
    Posts
    36
    Hi,

    By adding more datafiles you can solve the problem. There is no way to stop writing to rollback segment is you are performing an update.

    Regards,

  4. #4
    Join Date
    Jan 2002
    Location
    India
    Posts
    105

    Lightbulb

    Also you can place your table in nologging mode and later change it to logging mode.

    Regards
    viraj
    -----------
    9i OCA
    A Wise Man Knows How much he doesn't know !!!

  5. #5
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Hi

    Create a huge table space, create a rollback segment with initian and next say 500M min extents 5 make the optimal as 2 GB.

    and before the update give the command

    set transaction rollback segment segment_name;

    this will make sure that you use only this rollback segment that you have just created.

    Regards
    Amar
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I think that you would be pleasantly surprised by the benefits of not doing the operation as an update.

    You could instead try "CTAS" -- create table ... as select ...

    You can make the desired update changes as part of the select, and with the "nologging" option you will generate very little redo log (I don't believe that an update performed against a "nologging" table will cut down on the amount of logging performed).

    You can then rename the old table, rename the new table ,apply indexes, constraints, grants etc.

    It will be much faster.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by virajvk
    Also you can place your table in nologging mode and later change it to logging mode.
    There is no such thing as "performing UPDATE in nologging mode", no matter if your table is in logging or nologging mode......
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Feb 2001
    Location
    UAE
    Posts
    304

    Lightbulb

    I would do it group by group.
    Agasimani
    OCP(10g/9i/8i/8)

  9. #9
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by slimdave
    I think that you would be pleasantly surprised by the benefits of not doing the operation as an update.

    You could instead try "CTAS" -- create table ... as select ...

    You can make the desired update changes as part of the select, and with the "nologging" option you will generate very little redo log (I don't believe that an update performed against a "nologging" table will cut down on the amount of logging performed).

    You can then rename the old table, rename the new table ,apply indexes, constraints, grants etc.

    It will be much faster.
    I think that the suggestion above is very good; do follow it.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Originally posted by virajvk
    Also you can place your table in nologging mode and later change it to logging mode.

    Regards
    viraj
    -----------
    9i OCA

    nologging is only useful for direct insert/CTAS operations

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