log file switch (archiving needed)

I was running a test in Oracle 11.2 and the SQL below, which usually takes around 1 minute, seemed to be running forever:

SQL> begin
  2   for a in 1..8 loop
  3    insert into tab1 select * from tab1;
  4   end loop;
  5  end;
  6  /

I looked up the session's SID in V$SESSION to see if there was a row with wait_time set to zero. This means that the session is currently waiting on the event concerned. I saw a wait event I had not seen before:

SQL> select event, seconds_in_wait
  2  from v$session
  3  where sid = 191
  4  and wait_time = 0
  5  /

EVENT                               SECONDS_IN_WAIT
----------------------------------- ---------------
log file switch (archiving needed)             2596

SQL>

To double check the diagnosis, I waited for 10 seconds:

SQL> exec dbms_lock.sleep(10);

PL/SQL procedure successfully completed.

SQL>

... and when I looked again, the seconds_in_wait had increased by 10 seconds too, proving that the session was spending all its time waiting on this event:

SQL> select event, seconds_in_wait
  2  from v$session
  3  where sid = 191
  4  and wait_time = 0
  5  /

EVENT                               SECONDS_IN_WAIT
----------------------------------- ---------------
log file switch (archiving needed)             2606

SQL>

In most situations, you would have to clear some space from the archive area to allow the log file switch to proceed. However, the test I was running did not need the database to be in ARCHIVELOG mode so I closed the database and mounted it:

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  523108352 bytes
Fixed Size                  1375704 bytes
Variable Size             318767656 bytes
Database Buffers          197132288 bytes
Redo Buffers                5832704 bytes
Database mounted.
SQL>

However, when I tried to put the database in NOARCHIVELOG mode, I saw an error which I had not seen before - 2 new things in 1 day!

SQL> alter database noarchivelog;
alter database noarchivelog
*
ERROR at line 1:
ORA-38774: cannot disable media recovery - flashback
database is enabled

SQL>

Well, I was doing some flahsback testing about a week ago but that is finished for now so I turned it off:

SQL> alter database flashback off
  2  /

Database altered.

SQL>

Then I was able to put the database in NOARCHIVELOG mode and get on with my original test, which I hope to report in a future post:

SQL> alter database noarchivelog
  2  /

Database altered.

SQL> alter database open
  2  /

Database altered.

SQL>

How to Diagnose a Locking Issue

A user ran an update statement and noticed that it did not seem to be doing anything:

 

SQL> conn gordon/bennett

Connected.

SQL> update andrew.emp

  2  set ename = 'COLIN'

  3  where ename = 'BRIAN'

  4  /

 

I looked up his SID in V$SESSION:

 

SQL> select sid from v$session

  2  where username = 'GORDON'

  3  /

 

       SID

----------

      393

 

SQL>

I ran the following SQL, waited 10 seconds and ran it again:

 

SQL> l

  1  select event, time_waited/100

  2  from v$session_event

  3  where sid = 393

  4  and wait_class != 'Idle'

  5* order by 2 desc

SQL> /

 

EVENT                          TIME_WAITED/100

------------------------------ ---------------

enq: TX - row lock contention           249.58

Disk file operations I/O                   .01

SQL*Net message to client                    0

 

SQL> /

 

EVENT                          TIME_WAITED/100

------------------------------ ---------------

enq: TX - row lock contention            258.6

Disk file operations I/O                   .01

SQL*Net message to client                    0

 

SQL> 

This showed me that there was an ongoing locking issue with this user. I looked up the SID of the blocking session like this:

 

SQL> l

  1  select blocking_session from v$session

  2* where sid = 393

SQL> /

 

BLOCKING_SESSION

----------------

             101

 

SQL>

 

I saw that it belonged to Fred:

 

SQL> l

  1  select username from v$session

  2* where sid = 101

SQL> /

 

USERNAME

------------------------------

FRED

 

SQL>

I found some SQL on the Internet and hoped that it would tell me what Fred was doing but it did not work:

 

SQL> l

  1  select b.sql_text

  2  from v$session a, v$sqlarea b

  3  where a.sql_address = b.address

  4* and a.sid = 101

SQL> /

 

no rows selected

 

SQL>

 

I wondered if Fred had run his SQL but not commited it and changed my SQL as follows:

 

SQL> l

  1  select b.sql_text

  2  from v$session a, v$sqlarea b

  3  where a.prev_sql_addr = b.address

  4* and a.sid = 101

SQL> /

 

SQL_TEXT

--------------------------------------------------

update andrew.emp set enum = 2 where enum = 1

 

SQL> 

Then I looked in the EMP table and saw that both Fred and Gordon were trying to update the same row:

 

SQL> select * from andrew.emp

  2  /

 

ENUM  ENAME

----- ----------

1     BRIAN

 

SQL>

 

I asked Fred to COMMIT his UPDATE statement:

 

SQL> update andrew.emp

  2  set enum = 2

  3  where enum = 1

  4  /

 

1 row updated.

 

SQL> commit

  2  /

 

Commit complete.

 

SQL>

This allowed Gordon’s UPDATE to finish:

 

SQL> conn gordon/bennett

Connected.

SQL> update andrew.emp

  2  set ename = 'COLIN'

  3  where ename = 'BRIAN'

  4  /

 

1 row updated.

 

SQL>

ORA-00028

This example shows how to kill a user session. You can view sessions in your database as follows:

SQL> col username format a10
SQL> l
  1  select username, sid, serial#, status
  2  from v$session
  3* where username = 'ANDREW'
SQL> /

USERNAME          SID    SERIAL# STATUS
---------- ---------- ---------- --------
ANDREW            143         79 INACTIVE

SQL>

You can then use the SID and SERIAL# displayed to kill a session as shown below. The username you are interested in may have more than one session. If this happens, you will need to use one or more of the other columns in V$SESSION (e.g.OSUSER, MACHINE, LOGON_TIME etc) to see exactly which session you want to get rid of:

SQL> alter system kill session '143,79';
 

System altered.
 

SQL>

The killed session stays in V$SESSION with a status of KILLED:

SQL> select username, sid, serial#, status
  2  from v$session
  3  where username = 'ANDREW';

USERNAME          SID    SERIAL# STATUS
---------- ---------- ---------- --------
ANDREW            143         79 KILLED

SQL>

Until the user tries to access it again (the connection was made earlier). He will then get an ORA-00028:

SQL> conn andrew/reid@test10
Connected.
SQL> select sysdate from dual;
select sysdate from dual
*
ERROR at line 1:
ORA-00028: your session has been killed

SQL>

... and the session will disappear from V$SESSION:

SQL> select username, sid, serial#, status
  2  from v$session
  3  where username = 'ANDREW';

no rows selected

SQL>

DROP TYPE Statement Hangs

This happened in an Oracle 11 database. I had an INACTIVE session in the database:

SQL> select status from v$session

  2  where sid = 112;

 

STATUS

--------

INACTIVE

 

SQL>

 

I tried to use this session to drop a type but it did not seem to work:

 

SQL> drop type ppc_day_rec

  2  /

… but the status of the session had changed to ACTIVE so I assumed it was doing something:

SQL> l

  1  select status from v$session

  2* where sid = 112

SQL> /

 

STATUS

--------

ACTIVE

 

SQL>

 

Nothing showed up in DBA_WAITERS:

 

SQL> select * from dba_waiters;

 

no rows selected

 

SQL>

 

… but I found what was holding onto the TYPE like this:

 

SQL> l

  1  select a.sid, serial#

  2  from v$access a, v$session b

  3  where a.sid = b.sid

  4* and object = 'PPC_DAY_REC'

SQL> /

 

       SID    SERIAL#

---------- ----------

       106       1378

 

SQL>

 

I killed the offending session as follows:

 

SQL> alter system disconnect session '106,1378' immediate

  2  /

 

System altered.

 

SQL>

 

… and the DROP TYPE statement completed:

 

SQL> drop type ppc_day_rec

  2  /

 

Type dropped.

 

SQL>

ORA-00030

This was tested on Oracle 11.2. When you run ALTER SYSTEM KILL SESSION ‘X,Y’; X must be a SID from V$SESSION and Y must be the SERIAL# from the same row. If not, you will get an ORA-00030: 

SQL> select count(*) from v$session

  2  where sid = 123

  3  and serial# = 321

  4  /

 

  COUNT(*)

----------

         0

 

SQL> alter system kill session '123,321'

  2  /

alter system kill session '123,321'

*

ERROR at line 1:

ORA-00030: User session ID does not exist.

 

SQL>

V$SESSION_WAIT SECONDS_IN_WAIT Column

This was tested on Oracle 11.2.0.1.0. I created a user in the red session below and used it to login to the database:

 

SQL> grant create session to andrew

  2  identified by reid

  3  /

 

Grant succeeded.

 

SQL> conn andrew/reid

Connected.

SQL>

 

While this session was waiting, I logged in to the green session below and looked in V$SESSION_WAIT to see what it was waiting for:

 

SQL> select b.event, b.seconds_in_wait

  2  from v$session a, v$session_wait b

  3  where a.sid = b.sid

  4  and username = 'ANDREW'

  5  /

 

EVENT                          SECONDS_IN_WAIT

------------------------------ ---------------

SQL*Net message from client                642

 

SQL> exec dbms_lock.sleep(10);

 

PL/SQL procedure successfully completed.

 

SQL> select b.event, b.seconds_in_wait

  2  from v$session a, v$session_wait b

  3  where a.sid = b.sid

  4  and username = 'ANDREW'

 5  /

 

EVENT                          SECONDS_IN_WAIT

------------------------------ ---------------

SQL*Net message from client                652

 

SQL>

 

In the 10 second delay between the first query and the second, the value in the SECONDS_IN_WAIT column went up by 10, showing that the session was actively waiting on this event.

 

In Oracle 10, the information from V$SESSION_WAIT was included in V$SESSION so I could have used the following query instead:

 

SQL> select event, seconds_in_wait

  2  from v$session

  3  where username = 'ANDREW'

  4  /

 

EVENT                          SECONDS_IN_WAIT

------------------------------ ---------------

SQL*Net message from client               2036

 

SQL> exec dbms_lock.sleep(10);

 

PL/SQL procedure successfully completed.

 

SQL> select event, seconds_in_wait

  2  from v$session

  3  where username = 'ANDREW'

  4  /

 

EVENT                          SECONDS_IN_WAIT

------------------------------ ---------------

SQL*Net message from client               2046

 

SQL>

ORA-00026

This was tested on Oracle 9. When you use the ALTER SYSTEM KILL SESSION command, you must supply the SID and SERIAL# from an entry in V$SESSION. You get an ORA-00026 if you do not provide one: 

SQL> alter system kill session

  2  /

alter system kill session

                        *

ERROR at line 1:

ORA-00026: missing or invalid session ID

 

SQL>

 

The SID and SERIAL# are both numbers so you also get an ORA-00026 if you give non numeric values:

 

SQL> alter system kill session 'A,B'

  2  /

alter system kill session 'A,B'

*

ERROR at line 1:

ORA-00026: missing or invalid session ID

 

SQL>

 

… or values which do not come from V$SESSION:

 

SQL> select count(*) from v$session

  2  where sid = 123

  3  and serial# = 456

  4  /

 

  COUNT(*)

----------

         0

 

SQL> alter system kill session '123,456'

  2  /

alter system kill session '123,456'

*

ERROR at line 1:

ORA-00026: missing or invalid session ID

 

SQL>

 

However, if the values are valid, the command should work:

 

SQL> select sid, serial# from v$session

  2  where username = 'ANDREW'

  3  /

 

       SID    SERIAL#

---------- ----------

        13         34

 

SQL> alter system kill session '13,34'

  2  /

 

System altered.

 

SQL>

ORA-04021

A colleague had another problem with a package compilation hanging in an Oracle 11.1.0.6.0 test database. I was able to reproduce it as follows: 

SQL> alter package srce.pk_pricing compile

  2  /

alter package srce.pk_pricing compile

*

ERROR at line 1:

ORA-04021: timeout occurred while waiting to lock object

 

SQL>

 

There were locks on this package according to V$DB_OBJECT_CACHE but this time, flushing the shared pool made no difference and I found that I still could not compile the package:

 

SQL> l

  1  select type, locks

  2  from v$db_object_cache

  3  where owner = 'SRCE'

  4* and name = 'PK_PRICING'

SQL> /

 

TYPE                              LOCKS

---------------------------- ----------

PACKAGE BODY                          3

PACKAGE                               3

 

SQL> alter system flush shared_pool

  2  /

 

System altered.

 

SQL> select type, locks

  2  from v$db_object_cache

  3  where owner = 'SRCE'

  4  and name = 'PK_PRICING'

  5  /

 

TYPE                              LOCKS

---------------------------- ----------

PACKAGE BODY                          3

PACKAGE                               3

 

SQL>

 

I read somewhere that you could not compile a package if somebody was using it and you could find who it was by looking in V$ACCESS. I joined it with V$SESSION to pick up the SERIAL# as follows:

 

SQL> l

  1  select a.sid, serial#

  2  from v$access a, v$session b

  3  where a.sid = b.sid

  4* and object = 'PK_PRICING'

SQL> /

 

       SID    SERIAL#

---------- ----------

       187       7623

       225       3111

       179       6987

 

SQL>

 

I killed the first session:

 

SQL> alter system kill session '187,7623'

  2  /

 

System altered.

 

SQL>

 

… and the number of locks in V$DB_OBJECT_CACHE went down:

 

SQL> l

  1  select type, locks

  2  from v$db_object_cache

  3  where owner = 'SRCE'

  4* and name = 'PK_PRICING'

SQL> /

 

TYPE                              LOCKS

---------------------------- ----------

PACKAGE BODY                          2

PACKAGE                               2

 

SQL>

 

I killed the other two sessions:

 

SQL> alter system kill session '225,3111'

  2  /

 

System altered.

 

SQL> alter system kill session '179,6987'

  2  /

 

System altered.

 

SQL>

 

The number of locks in V$DB_OBJECT_CACHE went to zero:

 

SQL> l

  1  select type, locks

  2  from v$db_object_cache

  3  where owner = 'SRCE'

  4* and name = 'PK_PRICING'

SQL> /

 

TYPE                              LOCKS

---------------------------- ----------

PACKAGE BODY                          0

PACKAGE                               0

 

SQL>

 

… and I was able to compile the package in a second or two:

 

SQL> alter package srce.pk_pricing compile

  2  /

 

Package altered.

 

SQL>

If this does not work for you, click on the Older Post link below to see what I did the first time this happened.