{"id":10576,"date":"2019-07-18T14:35:09","date_gmt":"2019-07-18T14:35:09","guid":{"rendered":"https:\/\/ittutorial.org\/?p=10576"},"modified":"2019-07-18T14:37:21","modified_gmt":"2019-07-18T14:37:21","slug":"oracle-sql-tutorials-chapter-6-part-2-of-2","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/oracle-sql-tutorials-chapter-6-part-2-of-2\/","title":{"rendered":"Oracle SQL Tutorials \u2013 Chapter 6 (Part 2 of 2)"},"content":{"rendered":"<p><strong>DATABASE TRANSACTIONS<\/strong><\/p>\n<ul>\n<li>DB transaction is start with any of them;<\/li>\n<li>When DML command is running.<\/li>\n<li>When DDL command is running.<\/li>\n<li>When DCL command is running.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-10559\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-138.png\" width=\"372\" height=\"265\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-138.png 528w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-138-300x214.png 300w\" sizes=\"auto, (max-width: 372px) 100vw, 372px\" \/><\/p>\n<ul>\n<li>Transaction is started when a DML command is executed. And ;<\/li>\n<li>Ends with one of them :<\/li>\n<li>When commit or Rollback is occurred.<\/li>\n<li>A DDL or DCL command is executed ( Autocommit).<\/li>\n<li>When user logouts.<\/li>\n<li>When an error occurs in the system<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p><strong>COMMIT &amp; ROLLBACK<\/strong><\/p>\n<ul>\n<li><strong>ROLLBACK : <\/strong>Provides reverting data changes.<\/li>\n<li><strong>COMMIT :<\/strong> It makes the changes permanent.<\/li>\n<li>These commands help you to track data changes.<\/li>\n<li>It is provided to session level control of changes made.<\/li>\n<li>They provide a safety work area.<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><strong>SAVEPOINT<\/strong><\/p>\n<ul>\n<li>The SAVEPOINT command is used to put a save point.<\/li>\n<li>The ROLLBACK[] command is used to return to created save point.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"506\" height=\"361\" class=\"wp-image-10560\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-139.png\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-139.png 506w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-139-300x214.png 300w\" sizes=\"auto, (max-width: 506px) 100vw, 506px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><strong>BEFORE COMMIT OR ROLLBACK<\/strong><\/p>\n<ul>\n<li>The owner of the transaction can check the data changed after executing a DML, using SELECT command.<\/li>\n<li>Other users can not see the results of transactions that the current user did.<\/li>\n<li>Affected rows are locked.<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p><strong>AFTER COMMIT<\/strong><\/p>\n<ul>\n<li>Data changes in the database are permanently applied.<\/li>\n<li>The previous state of the data is lost.<\/li>\n<li>All user can see the new data.<\/li>\n<li>The affected rows are unlocked.<\/li>\n<li>All savepoints are deleted.<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><strong>COMMIT EXAMPLE<\/strong><\/p>\n<ul>\n<li>I will delete a row from hr.jobs table but I will not commit this transaction. Then I will check this table with another user. This user will see the previous state of the data, because of not committed.<\/li>\n<\/ul>\n<p>First State :<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"449\" height=\"172\" class=\"wp-image-10561\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-140.png\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-140.png 449w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-140-300x115.png 300w\" sizes=\"auto, (max-width: 449px) 100vw, 449px\" \/><\/p>\n<pre>DELETE FROM hr.jobs WHERE job_id='AD_PRES';<\/pre>\n<p>Current user see like that;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"451\" height=\"151\" class=\"wp-image-10562\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-141.png\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-141.png 451w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-141-300x100.png 300w\" sizes=\"auto, (max-width: 451px) 100vw, 451px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>But other users see like that; There is still \u2018AD_PRES\u2019 row. Because we didn\u2019t commit.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"449\" height=\"172\" class=\"wp-image-10563\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-142.png\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-142.png 449w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-142-300x115.png 300w\" sizes=\"auto, (max-width: 449px) 100vw, 449px\" \/><\/p>\n<p>Now I will commit, so changes will be permanent<\/p>\n<pre>COMMIT ;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"451\" height=\"151\" class=\"wp-image-10564\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-143.png\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-143.png 451w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-143-300x100.png 300w\" sizes=\"auto, (max-width: 451px) 100vw, 451px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><strong>AFTER ROLLBACK<\/strong><\/p>\n<ul>\n<li>We use ROLLBACK to undo work done in the current session.<\/li>\n<li>The data returns to the previous state.<\/li>\n<li>Changed records are released, locks removed.<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p><strong>ROLLBACK EXAMPLE<\/strong><\/p>\n<ul>\n<li>I will delete all rows of the hr.job_history table. Then I will rollback this transaction. Let\u2019s see what happens.<\/li>\n<\/ul>\n<p>First State of the hr.job_history table :<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"480\" height=\"175\" class=\"wp-image-10565\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-144.png\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-144.png 480w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-144-300x109.png 300w\" sizes=\"auto, (max-width: 480px) 100vw, 480px\" \/><\/p>\n<ul>\n<li>Then, I will delete all rows of this table.<\/li>\n<\/ul>\n<pre>DELETE FROM hr.job_history;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"408\" height=\"209\" class=\"wp-image-10566\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-145.png\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-145.png 408w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-145-300x154.png 300w\" sizes=\"auto, (max-width: 408px) 100vw, 408px\" \/><\/p>\n<pre>ROLLBACK ;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"480\" height=\"175\" class=\"wp-image-10567\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-146.png\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-146.png 480w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-146-300x109.png 300w\" sizes=\"auto, (max-width: 480px) 100vw, 480px\" \/><\/p>\n<ul>\n<li>When I ROLLBACK the uncommited transaction, unmodified data is retrieved from the Undo Tablespace.<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><strong>FOR UPDATE<\/strong><\/p>\n<ul>\n<li>It locks selected records, so that other users cannot change it.<\/li>\n<li>If more than one table is contained in a join, all selected records are locked in both tables.<\/li>\n<li>Records are released at ROLLBACK or COMMIT.<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p><strong>FOR UPDATE EXAMPLE<\/strong><\/p>\n<ul>\n<li>I will select hr.employees table with FOR UPDATE option.<\/li>\n<\/ul>\n<pre>SELECT * FROM hr.employees FOR UPDATE;\r\n\r\n<\/pre>\n<ul>\n<li>Then, I\u2019m trying to delete from this table with another user but doesn\u2019t allow me to do that.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"294\" height=\"197\" class=\"wp-image-10568\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-147.png\" \/><\/p>\n","protected":false},"excerpt":{"rendered":"<p>DATABASE TRANSACTIONS DB transaction is start with any of them; When DML command is running. When DDL command is running. When DCL command is running. Transaction is started when a DML command is executed. And ; Ends with one of them : When commit or Rollback is occurred. A DDL or DCL command is executed &hellip;<\/p>\n","protected":false},"author":29,"featured_media":0,"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":[3019],"tags":[],"class_list":["post-10576","post","type-post","status-publish","format-standard","","category-oracle-sql"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/10576","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\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/comments?post=10576"}],"version-history":[{"count":2,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/10576\/revisions"}],"predecessor-version":[{"id":10579,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/10576\/revisions\/10579"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=10576"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=10576"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=10576"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}