| ROLLBACK usingTRUNCATE [message #658757] |
Mon, 26 December 2016 03:07  |
pointers
Messages: 451
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  |
John Watson
Messages: 8929
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 #658798 is a reply to message #658766] |
Tue, 27 December 2016 08:07 |
pointers
Messages: 451
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 #658810 is a reply to message #658798] |
Tue, 27 December 2016 10:01 |
 |
Michel Cadot
Messages: 68641
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
