I'd tend to agree with you on this one. It's not a method I've tested, but
I can see why it's a problem. When the resumable operation suspends, it
cycles every 30 seconds to see if the space error has been resolved. In
this case, it hasn't really been resolved because the file hasn't grown.
Maybe log it as an enhancement request.
Don't forget that there's an AFTER SUSPEND trigger now that allows you to do
something to address the problem, but that's not the place to put an ALTER
DATABASE command anyway. What you should do is page the DBA, sending them
the details of the problem (you can get this from the
DBMS_RESUMABLE.SPACE_ERROR_INFO procedure). The reason I say don't use the
ALTER DATABASE command is simple - what if the operation that suspends is
using another datafile? You could, I suppose, put an almighty CASE
statement in that checks the tablespace name in the aforementioned procedure
and resizes the datafiles appropriately, but that's getting too messy for my
liking!
--
HTH. Additions and corrections welcome.
Pete
Author of "Oracle8i: Architecture and Administration Exam Cram"
Now got a life back again that the book is released!
"Controlling developers is like herding cats."
Kevin Loney, Oracle DBA Handbook
"Oh no, it's not. It's much harder than that!"
Bruce Pihlamae, long-term Oracle DBA
"Howard J. Rogers" <howardjr_at_www.com> wrote in message
news:3b95a16c_at_news.iprimus.com.au...
> 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 Wed Sep 05 2001 - 10:19:37 CDT
