This post demonstrates that if a database has an unfinished transaction and you try to run the above-mentioned command, it will hang. I logged into an Oracle 11 database, started a transaction but did not COMMIT it in the red session below:
PQEDPT1 /export/home/oracle > sqlplus /
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jun 8 14:55:54 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create table andrew
2 as select * from dba_tables
3 where 1=2
4 /
Table created.
SQL> insert into andrew
2 select * from dba_tables
3 /
15251 rows created.
I started the blue session below and tried to run an ALTER DATABASE ADD SUPPLEMENTAL LOG DATA command but it did not appear to be doing anything:
PQEDPT1 /export/home/oracle > sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jun 8 15:01:47 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter database add supplemental log data
2 /
I started the green session below and saw that the blue session was waiting for events in waitclass Other:
PQEDPT1 /export/home/oracle > sqlplus /
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jun 8 15:23:52 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select time_waited/100
2 from v$session_event
3 where event = 'events in waitclass Other'
4 and sid =
5 (select sid from v$session where username = 'SYS')
6 /
TIME_WAITED/100
---------------
1312.97
SQL> /
TIME_WAITED/100
---------------
1321.79
SQL> /
TIME_WAITED/100
---------------
1330.59
SQL>
This was not particularly helpful but, fortunately for me, I knew that the blue session was waiting for the red session’s transaction to finish. I returned to it and did a COMMIT:
PQEDPT1 /export/home/oracle > sqlplus /
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jun 8 14:55:54 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create table andrew
2 as select * from dba_tables
3 where 1=2
4 /
Table created.
SQL> insert into andrew
2 select * from dba_tables
3 /
15251 rows created.
SQL> commit
2 /
Commit complete.
SQL>
… and the blue session’s command completed:
PQEDPT1 /export/home/oracle > sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jun 8 15:01:47 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter database add supplemental log data
2 /
Database altered.
SQL>
