Bypass Rollback Segment
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Bypass Rollback Segment

  1. #1
    Join Date
    Apr 2003
    Posts
    353

    Thumbs up Bypass Rollback Segment

    Hi

    I am going to update a large table contains 5 million + records.

    With normal mechanism of Rollback segment usage it is
    a time consuming job.
    The above can be accomplished by a create table command.But we dont want to create so.

    I am running the database in RESTRIct mode
    during this update command.


    Is there any way to bypass the Rollback segment Mechanism So that we could make this updation process
    runs faster.

    Thanks
    Giri

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Can't
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  3. #3
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    I don't think you can bypass UNDO generation. Create a BIG rollback segment and set your transaction to use that segment.
    HTH
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  4. #4
    Join Date
    Nov 2000
    Location
    Israel
    Posts
    268
    you can write a PL/SQL loop caluse and commit every n records.
    It is better to ask and appear ignorant, than to remain silent and remain ignorant.

    Oracle OCP DBA 9i,
    C++, Java developer

  5. #5
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    His concern is to speed up process and not Rollback Space ( as per his post ).

    So, PL/SQL code will not be of much help.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  6. #6
    Join Date
    Apr 2003
    Posts
    353
    Yes Mr.abhaysk
    is correct

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by engiri
    Yes Mr.abhaysk
    is correct
    U cant bypass ROLLBACK, to spead up process.

    Ofccource you cud aviod LOGS..if table created in NOLOGGING or u cud Alter it to NOLOGGING.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  8. #8
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Originally posted by abhaysk
    U cant bypass ROLLBACK, to spead up process.

    Ofccource you cud aviod LOGS..if table created in NOLOGGING or u cud Alter it to NOLOGGING.
    UPDATE is not eligible for NOLOGGING only certain operations like CREATE INDEX, REBUILD INDEX, CTAS, DIRECT_INSERT can take advantage of nogging. UPDATEs and DELETEs will always be logged to redo.
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  9. #9
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    As of Version 8i following operations can make use of no-logging mode:

    direct load (SQL*Loader)
    direct-load INSERT
    CREATE TABLE ... AS SELECT
    CREATE INDEX
    ALTER TABLE ... MOVE PARTITION
    ALTER TABLE ... SPLIT PARTITION
    ALTER INDEX ... SPLIT PARTITION
    ALTER INDEX ... REBUILD
    ALTER INDEX ... REBUILD PARTITION
    INSERT, UPDATE, and DELETE on LOBs in NOCACHE NOLOGGING mode stored out of line
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  10. #10
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by SANJAY_G
    UPDATE is not eligible for NOLOGGING only certain operations like CREATE INDEX, REBUILD INDEX, CTAS, DIRECT_INSERT can take advantage of nogging. UPDATEs and DELETEs will always be logged to redo.
    Sanjay :

    Thas correct.

    I was refering to Engiri's "Create Select" Part ( which he didnt want to use ).
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

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