-
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;
/
-
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
-
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
-
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.
