commit after 1000 records
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: commit after 1000 records

  1. #1
    Join Date
    Jun 2003
    Posts
    132

    commit after 1000 records

    Hi,

    I have the following Stored Procedure. I would like the stored procedure to commit after every 1000 records, but I do not how to do it.

    Could someone help me out please

    Thanks



    -----------------------------------------------

    PROCEDURE APPEND_TRN_NORM_30DAY as

    DTE DATE;
    CARRIER VARCHAR2(64);
    SRP_ID VARCHAR2(32);
    PIN NUMBER;
    RELAY VARCHAR2(64);
    DEVICE_TYPE VARCHAR2(32);
    MESSAGE_TYPE VARCHAR2(32);
    MFH_COUNT NUMBER;
    MFH_SIZE NUMBER;
    MTH_COUNT NUMBER;
    MTH_SIZE NUMBER;

    CURSOR C_TRN_NORM_30DAY IS

    SELECT /* + INDEX(A CDR_SUM_DTE) +*/

    distinct
    a.DTE, b.CARRIER, c.SRPID, a.PIN, a.RELAY,
    b.DEVICE_TYPE, a.MSGTYPE, a.MFH_COUNT,
    a.MFH_SIZE, a.MTH_COUNT, a.MTH_SIZE

    FROM bblg.CDR_SUMMARY@prldw a, bblg.DEVICE_INFO@prldw b, BBLG.SRP_INFO@prldw c

    WHERE a.PIN = b.PIN
    AND a.SRPID_ID = c.ID

    AND a.DTE BETWEEN trunc(sysdate) - 32 AND trunc(sysdate) - 2;

    cv_row C_TRN_NORM_30DAY%ROWTYPE;

    BEGIN

    OPEN C_TRN_NORM_30DAY;


    LOOP

    FETCH C_TRN_NORM_30DAY INTO cv_row;

    EXIT WHEN C_TRN_NORM_30DAY%NOTFOUND;

    INSERT INTO TRN_NORM_30DAY
    (DTE, CARRIER, SRP_ID, PIN, RELAY, DEVICE_TYPE, MESSAGE_TYPE, MFH_COUNT, MFH_SIZE, MTH_COUNT, MTH_SIZE)
    VALUES
    (cv_row.DTE, cv_row.CARRIER, cv_row.SRPID, cv_row.PIN, cv_row.RELAY, cv_row.DEVICE_TYPE,
    cv_row.MSGTYPE, cv_row.MFH_COUNT, cv_row.MFH_SIZE, cv_row.MTH_COUNT, cv_row.MFH_SIZE);


    END loop;
    COMMIT;
    END;

    --------------------------------------------------------

  2. #2
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    Add a counter

    Code:
    counter := 0
    
    insert ...
    counter := counter + 1
    if counter = 1000 then
      commit;
      counter := 0;
    end if;
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Why do you want to commit every 100 records?
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  4. #4
    Join Date
    Jun 2003
    Posts
    132
    Because this procedure will insert over 1 mil. records and my temp tablespace is very limited. I let the procedure to run and after 5 hours I got the following error(Error): ORA-30036: unable to extend segment by 1024 in undo tablespace 'UNDOTBS1'.

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    sorry, 1000 records
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    why aren't you doing ...
    Code:
    insert into TRN_NORM_30DAY
    select ... from bblg.CDR_SUMMARY@prldw ...
    This line-by-line stuff sucks for performance.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  7. #7
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    Originally posted by rkiss
    Because this procedure will insert over 1 mil. records and my temp tablespace is very limited. I let the procedure to run and after 5 hours I got the following error(Error): ORA-30036: unable to extend segment by 1024 in undo tablespace 'UNDOTBS1'.
    frequent commits increase the chance of ora-1555 so you can commit frequently if you like but risk running into snapshot too old

    why dont oyu just increase the size of the undo tbs?

    steve
    I'm stmontgo and I approve of this message

  8. #8
    Join Date
    Jun 2003
    Posts
    132
    Since it tries to do the whole thing in one commit, it will fail with rollback problems (again).

    Thank you for you help

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Have you tried it and measured the amount of rollback?

    Have you also tried insert /*+ append */ with the table in nologging mode?
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  10. #10
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Read this also.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

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