Re: Tablespace, Diskspace or sql abuse..

"Arcangelo" <arcangelo-c_at_yahoo.com> wrote in message news:<3ef85c10$0$30819$afc38c87_at_news.optusnet.com.au>...
> "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

by 'tempfile' you mean a locally managed temporary tablespace correct?

hmm... didnt know that can happen. Not everyone is in a situation where you can striple datafiles.(ie... company wont pay for it) Any other way to restrict this? Id guess you can logically partition the disks so this doesnt happen or just put it in a seperate file.

this doesnt happen with dictionary managed temporary tablespaces. This is pretty interesting. Never saw that before. Received on Tue Jun 24 2003 - 13:36:38 CDT