Monday, June 02, 2014

SHUTDOWN TRANSACTIONAL

If you run this command, Oracle waits until all users have committed or rolled back any outstanding transactions before closing the database. To test this out in Oracle 12.1, I tried to close a database from a session with an outstanding transaction. This failed with an ORA-01097. Once I had committed the transaction, I was able to close the database successfully:

SQL> show user
USER is "SYS"
SQL> create table tab1(col1 number)
  2  /
 
Table created.
 
SQL> insert into tab1 values(1)
  2  /
 
1 row created.
 
SQL> shutdown transactional
ORA-01097: cannot shutdown while in a transaction - commit or rollback first
SQL> commit
  2  /
 
Commit complete.
 
SQL> shutdown transactional
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
 
Total System Global Area 1720328192 bytes
Fixed Size                  2403496 bytes
Variable Size            1056965464 bytes
Database Buffers          654311424 bytes
Redo Buffers                6647808 bytes
Database mounted.
Database opened.
SQL>
 
For the next example, I logged in as user ANDREW, created a table, inserted a row but did not commit it:
 
SQL> show user
USER is "ANDREW"
SQL> create table tab1(col1 number)
  2  /
 
Table created.
 
SQL> insert into tab1 values(1)
  2  /
 
1 row created.
 
SQL>
 
When user SYS tried to close the database, nothing happened:
 
SQL> show user
USER is "SYS"
SQL> shutdown transactional
 
After a while, user ANDREW rolled back his transaction:
 
SQL> show user
USER is "ANDREW"
SQL> create table tab1(col1 number)
  2  /
 
Table created.
 
SQL> insert into tab1 values(1)
  2  /
 
1 row created.
 
SQL> rollback
  2  /
 
Rollback complete.
 
SQL>
 
… then the database closed down a few seconds later:
 
SQL> show user
USER is "SYS"
SQL> shutdown transactional
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>


No comments: