title: A simple scenario in which the effect of the ROLLBACK command can not be observed in Oracle 9i
body: <p>Suppose, on the SQL prompt in Oracle 9i, I'm creating a simple table say <strong>orderStatus</strong> with only two columns with the following CREATE SQL command.</p>
  
  <pre><code>CREATE TABLE orderStatus(statusID varchar2(6) primary key, status varchar2(15));
  </code></pre>
  
  <hr>
  
  <p>just then I'm entering four rows with the following INSERT INTO commands.</p>
  
  <pre><code>INSERT INTO orderStatus VALUES("S0001", "Fulfilled");
  
  INSERT INTO orderStatus VALUES("S0002", "Back order");
  
  INSERT INTO orderStatus VALUES("S0003", "In process");
  
  INSERT INTO orderStatus VALUES("S0004", "Cancelled");
  </code></pre>
  
  <hr>
  
  <p>On successful execution of the above commands, I'm issuing a COMMIT command to complete the current transaction.</p>
  
  <pre><code>COMMIT;
  </code></pre>
  
  <hr>
  
  <p>just then I need to fire the following DELETE command.</p>
  
  <pre><code>DELETE FROM orderStatus WHERE statusID="S0004";
  </code></pre>
  
  <p>which deletes <strong>one</strong> row with the statusID S0004. The table now contains only 3 rows.</p>
  
  <hr>
  
  <p>Suppose, I need to add the NOT NULL constraint to the status column which requires the following ALTER TABLE command.</p>
  
  <pre><code>ALTER TABLE orderStatus MODIFY(status varchar(15) NOT NULL);
  </code></pre>
  
  <p>The table orderStatus will be altered to add a NOT NULL constraint to the status column.</p>
  
  <hr>
  
  <p>Now, suppose I execute a ROLLBACK command on the SQL prompt which causes the current transaction to finish with all the data which are previously affected in this table to be undone. With this, the previously deleted row with the DELETE command above should be undone and the table should now contain the original four rows but the effect of ROLLBACK can not be observed and the table would have only three rows. Why does this happen?</p>