Committing every Nth record -- Please Help! Deadline coming soon...
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Committing every Nth record -- Please Help! Deadline coming soon...

  1. #1
    Join Date
    Apr 2000
    Location
    Boston, MA 01803
    Posts
    24

    Unhappy

    Help!!!

    I'm not sure if I'm just incorrectly trying to commit every nth record, or if because I'm handling 4 million rows I keep getting the "ORA-01652: unable to extend temp segment..." error.

    My goal is to move 2 large staging tables into one Fact Table. Any suggestions would be greatly appreciated!

    Thanks,
    tracy


    DECLARE
    CURSOR c1 IS SELECT
    a.BUS_DATE_KEY bus_date_key ,a.BATCH_KEY batch_key ,
    b.area_id area_id, sum(a.sales)
    from stage_trade a, stage_month_earn b
    where a.loan_join = b.loan_join
    group by a.BUS_DATE_KEY ,a.BATCH_KEY ,b.AREA_id;
    ctr NUMBER := 0;
    BEGIN
    FOR tg_rec IN c1 LOOP
    ctr := ctr + 1;
    IF ctr = 100 THEN
    COMMIT;
    INSERT into fct_trade_alloc (
    BUS_DATE_KEY,BATCH_KEY,AREA_id,sales)
    VALUES (
    tg_rec.BUS_DATE_KEY,tg_rec.batch_key,
    tg_rec.area_id, tg_rec.sales);
    END IF;
    END LOOP;
    END;
    /

  2. #2
    Join Date
    Mar 2001
    Posts
    635
    Hi Tracy,

    I had a similiar problem as yours and was ending up with the same error as yours the problem I feel is in your cursor declaration that is going to gobble up your whole temp tablespace.My suggestion is to create temporary tables for your query and reference the table in your cursor. In this way it will not use the temp tablespace to a large extent. Remember when you are inserting it use the rollback segment to keep track of your transaction so the error you are getting is nothing related with commiting the records.

    Regards
    Santosh

  3. #3
    Join Date
    Mar 2001
    Posts
    635
    Hi

    The other alternative is you have to increase you temp tablepsace and adjust your minextents.maxextents,initial extents and next extents to accomdate the rows of the two tables before you execute your pl/sql block.

    Regards
    Santosh

  4. #4
    Join Date
    Jun 2000
    Posts
    417
    Also your loop condition is if "ctr = 100", but I don't see anything that resets it to 0. So the first 100 rows are committed, but afterwords ctr = 101 and just keeps adding, so there are no other intermediate commits. You probably want to reset counter to zero after committing, or just do your conditional off of ctr % 100.

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