CODEWITHSUNDEEP
sqloracle-databaseplsql
banana_99
banana_99

Reputation: 641

Oracle table with backup data

I have a package which I call like this:

BEGIN
    package.sp1();
    package.sp2();
END;

During this execution, a table is updated in procedures sp1 and sp2. However, I need to store a copy of that table as it was originally just in case something goes wrong and I need to restore the previous data. The data would only be copied on the first execution of each day. Then, the next day, the copied data would be used as a starting point to be updated in procedures sp1 and sp2.

How can I do that in another procedure?

This is what I want to do in pseudo-code:

BEGIN
    -- Fetch the data copied in yesterday's first execution
        package.sp1(); -- Updates the table
        package.sp2(); -- Also updates the table
    -- Store the updated data if this is the first execution of the day
END;

Upvotes: 0

Views: 146

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65433

You can leave it turning back to the previous day's data to the Oracle's Flashback Query mechanism provided that the version your database is 10g+ along with some conditions satisfied such as

  1. undo_retention parameter's value should be set at least to 86,400 which is in seconds, and equals to one day period.
  2. undo tablespace should be with autoextend enabled or retention guarantee is enabled for fixed-size but having big volume undo tablespace.

Then use this query ;

DELETE tab;
INSERT INTO tab
SELECT *
  FROM tab
    AS OF TIMESTAMP TRUNC(SYSTIMESTAMP);
COMMIT;

whenever something went wrong and needed to come back to the point in time for the data.

Upvotes: 1

Related Questions