Re: Run sql statement make file system full ?

Sybrand Bakker <postbus_at_sybrandb.demon.nl> writes:

> On Fri, 23 Aug 2002 16:18:37 +0800, "Ken Man" <ken_at_ifshk.com> wrote:
>
> >I have a SQL statement like follows:
> >
> > Select field1, field2......field50 from table1, table2 where XXXXXX
> >
> > UNION
> >
> > Select field1, field2......field50 from table1, table2 where
> >YYYYYYY
> >
> >Then, I got an error message: SQL0968C The file system is full.
> >SQLSTATE=57011
> >
>
> Not an Oracle issue. Must be ODBC. ODBC performs your select locally
> and create a HUGE temporary file on your local disk.
> Yet another reason to avoid ODBC like HELL
>

I don't know if this is related or not, but we had a similar situation arise just a couple of weeks ago. We got "file system full" errors. When we checked the file system with df it showed the data partition was 100% full. However, when we checked it with du, we got different results, which looked like we still had quite a few Gb free. (This is on a True64 system running Oracle 8.1.7).

I'm not the DBA or the sys admin for this system, but was asked to have a look or if I had any ideas what the problem could be (shows how desperate they were getting!). I verified the different figures and was a bit stumped. When the dba removed some logging files, the system briefly showed spare space, but it was almost instantly filled up again. I suggested using find to verify the usage and check for "unusual" files, sparse files which might be getting copied, extra large files etc. My last suggestion was to check for any processes which may somehow be eating up disk space and left it at that (and smiled, realising again how happy I was to just concentrating on development and leaving sys admin/dba stuff to others).

The DBA came back a few minutes later to tell me the problem was solved. The cause was sql*plus. After suggesting checking for processes out of control, the DBA did a ps and saw an SQLplus process which had been running for a long time. He killed this process and suddenly a whole heap of space appeared and the problem was solved.

I don't know enough about sqlplus and how it operates, but this surprised me - I would have thought if sqlplus needed more memory, it would have just used virtual memory (we had plenty of this available). I guess someone could have executed a select and forgotten to join the tables properly or something, but I don't understand why we got different values from df and du, unless sqlplus/oracle was somehow able to use disk space without creating a file or in such a way that du and other file based tools like find do not see it. Whatever the case, it seems there may be some situations where sqlplus/oracle interactions might result in all available disk space being consumed - its a pity the DBA was not able to first check the sqlplus session and find out exactly what it was doing before killing it.

Tim Received on Sun Aug 25 2002 - 19:45:11 CDT