Home » SQL & PL/SQL » SQL & PL/SQL » How to disable implicit commit in Oracle (Oracle 10.2g Windows XP)
How to disable implicit commit in Oracle [message #325409] Fri, 06 June 2008 02:43 Go to next message
Metalpalo
Messages: 3
Registered: June 2008
Junior Member
Hello

I need to erase enormous count of rows from my table(arround 1000000 rows). This table is foun in the large database problem.
My problem is that erasing take very much time(ca 5 hours). There fore I tried to disable problematic constrains in database and I got acceptable erasing time. But I erase more than one table whereupon I want to execute as one transaction.

I find that Oracle automatic call commit when I enable/disable constrains(DDL part).

Can somebody help me how to acomplish it as one transaction?

Thanks
Re: How to disable implicit commit in Oracle [message #325417 is a reply to message #325409] Fri, 06 June 2008 02:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't disable commit in DDL.
Put the DLL outside your functional transaction:
- Disable constraints DDL
- Your updates
- Enable constraints DDL

Regards
Michel
Re: How to disable implicit commit in Oracle [message #325419 is a reply to message #325409] Fri, 06 June 2008 02:55 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Disable all constraints before, and enable all of them after deleting is done.
Re: How to disable implicit commit in Oracle [message #325523 is a reply to message #325409] Fri, 06 June 2008 08:46 Go to previous messageGo to next message
Metalpalo
Messages: 3
Registered: June 2008
Junior Member
Hello

It would be good solution but I omitted that after all deleting process I need to import new data into tables. I want to ensure consinstence of database what means that all constraints must be enabled again before inserting.

If not possible how can I speed up deleting process? BY creating indexes for relevant constraints or how ?

Can somebody explain me some solution?

Thanks

Re: How to disable implicit commit in Oracle [message #325528 is a reply to message #325523] Fri, 06 June 2008 08:56 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
1) Disable constraints on all tables
2) Delete all tables
3) Enable constraints on all tables
4) Import new data into all tables.
Re: How to disable implicit commit in Oracle [message #325605 is a reply to message #325409] Fri, 06 June 2008 12:41 Go to previous messageGo to next message
Metalpalo
Messages: 3
Registered: June 2008
Junior Member
But third step triggers implicit commit by Oracle what is not acceptable for me.

I want to commit transaction after 4.step is done. If import is not successful any data will be not deleted what means rollback.

[Updated on: Fri, 06 June 2008 12:43]

Report message to a moderator

Re: How to disable implicit commit in Oracle [message #325606 is a reply to message #325605] Fri, 06 June 2008 12:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't.

Regards
Michel
Re: How to disable implicit commit in Oracle [message #325852 is a reply to message #325606] Mon, 09 June 2008 04:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You might be able to get away with this.

Documentation

1) Alter the state of your constraints to DEFERRED (you may have to recreate the constraints as DEFERRABLE) or set them all as 'DISABLE NOVALIDATE'

2) Do all the deletes and inserts

3) Manually check the data integrity

4) Based on the results from 3) either Commit or Rollback

Re: How to disable implicit commit in Oracle [message #325906 is a reply to message #325852] Mon, 09 June 2008 08:06 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you plan to do manual checks, you might as well drop the constraints and recreate..
Previous Topic: Disable validate
Next Topic: ORA-000979: not a GROUP BY expression
Goto Forum:
  


Current Time: Sun Dec 04 18:25:24 CST 2016

Total time taken to generate the page: 0.20005 seconds