{"id":11921,"date":"2023-10-31T20:19:32","date_gmt":"2023-10-31T19:19:32","guid":{"rendered":"https:\/\/roglacup.com\/klaus62\/?p=11921"},"modified":"2023-11-01T18:53:06","modified_gmt":"2023-11-01T17:53:06","slug":"transactions","status":"publish","type":"post","link":"https:\/\/roglacup.com\/klaus62\/2023\/10\/31\/transactions\/","title":{"rendered":"Transactions"},"content":{"rendered":"\n<h3 class=\"wp-block-heading\"><\/h3>\n\n\n\n<p>Using SET TRANSACTION to begin a transaction is optional. A new transaction begins implicitly with the first DML statement that you execute after you make a database connection or with the first DML statement that you execute following a COMMIT or a ROLLBACK (or any DDL statement such as TRUNCATE). You need to use SET TRANSACTION only when you want transaction attributes such as READ ONLY that are not the default.<\/p>\n\n\n\n<p>Posted from: <a href=\"https:\/\/www.oreilly.com\/library\/view\/oracle-sqlplus-the\/0596007469\/ch04s02.html\">https:\/\/www.oreilly.com\/library\/view\/oracle-sqlplus-the\/0596007469\/ch04s02.html<\/a><\/p>\n\n\n\n<h5 class=\"wp-block-heading\" id=\"CNCPT-GUID-E3FB3DC3-3317-4589-BADD-D89A3547F87D\">Beginning of a Transaction<\/h5>\n\n\n\n<p>A transaction begins when the first executable SQL statement is encountered.<\/p>\n\n\n\n<p>An&nbsp;<a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/19\/cncpt\/Chunk520365104.html#GUID-2F2F02A4-2405-4A0A-ABAF-91DB48DD4B82\">executable SQL statement<\/a>&nbsp;is a SQL statement that generates calls to a&nbsp;<a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/19\/cncpt\/Chunk520365104.html#GUID-CFB1A30E-76B6-44EA-839E-9E63C8DA31AC\">database instance<\/a>, including DML and DDL statements and the&nbsp;<code>SET TRANSACTION<\/code>&nbsp;statement.<\/p>\n\n\n\n<p>When a transaction begins, Oracle Database assigns the transaction to an available\u00a0<a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/19\/cncpt\/Chunk520365104.html#GUID-297B963A-989C-4720-B061-A2352FF72892\">undo data<\/a>\u00a0segment to record the undo entries for the new transaction. A transaction ID is not allocated until an undo segment and\u00a0<a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/19\/cncpt\/Chunk520365104.html#GUID-BFF71130-2760-4C64-8829-7772C803FBE7\">transaction table<\/a>\u00a0slot are allocated, which occurs during the first DML statement. A transaction ID is unique to a transaction and represents the undo segment number, slot, and sequence number.<\/p>\n\n\n\n<h5 class=\"wp-block-heading\" id=\"CNCPT-GUID-510DF997-BA9C-4431-95EF-A0622881EC9C\">End of a Transaction<\/h5>\n\n\n\n<p>A transaction can end under different circumstances.<\/p>\n\n\n\n<p>A transaction ends when any of the following actions occurs:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>A user issues a&nbsp;<code>COMMIT<\/code>&nbsp;or&nbsp;<code>ROLLBACK<\/code>&nbsp;statement&nbsp;<em>without<\/em>&nbsp;a&nbsp;<code>SAVEPOINT<\/code>&nbsp;clause.In a&nbsp;<a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/19\/cncpt\/Chunk520365104.html#GUID-0680EB2C-ADF4-431A-A259-FB2227E5AA93\">commit<\/a>, a user explicitly or implicitly requested that the changes in the transaction be made permanent. Changes made by the transaction are permanent and visible to other users only after a transaction commits.<\/li>\n\n\n\n<li>A user runs a DDL command such as&nbsp;<code>CREATE<\/code>,&nbsp;<code>DROP<\/code>,&nbsp;<code>RENAME<\/code>, or&nbsp;<code>ALTER<\/code>.The database issues an implicit&nbsp;<code>COMMIT<\/code>&nbsp;statement before and after every DDL statement. If the current transaction contains DML statements, then Oracle Database first commits the transaction and then runs and commits the DDL statement as a new, single-statement transaction.<\/li>\n\n\n\n<li>A user exits normally from most Oracle Database utilities and tools, causing the current transaction to be implicitly committed. The commit behavior when a user disconnects is application-dependent and configurable.Note:Applications should always explicitly commit or undo transactions before program termination.<\/li>\n\n\n\n<li>A client process terminates abnormally, causing the transaction to be implicitly rolled back using metadata stored in the transaction table and the undo segment.<\/li>\n<\/ul>\n\n\n\n<p>After one transaction ends, the next executable SQL statement automatically starts the following transaction. The following example executes an\u00a0<code>UPDATE<\/code>\u00a0to start a transaction, ends the transaction with a\u00a0<code>ROLLBACK<\/code>\u00a0statement, and then executes an\u00a0<code>UPDATE<\/code>\u00a0to start a new transaction (note that the transaction IDs are different):<\/p>\n\n\n\n<p>Posted from<\/p>\n\n\n\n<p>In which three situations does a new transaction always start? (Choose three.)<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>A.\u00a0when issuing a SELECT FOR UPDATE statement after a CREATE TABLE AS SELECT statement was issued in the same session<\/strong><\/li>\n\n\n\n<li>B.&nbsp;when issuing a TRUNCATE statement after a SELECT statement was issued in the same session<\/li>\n\n\n\n<li>C.\u00a0when issuing a CREATE TABLE statement after a SELECT statement was issued in the same sessio<strong>n<\/strong><\/li>\n\n\n\n<li><strong>D.\u00a0when issuing the first Data Manipulation Language (DML) statement after a COMMIT OR ROLLBACK statement was issued in the same session<\/strong><\/li>\n\n\n\n<li>E.&nbsp;when issuing a CREATE INDEX statement after a CREATE TABLE statement completed successfully in the same session<\/li>\n\n\n\n<li><strong>F.\u00a0when issuing a DML statement after a DML statement failed in the same session<\/strong><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Using SET TRANSACTION to begin a transaction is optional. A new transaction begins implicitly with the first DML statement that you execute after you make a database connection or with the first DML statement that you execute following a COMMIT or a ROLLBACK (or any DDL statement such as TRUNCATE). You need to use SET&hellip;&nbsp;<a href=\"https:\/\/roglacup.com\/klaus62\/2023\/10\/31\/transactions\/\" class=\"\" rel=\"bookmark\">Read More &raquo;<span class=\"screen-reader-text\">Transactions<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"","neve_meta_content_width":0,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":"","footnotes":""},"categories":[1299],"tags":[1316],"class_list":["post-11921","post","type-post","status-publish","format-standard","hentry","category-1z0-082","tag-transactions"],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/roglacup.com\/klaus62\/wp-json\/wp\/v2\/posts\/11921","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/roglacup.com\/klaus62\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/roglacup.com\/klaus62\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/roglacup.com\/klaus62\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/roglacup.com\/klaus62\/wp-json\/wp\/v2\/comments?post=11921"}],"version-history":[{"count":1,"href":"https:\/\/roglacup.com\/klaus62\/wp-json\/wp\/v2\/posts\/11921\/revisions"}],"predecessor-version":[{"id":11922,"href":"https:\/\/roglacup.com\/klaus62\/wp-json\/wp\/v2\/posts\/11921\/revisions\/11922"}],"wp:attachment":[{"href":"https:\/\/roglacup.com\/klaus62\/wp-json\/wp\/v2\/media?parent=11921"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/roglacup.com\/klaus62\/wp-json\/wp\/v2\/categories?post=11921"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/roglacup.com\/klaus62\/wp-json\/wp\/v2\/tags?post=11921"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}