Home » SQL & PL/SQL » SQL & PL/SQL » ROLLBACK usingTRUNCATE (Oracle 11.2.0.3)
ROLLBACK usingTRUNCATE [message #658757] Mon, 26 December 2016 03:07 Go to next message
pointers
Messages: 439
Registered: May 2008
Senior Member
Hi,

I've couple of tables mapped under each group. Let say -

Group 1000 will have two tables EMP and DEPT.
Group 1001 will have two tables COMM and LOCATION.

I would push data from one database to another database for all the tables under a group using DB Link.
All this is written dynamically.

Once data is pushed from one database (source) to another (target), data will be deleted from all the tables of the group in the source database.

I would like to use TRUNCATE instead of DELETE for removing data. But the problem is if I use TRUNCATE it would COMMIT which I don't want happen until the last stage to make the transaction complete.

Let say, I've pushed data for Group the group 1000 to the target database i.e. two tables, EMP and DEPT.

I've started TRUNCATE of EMP table and went sucessfully.

While deleting next table of the group i.e. DEPT, I've got some error.
Ideally, I want ROLLBACK everything including data push from one database to another.
I could ROLLBACK If I employ DELETE. But If I use TRUNCATE it would COMMIT and I will not be able to ROLLBACK.
I don't want to use DELETE because the DELETE will be fired multiple times and I may see HIGH WATERMARK issues.

Please suggest your thoughts on this.

Thank you in advance.

Regards,
Pointers

Re: ROLLBACK usingTRUNCATE [message #658759 is a reply to message #658757] Mon, 26 December 2016 04:59 Go to previous messageGo to next message
John Watson
Messages: 7265
Registered: January 2010
Location: Global Village
Senior Member
When you say "group" do you mean replication groups? As defined with Multi-Master or Materialized View Replication? If so, it will be all managed for you. Just configure it, and trust Uncle Oracle to look after it. No need for you to issue any TRUNCATEs or anything else.
Re: ROLLBACK usingTRUNCATE [message #658766 is a reply to message #658757] Mon, 26 December 2016 09:36 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Let say, I've pushed data for Group the group 1000 to the target database i.e. two tables, EMP and DEPT.

I've started TRUNCATE of EMP table and went sucessfully.

While deleting next table of the group i.e. DEPT, I've got some error.
Then why don't you TRUNCATE the tables ONLY AFTER ALL tables have been pushed?

Re: ROLLBACK usingTRUNCATE [message #658798 is a reply to message #658766] Tue, 27 December 2016 08:07 Go to previous messageGo to next message
pointers
Messages: 439
Registered: May 2008
Senior Member
Thank you for your response Micheal.

I could not because, if TRUNCATE raise an error, I will not be able to rollback.
i.e. let say, I've 3 tables in group 1000, all the three tables pushed from source database to target database.

In the second stage, I started truncating source tables one by one, first table truncate was successful. so it does internal COMMIT. And the second TRUNCATE raised an error( ideally no error but just in case) in which case I would like to ROLLBACK the complete 3 tables rollback which is not possible because of the prior implicit COMMIT.

Regards,
Pointers
Re: ROLLBACK usingTRUNCATE [message #658799 is a reply to message #658798] Tue, 27 December 2016 08:30 Go to previous messageGo to next message
BlackSwan
Messages: 25858
Registered: January 2009
Location: SoCal
Senior Member
in SQL there are only two ways to remove data, TRUNCATE or DELETE.
You are free to use either.
We can't change how SQL commands behave.
So pick your poison.
Re: ROLLBACK usingTRUNCATE [message #658810 is a reply to message #658798] Tue, 27 December 2016 10:01 Go to previous message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
And the second TRUNCATE raised an error( ideally no error but just in case) in which case I would like to ROLLBACK the complete 3 tables rollback which is not possible because of the prior implicit COMMIT.
It is very less likely you have an error on a TRUNCATE unless you did not think of what you do.
As Blackswan said: if you don't want to use TRUNCATE then you have to use DELETE, which has far more reasons to fail.


Previous Topic: ORA-01426 "Numeric Overflow"
Next Topic: display all numbers with like
Goto Forum:
  


Current Time: Sun Feb 25 04:24:03 CST 2018

Total time taken to generate the page: 0.01353 seconds