Re: CONFESSION TIME.

OH, Niall, how COULD you?

(I hate when people say things like that. Like that really helps. "Oh my, why didn't you install sprinklers? as your house, barn, office building , etc, burns to ash)

I have been suspicious of auto-extend (that's set on a file basis, I believe, correct?), quasi-suspicious of "unlimited extents"

I use raw i/o so much that autoextend for datafiles is irrelevant to me; I make sure blocksize is big, and unless I died at the keyboard, managing and collapsing extents out of control isn't too bad. If they enhance Oracle to do autoextends by allocating from a given raw partition list, I'd be more interested.

OEM has been reliable for me in shrinking unused space, and for invoking coalescing (though that is easy enough to do in sqlplus or svrmgrl); that bit about having to have a nonzero PCTwhatever for Oracle to automatically coalesce freespace still galls me and I fail to see the reason. But I am just a stupid DBA grunt.)

But, bottom line, mate, "that feature should not have behaved in the manner you have described" and sounds like a big bloody bug to me. OEM {qualifying. OEM on NT running against Oracle 8.x on Solaris, HP-UX, NCR UNIX} would barf and say ixnay to shrinking past the point of the "No more data here" marker.

I hope you TARred and feathered the guilty parties, and best of luck with this, ick.

RSH. "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:3c879eee$0$232$ed9e5944_at_reading.news.pipex.net...
> Ooops
>
> Database is busy filling up disk because tablespace is set to auto-extend.
> DBA therefore decides as the largest tablespace is 11gig and only actually
> contains 400mb of data (because third party app drops and recreates
> temporary tables in permanent tablespace!). As he happens to have oem open
> resizes the datafile using the handy dandy GUI.
>
> Alert log follows
>
> Thu Mar 07 15:39:37 2002
> /* OracleOEM */ ALTER DATABASE DATAFILE 'F:\ORACLE\AGR\AGR_AGRTEMP.DBF'
> RESIZE 4096M
> Thu Mar 07 15:39:38 2002
> Completed: /* OracleOEM */ ALTER DATABASE DATAFILE 'F:\ORACLE
> Thu Mar 07 15:39:39 2002
> Errors in file g:\oracle\admin\agr\udump\ORA00630.TRC:
> ORA-01115: IO error reading block from file 7 (block # 49930)
> ORA-01110: data file 7: 'F:\ORACLE\AGR\AGR_AGRTEMP.DBF'
> ORA-27091: skgfqio: unable to queue I/O
> OSD-04026: Invalid parameter passed. (OS 49929)
> etc etc.
>
>
>
> So now we are recovering. My question is - my understanding was that
'ALTER
> DATABASE DATAFILE BLAH RESIZE <SOME SIZE> would fail if there was data in
> the file beyond the requested resize. I am unclear as to why oracle has
lost
> its grip on the datafile size. any clues.
>
> Flames along the lines of
>
> 1. don't use autoextend
> 2. don't use oem and
> 3. don't use NT use a man's operating system are gratefully received
>
> Windows NT4 sp6a
> 8.1.6.1 SE database.
> OEM 2.2
>
>
>
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
>
> ******************************************
>
>
Received on Thu Mar 07 2002 - 15:58:21 CST