var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Rollback DDL
We have 2 tables namely USER_table1 and USER_table2. Both contains same structure.
At any time any one of the table will serve as main table and other
as backup table.
Data selection will always be with main table
Data manipulation (insert/update/delete) will always take place in backup table
We are acheiving this using synonym.
Once Data manipulation is sucess we swap the 2 tables
i.e. backup table become main table and main table become backup table
SELECT TABLE_NAME INTO vBkupTableName FROM USER_SYNONYMS
SYNONYM_NAME = 'USER_TABLE2';
SELECT TABLE_NAME INTO vMainTableName FROM USER_SYNONYMS
SYNONYM_NAME = 'USER_TABLE1';
-- Change the Backup table as Main Table
'CREATE OR REPLACE SYNONYM USER_TABLE1 FOR ' ||
-- Change the Main table as Backup Table
'CREATE OR REPLACE SYNONYM USER_TABLE2 FOR ' ||
In worst failure cases, if step 4 fails we have to revert back the step3 also.
Since the statements are DDL, how can we rollback? Please suggest on this?
You could catch the exception and recreate the synonym to point to the right place.
On the other hand why are you doing this stuff in the first place, whats the point of it? After all there is a point in time when both synonyms point to the same table.
Currently I am doing the above suggested solution only, but if think hypothetically it is never ending catch block. I need both the steps (step 3 and step4 ) to be either success or failure.
I have some other procedure which clean-up the backup table after a time interval
Why are you even swapping the tables. Simply have one table named USER_TABLE1 and have a synonym named USER_TABLE2 pointing to USER_TABLE1. All you selects, updates, and deletes will go against the same table. What possible reason do you have to keep swapping them back and forth?
You do not need a parachute to skydive. You only need a parachute to skydive twice.