Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Is this an "unexpected feature" of 9i?
Howard J. Rogers wrote:
>
> 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
I presume (as per adding a datafile) it also works if you resize the
existing one ?
-- ============================== Connor McDonald http://www.oracledba.co.uk "Some days you're the pigeon, some days you're the statue..."Received on Tue Sep 04 2001 - 16:10:35 CDT