CONFESSION TIME.

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Thu, 7 Mar 2002 17:09:53 -0000
Message-ID: <3c879eee$0$232$ed9e5944@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

don't use autoextend
don't use oem and
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 - 11:09:53 CST