Monday, April 13, 2015

statement suspended, wait error to be cleared

This happened in an Oracle 11.2.0.4 database. A colleague complained of poor performance. I looked to see what his session was waiting for:

SQL> l
  1  select event
  2  from v$session_wait
  3  where sid = 226
  4* and state = 'WAITING'
SQL> /
 
EVENT
----------------------------------------------------------------
statement suspended, wait error to be cleared
 
SQL>

I looked in the alert log and saw the following error:

statement in resumable session 'User WORK(105), Session 226, Instance 1' was suspended due to
    ORA-01653: unable to extend table WORK.WRK_CONSUMPTION_NHH by 8192 in tablespace MI_DATA

I increased the size of one of the tablespace’s datafiles:

SQL> l
  1  alter database datafile
  2  '/cogdbase/ecocog5/eco_data/mi_data2.dbf'
  3* resize 31g
SQL> /
 
Database altered.
 
SQL>

Then I looked in the alert log and saw that the statement had started working again:

Mon Apr 13 16:23:19 2015
Completed: alter database datafile
'/cogdbase/ecocog5/eco_data/mi_data2.dbf'
resize 31g
Mon Apr 13 16:23:19 2015
statement in resumable session 'User WORK(105), Session 226, Instance 1' was resumed

Finally, I checked the session and it was waiting for a different event:

SQL> l
  1  select event
  2  from v$session_wait
  3  where sid = 226
  4* and state = 'WAITING'
SQL> /
 
EVENT
----------------------------------------------------------------
db file sequential read
 
SQL>

1st November 2016:
Checked for relevance.
Shared on LinkedIn.

No comments: