Re: Tablespace, Diskspace or sql abuse..

"Norman Dunbar" <Norman.Dunbar_at_lfs.co.uk> wrote in message news:E2F6A70FE45242488C865C3BC1245DA703D3D257_at_lnewton.leeds.lfs.co.uk...
> A quick check with oerr shows this :
>
> oerr ora 27069
> 27069, 00000, "skgfdisp: attempt to do I/O beyond the range of the file"
> // *Cause: internal error, the range of blocks being read or written is
> // outside the range of the file, additional information
> indicates
> // the starting block number, number of blocks in I/O, and the
> // last valid block in the file
> // *Action: check for trace file and contact Oracle Support
>
>
> I suspect that internally, Oracle 'thinks' the file is 'x; extents long,
> but the OS 'thinks' it is less than 'x'. End result, carnage !
>
> You say that you are running a query, but the error is obtained writing
> to the file in question. I suspect that this could be a file in your
> temporary tablespace. Whatever it is, you should check for any trace
> files in BACKGROUND_DUMP_DEST and log an iTAR with support. Your DBA
> should be able to manage this - he hasn't been very helpful so far :o)
>

If this is 8i or above, then the use of tempfile temporary tablespace can give rise to this error. When you create a tempfile datafile, Oracle only 'touches' the file, and doesn't actually map out and format the entire thing. If another file on the same disk grows into the space that the tempfile one day needs to properly format, then you get this sort of mess.

Likely, you will have to drop and re-create the temporary tablespace to fix the problem.

Then you can tell your DBA that this sort of thing should never happen, because temporary tablespaces generate so much I/O, it's poor practice to house them along with other, 'genuine' tablespaces. And if they are housed on their own, as they should be, then the non-formatting of tempfiles at the time of their creation becomes a non-issue.

If you like gloating, you can re-phrase that last paragraph slightly more clearly: he doesn't know his job.

;-o Received on Tue Jun 24 2003 - 09:11:22 CDT