DDL (synonym) rollback

Oracle Version: 9i

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

STEP 1:
SELECT TABLE_NAME INTO vBkupTableName FROM USER_SYNONYMS
WHERE
SYNONYM_NAME = 'USER_TABLE2';

STEP 2:
SELECT TABLE_NAME INTO vMainTableName FROM USER_SYNONYMS
WHERE
SYNONYM_NAME = 'USER_TABLE1';

STEP 3:
-- Change the Backup table as Main Table
EXECUTE IMMEDIATE
'CREATE OR REPLACE SYNONYM USER_TABLE1 FOR ' ||
vBkupTableName;

STEP 4:
-- Change the Main table as Backup Table
EXECUTE IMMEDIATE
'CREATE OR REPLACE SYNONYM USER_TABLE2 FOR ' ||
vMainTableName;


we have common exception block.

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?

DDL cannot be rolled back.

In your case, I think, SYNONYM may not be right choice.

"ALTER TABLE rename ..." may be the right choice.

Tamil