Re: Is this an "unexpected feature" of 9i?

It certainly did.

Which made the fact that 1 of the 3 ways of making a tablespace bigger failed, but not the other 2. Being a tidy-minded sort of person, I would've preferred 100% failure or 100% success!

Anything else is just a tad messy.

Regards
HJR "Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message news:3B95434B.4323_at_yahoo.com...
> 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 - 22:56:33 CDT