Working with the Oracle Flashback option we can restore the old data using flashback queries. Introduced in Oracle Version 9.2 the “AS OF” clause can be used to:<\/p>\n
Mistakenly set the salary of Manager King to a wrong value: SQL> select ename, sal from emp where ename=’KING’; We can query the old value again using the “AS OF” clause and specifying a time interval:<\/p>\n Issuing this query will tell us the correct value as it was existing 15 minutes before.<\/em><\/p>\n SELECT ename, sal FROM emp ENAME SAL Now we can use flashback query as a sub query in an update statement to restore back to the old value. The command is as follows:<\/p>\n update emp In case you are going to use this for testing purposes, you might find it usefully to record the referencing SCN and use flashback query in combination with the recorded SCN.<\/p>\n SQL> select dbms_flashback.get_system_change_number from dual; Now changing a value on purpose:<\/p>\n SQL> update emp set sal=4300 where ename=’KING’; SQL> commit; SQL> select ename, sal from emp where ename=’KING’; Here is the required query:<\/p>\n SELECT ename, sal FROM emp ENAME SAL And you can restore the old value by updating the row or better you directly restore the old value using the following update with flashback sub query:<\/p>\n update emp Working with the Oracle Flashback option we can restore the old data using flashback queries. Introduced in Oracle Version 9.2 the “AS OF” clause can be used to: find the old data restore the old data Mistakenly set the salary of Manager King to a wrong value: SQL> update emp set sal=4500 where ename=’KING’; 1 […]<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,5,31],"tags":[191,189,190,257,192,42],"class_list":["post-203","post","type-post","status-publish","format-standard","hentry","category-database","category-oracle","category-tipsntricks","tag-as-of","tag-flashback-option","tag-flashback-query","tag-oracle","tag-select","tag-update"],"_links":{"self":[{"href":"https:\/\/www.askthegerman.com\/index.php?rest_route=\/wp\/v2\/posts\/203","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.askthegerman.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.askthegerman.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.askthegerman.com\/index.php?rest_route=\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.askthegerman.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=203"}],"version-history":[{"count":7,"href":"https:\/\/www.askthegerman.com\/index.php?rest_route=\/wp\/v2\/posts\/203\/revisions"}],"predecessor-version":[{"id":351,"href":"https:\/\/www.askthegerman.com\/index.php?rest_route=\/wp\/v2\/posts\/203\/revisions\/351"}],"wp:attachment":[{"href":"https:\/\/www.askthegerman.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=203"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.askthegerman.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=203"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.askthegerman.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=203"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\n
\nSQL> update emp set sal=4500 where ename=’KING’;
\n1 row updated.
\nSQL> commit;
\nCommit complete.<\/em><\/p>\n
\nENAME SAL
\n———- ———-
\nKING 4500<\/em><\/p>\n
\nAS OF TIMESTAMP (SYSTIMESTAMP – INTERVAL ’15’ MINUTE)
\nWHERE ename = ‘KING’;<\/em><\/p>\n
\n———- ———-
\nKING 5000<\/em><\/p>\n
\nset sal = (
\nSELECT sal FROM emp
\nAS OF TIMESTAMP (SYSTIMESTAMP – INTERVAL ’15’ MINUTE) where ename=’KING’)
\nwhere ename=’KING’;<\/em><\/p>\n
\nGET_SYSTEM_CHANGE_NUMBER
\n————————
\n 4312233<\/em><\/p>\n
\n1 row updated.<\/em><\/p>\n
\nCommit complete.<\/em><\/p>\n
\nENAME SAL
\n———- ———-
\nKING 4300
\n<\/em>
\nThen you might not know the previous salary of MILLER but you do know the SCN when it refered to the old value and the original value can be updated and restored with flashback query by using the SCN noted prior to the update statement.<\/p>\n
\nAS OF SCN 4312233
\nwhere ename=’KING’;<\/em><\/p>\n
\n———- ———-
\nKING 4300<\/em><\/p>\n
\nset sal = (
\nSELECT sal FROM emp
\nAS OF SCN 4312233 where ename=’KING’)
\nwhere ename=’KING’;<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"