Is this an "unexpected feature" of 9i?

One of the nice new features of 9i is its ability to suspend a transaction if it runs into 'out of space' issues, and to resume it (automatically) when the space issue has been fixed up by the DBA.

I was just testing that today, and I encountered a surprise.

Alter session enable resumable timeout 3600; create table newone as select * from huge_one tablespace small_one; (The transaction suspends, because the tablespace is too small)

In another session:
Alter database datafile '/direcotry/smallone.dbf' autoextend on;

...and nothing happened. I'd assumed that allowing the original datafile now to autoextend would permit the transaction to resume, since there should now be no worries about space. But it didn't (but this is on Linux, remember).

If I 'alter tablespace small_one add datafile 'directory/small2.dbf' size 100m', then sure enough the alert log included the line 'statement in resumable session.....was resumed'.

I'm assuming that files only autoextend at the time a request for space beyond their current size is made -and by the time I switched on autoextension, the request was long since made and gone, and hence the failure to respond to the new situation.

I'd consider this, myself, to be slightly odd behaviour, however, and I'm left wondering whether it really is a problem, or a quirk of my system, or something I should have expected.

Comments welcome.
HJR Received on Tue Sep 04 2001 - 01:23:05 CDT