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>