DDL (synonym) rollback
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: DDL (synonym) rollback

  1. #1
    Join Date
    Feb 2005
    Posts
    1

    Question 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?

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width