| Intermediate Commit [message #208717] |
Mon, 11 December 2006 23:52  |
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  |
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 #208836 is a reply to message #208825] |
Tue, 12 December 2006 06:02 |
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 |
 |
Mahesh Rajendran
Messages: 10708
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. 
[Updated on: Tue, 12 December 2006 06:07] Report message to a moderator |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
