| Commiting in between transactions [message #38344] |
Fri, 12 April 2002 08:15  |
Rajesh K
Messages: 8
Registered: March 2002
|
Junior Member |
|
|
Hi all...
Please advice with this scenario.
I am issuing a SQL statement as follows....
1) set autocommit on;
2) delete from table T1 where (condition)
( this table has 100,000 rows)
When I execute this statement, I get the error saying that it could not extend the rollback segment size and the delete operation fails.
So I would like to do a commit after every few rows (say 25) How do I specify this along with the delete statement. Is there any SQL construct for that..??
Thanks.
|
|
|
|
| Re: Commiting in between transactions [message #38347 is a reply to message #38344] |
Fri, 12 April 2002 09:17  |
Cindy
Messages: 88
Registered: November 1999
|
Member |
|
|
You can increase your rollback segments, use pl/sql, or add rownum < your_number to your delete statement.
delete from table T1 where (condition) and rownum < 10001;
Repeat statement until all records are deleted.
It is not the best solution, but it gets the job done without lots of coding.
|
|
|
|
| Re: Commiting in between transactions [message #38351 is a reply to message #38344] |
Fri, 12 April 2002 16:09 |
Rajesh K
Messages: 8
Registered: March 2002
|
Junior Member |
|
|
Hi
Thanks for the replies.
I am trying to do this delete operation using a single SQL statement since I am using the PERL DBI module for connecting to the database and executing the SQL command. That's why I cannot use a cursor like program.
So I would like to know if there is a single command like
Delete from T1 where (condition) commit after 25 rows
or something similar to that.
Thanks again
|
|
|
|
| Re: Commiting in between transactions [message #38355 is a reply to message #38344] |
Sun, 14 April 2002 01:51 |
Phenoracle
Messages: 35
Registered: March 2001
|
Member |
|
|
Many times we come across this problem and as you see from the replies it is mainly down to the size of your rollback segments. Processing multiple transactions as Cindy said is one way but the root problem will never go away until the rollback segments are sized correctly. One transaction is the best method as with multiple ones you are asking Oracle to use more processes
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
