{"id":9134,"date":"2019-05-15T14:09:44","date_gmt":"2019-05-15T14:09:44","guid":{"rendered":"https:\/\/ittutorial.org\/?p=9134"},"modified":"2020-03-29T14:11:07","modified_gmt":"2020-03-29T14:11:07","slug":"flashback-query-version-in-oracle-database","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/flashback-query-version-in-oracle-database\/","title":{"rendered":"Flashback Query Version in Oracle Database"},"content":{"rendered":"<p>Hi,<\/p>\n<p>I will explain what is the Flashback Query\u00a0Version feature in Oracle in this article.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-9139\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/05\/Flashback-Version-Query.jpg\" alt=\"\" width=\"637\" height=\"415\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/05\/Flashback-Version-Query.jpg 637w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/05\/Flashback-Version-Query-300x195.jpg 300w\" sizes=\"auto, (max-width: 637px) 100vw, 637px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><!--more--><\/p>\n\n<p>&nbsp;<\/p>\n<p>Read previous article before this.<\/p>\n<blockquote class=\"wp-embedded-content\" data-secret=\"07UddED785\"><p><a href=\"https:\/\/ittutorial.org\/flashback-oracle-architecture-flashback-usage\/\">Oracle Flashback Architecture<\/a><\/p><\/blockquote>\n<p><iframe loading=\"lazy\" class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; clip: rect(1px, 1px, 1px, 1px);\" title=\"&#8220;Oracle Flashback Architecture&#8221; &#8212; IT Tutorial\" src=\"https:\/\/ittutorial.org\/flashback-oracle-architecture-flashback-usage\/embed\/#?secret=X5y90ntI6Z#?secret=07UddED785\" data-secret=\"07UddED785\" width=\"600\" height=\"338\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe><\/p>\n<p>&nbsp;<\/p>\n<p>Flashback version query let you versions of a specific row to be tracked during\u00a0a specified time period.\u00a0You need to use VERSIONS BETWEEN clause to enable\u00a0Flashback version query feature like following.<\/p>\n<p>&nbsp;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-9139\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/05\/Flashback-Version-Query.jpg\" alt=\"\" width=\"637\" height=\"415\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/05\/Flashback-Version-Query.jpg 637w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/05\/Flashback-Version-Query-300x195.jpg 300w\" sizes=\"auto, (max-width: 637px) 100vw, 637px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>Let&#8217;s go to make an example about Flashback version Query to learn this feature very well. I will create sample table and insert Test data , then I will track versions of a specific row via Flashback version Query.<\/p>\n<p>&nbsp;<\/p>\n\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>Create table and insert data.<\/p>\n<pre>SQL&gt; Create table MEHMET.SALIH(id NUMBER,name VARCHAR2(20));\r\n\r\nTable created.\r\n\r\nSQL&gt; insert into MEHMET.SALIH values(1,'Deveci');\r\n\r\n1 row created.\r\n\r\nSQL&gt; commit;\r\n\r\nCommit complete.\r\n\r\n<\/pre>\n<p>Query Current SCN of Database.<\/p>\n<p>&nbsp;<\/p>\n<pre>SQL&gt; Select current_scn from v$database;\r\n\r\nCURRENT_SCN\r\n-----------\r\n12209868\r\n\r\nSQL&gt;<\/pre>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>And perform some DML (Insert,Update,Delete) on this table.<\/p>\n<pre>SQL&gt; insert into MEHMET.SALIH values(2,'SALIH');\r\n\r\n1 row created.\r\n\r\nSQL&gt; commit;\r\n\r\nCommit complete.\r\n\r\n\r\n\r\nSQL&gt; Update MEHMET.SALIH SET name='Mehmet' where id=1;\r\n\r\n1 row updated.\r\n\r\nSQL&gt; commit;\r\n\r\nCommit complete.\r\n\r\n\r\n\r\nSQL&gt; Delete MEHMET.SALIH where id=2;\r\n\r\n1 row deleted.\r\n\r\nSQL&gt; commit;\r\n\r\nCommit complete.\r\n\r\n\r\n\r\nSQL&gt; insert into MEHMET.SALIH values(1,'Deveci');\r\n\r\n1 row created.\r\n\r\nSQL&gt; commit;\r\n\r\nCommit complete.<\/pre>\n<p>&nbsp;<\/p>\n<p>Query Current SCN again.<\/p>\n<p>&nbsp;<\/p>\n<pre>SQL&gt; Select current_scn from v$database;\r\n\r\nCURRENT_SCN\r\n-----------\r\n12209893<\/pre>\n<p>&nbsp;<\/p>\n<p>Query table now.<\/p>\n<pre>SQL&gt; select * from MEHMET.SALIH;\r\n\r\nID NAME\r\n---------- --------------------\r\n1 Mehmet\r\n1 Deveci<\/pre>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>Lets go to see changes of table like following.<\/p>\n<p>&nbsp;<\/p>\n<pre>SQL&gt; select versions_startscn, versions_starttime,\r\nversions_endscn, versions_endtime,\r\nversions_xid, versions_operation from MEHMET.SALIH <span style=\"color: #ff0000;\">VERSIONS BETWEEN SCN 12209868 AND 12209893<\/span>;\r\n\r\n\r\n\r\n<img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-9138\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/05\/Flashback-Version-Query2.jpg\" alt=\"\" width=\"1341\" height=\"502\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/05\/Flashback-Version-Query2.jpg 1341w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/05\/Flashback-Version-Query2-300x112.jpg 300w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/05\/Flashback-Version-Query2-768x287.jpg 768w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/05\/Flashback-Version-Query2-1024x383.jpg 1024w\" sizes=\"auto, (max-width: 1341px) 100vw, 1341px\" \/><\/pre>\n<pre><\/pre>\n<p>You can see all operations start time and end time and also operation type (Insert, Delete, Update etc.)<\/p>\n<pre>\r\n<\/pre>\n<p>&nbsp;<\/p>\n<h5><span style=\"color: #ff0000;\">Do you want to learn Oracle Database for Beginners, then read the following articles.<\/span><\/h5>\n<p><a href=\"https:\/\/ittutorial.org\/oracle-database-19c-tutorials-for-beginners\/\">https:\/\/ittutorial.org\/oracle-database-19c-tutorials-for-beginners\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Hi, I will explain what is the Flashback Query\u00a0Version feature in Oracle in this article. &nbsp;<\/p>\n","protected":false},"author":1,"featured_media":9139,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[5239,1994],"tags":[4315,4320,4317,2672,4301,485,2747,508,4300,4295,4306,638,4297,4322,4345,4344,4296,4310,4311,4316,4292,4299,4302,4298,2473,4323,4294,2671,2674,1031,4319,4318,1267,1268,4290,4304,4305,1269,1270,4312,1271,4314,4303,1272,1345,2673,2686,1515,4308,4321,2620,4309,4313,4291,4307,4293],"class_list":["post-9134","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-flashback","category-oracle","tag-alter-table-table_name-enable-row-movement","tag-as-of-scn","tag-as-of-timestamp","tag-database-tutorial","tag-db_flashback_retention_target","tag-db_recovery_file_dest","tag-db_recovery_file_dest_size","tag-deveci","tag-flashback-data-archive","tag-flashback-database","tag-flashback-database-oracle","tag-flashback-ne-ise-yarar","tag-flashback-query","tag-flashback-query-in-oracle-database","tag-flashback-query-version","tag-flashback-query-version-in-oracle-database","tag-flashback-table","tag-flashback-table-in-oracle-database","tag-flashback-table-oracle","tag-flashback-table-to-scn","tag-flashback-technology-oracle","tag-flashback-transaction-query","tag-flashback-usage-oracle","tag-flashback-version-query","tag-goldengate-tutorial","tag-how-to-query-past-in-oracle","tag-how-to-use-flashback","tag-it-tutorial","tag-linux-tutorial","tag-mehmet-salih-deveci","tag-oracle-as-of-scn","tag-oracle-as-of-timestamp","tag-oracle-egitimi","tag-oracle-flashback","tag-oracle-flashback-architecture","tag-oracle-flashback-database","tag-oracle-flashback-database-feature","tag-oracle-flashback-ne-icin-kullanilir","tag-oracle-flashback-nedir","tag-oracle-flashback-table","tag-oracle-flashback-teknolojisi","tag-oracle-flashback-tutorial","tag-oracle-flashback-usage","tag-oracle-flashback-veritabanini-ileriye-geriye-sarma","tag-oracle-support","tag-oracle-tutorial","tag-programming-language-tutorial","tag-salih-deveci","tag-scn-system-change-number","tag-show-parameter-undo_retention","tag-sql-server-tutorial","tag-system-change-number","tag-table-flashback-oracle","tag-what-is-oracle-flashback","tag-what-is-scn","tag-what-is-the-flashback-in-oracle"],"aioseo_notices":[],"jetpack_featured_media_url":"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/05\/Flashback-Version-Query.jpg","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/9134","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/comments?post=9134"}],"version-history":[{"count":4,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/9134\/revisions"}],"predecessor-version":[{"id":12556,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/9134\/revisions\/12556"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media\/9139"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=9134"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=9134"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=9134"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}