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>
