Wednesday, March 14, 2018

How to get deleted the records even after commit?

In Oracle, there is concept called flash back query. Using this we are able to get the data back into table.

Scripts:

/*Step 1: */

DROP TABLE STUDENT_TEST;

/*Step 2:*/
CREATE TABLE STUDENT_TEST
(SID NUMBER,
SNAME VARCHAR2(100));

/*Step 3:*/

INSERT INTO STUDENT_TEST VALUES(1,'TRINESH');
INSERT INTO STUDENT_TEST VALUES(2,'SHANTHI');
INSERT INTO STUDENT_TEST VALUES(3,'TEJESH');
INSERT INTO STUDENT_TEST VALUES(4,'VASAVI');
INSERT INTO STUDENT_TEST VALUES(5,'LAKSHMI');
COMMIT;

/*Step 4:*/
DELETE FROM STUDENT_TEST;
COMMIT;

/*Step 5:*/
SELECT * FROM STUDENT_TEST;

/*Step 6:*/
INSERT INTO STUDENT_TEST
SELECT * FROM STUDENT_TEST AS OF TIMESTAMP TO_TIMESTAMP('15-MAR-2018 03:41:01.750');
COMMIT;

/*Step 7:*/
SELECT * FROM STUDENT_TEST;

At Step 4 we are deleting all the records and commit. Many of us think that if we delete the records and commit it is impossible to get the data back. But there is flexibility that we can get the data back.

At Step 5: we are cross checking whether really deleted or not.

At Step 6: We are re-inserting records back to table using flash back query.
[Note: We have to specify the timestamp at what time you need to go back and get the data]

At Step 7: We are able to see our records back.

Output:







FinalOutput:

No comments:

Post a Comment