Results 1 to 4 of 4

Thread: Rollback DDL

  1. #1
    Join Date
    Jan 2002
    Location
    India
    Posts
    15

    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

    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;


    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
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    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.

    Alan

  3. #3
    Join Date
    Jan 2002
    Location
    India
    Posts
    15
    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

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    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?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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