-
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;
--------------------------------------------------------
-
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!
-
Why do you want to commit every 100 records?
-
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'.
-
-
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.
-
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
-
Since it tries to do the whole thing in one commit, it will fail with rollback problems (again).
Thank you for you help
-
Have you tried it and measured the amount of rollback?
Have you also tried insert /*+ append */ with the table in nologging mode?
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|