Wrong DML: what to do when data is deleted by mistake.In Oracle the rolling back a transaction is possible before the final commit or up to a savepoint, but the database offers another chance, which is probably little known, and it is the Flashback.The essential requirement is that the database is using UNDO tablespaces and that the undo_retention is high enough. Generally speaking, rolling back the changes is only possible if the correction is made not later than the number of seconds specified by the parameter undo_retention. SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 1800 undo_suppress_errors boolean FALSE undo_tablespace string UNDOTBS1In this example a table is created and populated
SQL> CREATE TABLE t_a (c1 number);
SQL> INSERT INTO t_a values (1);
SQL> INSERT INTO t_a values (2);
SQL> INSERT INTO t_a values (3);
1 row created.
SQL> INSERT INTO t_a values (4);
SQL> INSERT INTO t_a values (5);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> ALTER SESSION SET NLS_DATE_FORMAT ='hh24:mi:ss dd-mon-yyyy';
Session altered.
SQL> SELECT SYSDATE FROM DUAL;
SYSDATE
--------------------
12:53:43 31-dec-2004
SQL> DELETE t_a;
5 rows deleted
SQL> COMMIT
Commit completed
SQL> SELECT COUNT(*) FROM t_a;
COUNT(*)
----------
0
The deletion was a blunder and the problem is realized 20 minutes later; since the undo_retention is 1800 seconds (30 minutes) it is probably still possible to rescue the data using the close "AS OF TIMESTAMP" and specifying a time interval in the past.
SQL> SELECT SYSDATE FROM DUAL;
SYSDATE
--------------------
13:11:20 31-dec-2004
SQL> SELECT * FROM t_a
2 AS OF TIMESTAMP (sysdate - interval '30' minute);
C1
----------
1
2
3
4
5
By specifying SELECT AS OF TIMESTAMP we are still able to access the records that have been deleted, even if the table is now empty.
SQL> SELECT COUNT(*) FROM t_a;
COUNT(*)
----------
0
SQL> CREATE TABLE t_b AS (SELECT * FROM t_a AS OF TIMESTAMP (SYSDATE - INTERVAL '30' MINUTE);
Table created.
SQL> SELECT COUNT(*) FROM t_b;
COUNT(*)
----------
5
SQL> SELECT * FROM t_b;
C1
----------
1
2
3
4
5
It was therefore possible to restore the contents as they were 20 minutes before. If the table is dropped, unfortunately no rollback is possible.
SQL>
SQL> DROP TABLE t_a;
Table dropped.
SQL> CREATE TABLE t_c AS (SELECT * FROM t_a AS OF TIMESTAMP (SYSDATE - INTERVAL '40' MINUTE));
CREATE TABLE t_c AS (SELECT * FROM t_a AS OF TIMESTAMP (SYSDATE - INTERVAL '40' MINUTE))
*
ERROR at line 1:
ORA-00942: table or view does not exist
The Oracle Flashback is therefore one of the ways of restoring deleted or wrongly updated records; another tool is the Oracle LogMiner, which offers a graphical interface.
|
|
| [Home] [Web Design] [HTML tutorials] [Javascript] [PSP] [About us] [Links] [Anonymous email] [Best hosting] [Daily Oracle Life] [IT jobs in Switzerland] [Web Submission] [Web traffic] | |