Home » RDBMS Server » Server Administration » Intermediate Commit
Intermediate Commit [message #208717] Mon, 11 December 2006 23:52 Go to next message
mitra.kausik
Messages: 21
Registered: June 2006
Junior Member
I need to insert some 50 million records from one table to another.
I want to introduce some intermediate commit between this insertion.
For eg after every 50,000 record insertion there will be commit.
Can I do it at the instance level or session level.
If so how .


Please let me know.

Thanks

Re: Intermediate Commit [message #208726 is a reply to message #208717] Tue, 12 December 2006 00:19 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

hi,

try this i will helps
SET AUTO[COMMIT]{ON | OFF | IMM[EDIATE] | n}
Controls when Oracle Database commits pending changes to the database after SQL or PL/SQL commands.

ON commits pending changes to the database after Oracle Database executes each successful INSERT, UPDATE, or DELETE, or PL/SQL block. OFF suppresses automatic committing so that you must commit changes manually (for example, with the SQL command COMMIT). IMMEDIATE functions in the same manner as ON. n commits pending changes to the database after Oracle Database executes n successful SQL INSERT, UPDATE, or DELETE commands, or PL/SQL blocks. n cannot be less than zero or greater than 2,000,000,000. The statement counter is reset to zero after successful completion of n INSERT, UPDATE or DELETE commands or PL/SQL blocks, a commit, a rollback, or a SET AUTOCOMMIT command.

SET AUTOCOMMIT does not alter the commit behavior when SQL*Plus exits. Any uncommitted data is committed by default.


Note:

For this feature, a PL/SQL block is considered one transaction, regardless of the actual number of SQL commands contained within it.



http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14357/ch12040.htm#sthref2687

[Updated on: Tue, 12 December 2006 00:20]

Report message to a moderator

Re: Intermediate Commit [message #208795 is a reply to message #208717] Tue, 12 December 2006 04:48 Go to previous messageGo to next message
bwetkstr
Messages: 114
Registered: August 2005
Senior Member
maybe snapshots could help also
Re: Intermediate Commit [message #208825 is a reply to message #208717] Tue, 12 December 2006 05:49 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> after every 50,000 record insertion there will be commit.
Question is why?
Why do you want to commit frequently?
It will more or less lead you to ORA-01555. It will more slower. If the transaction fails, you will be left with partial data.
So why?
Re: Intermediate Commit [message #208836 is a reply to message #208825] Tue, 12 December 2006 06:02 Go to previous messageGo to next message
bwetkstr
Messages: 114
Registered: August 2005
Senior Member
Mahesh Rajendran wrote on Tue, 12 December 2006 12:49
>>
It will more or less lead you to ORA-01555. I



Couldn't this error be solved by creating a large undo tablespace specially for that transaction?
Re: Intermediate Commit [message #208838 is a reply to message #208836] Tue, 12 December 2006 06:06 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> Couldn't this error be solved by creating a large undo tablespace specially for that transaction?
Yes. That is way to go.
large undotablespace and undo_retention.
But COMMITTING frequently is not the solution for this.
If the transaction is large , it needs a large UNDO/UNDO_RETENTION.
So if you commit frequently and still the transaction is large, commiting does not help in this aspect.
It is like fixing the symptom instead of fixing the problem. Smile

[Updated on: Tue, 12 December 2006 06:07]

Report message to a moderator

Re: Intermediate Commit [message #208851 is a reply to message #208838] Tue, 12 December 2006 06:23 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

First Im curios if he is using
manual or automatic undo management ?

Havent seen any info on that...
Re: Intermediate Commit [message #208972 is a reply to message #208851] Tue, 12 December 2006 15:29 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
this starts of discussing Updates, but should cover most of your considerations.
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:6407993912330
Previous Topic: Denormalized columns
Next Topic: DBMS_SCHEDULER Problem
Goto Forum:
  


Current Time: Sat Dec 10 01:33:55 CST 2016

Total time taken to generate the page: 0.07775 seconds