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>