Issue w/ AAA*.TMP files blowing disk space

[Michelle Weiss]

I have an L3000 running 11i which is a middle tier server housing our new oracle 6i forms application - via a netscape url the user connects to forms on this middle tier and then these forms access an N class server housing the back-end oracle databases and application/batch programs and code. (pls don't ask why we opted for this middle tier approach)

The problem is this: extremely large temp files (AAA*.TMP) get created on disk (/tmp or /var/tmp) when a user executes a large or global sql query on a large table. This temp file gets created about 4-5 minutes into the query and continues to grow until it gobbles up the entire 1gb of space I have available on /tmp; when disk capacity is reached the form returns to the user presenting the rows of data that it was able to successfully fetch and it produces an error "FRM-40900: Unable to allocate record buffer". Forms buffers a number of records per block in memory and then when this area is full buffers it to disk; which is why the temp file isn't created immediately.

I clean out this directory every night, but am still running into the issue during the day. My DBA has a TAR open with Oracle, but isn't expecting much back. My forms developers are trying to block global queries, but large queries are producing the same results so I am trying to find a solution. I tried increasing dbc_max_pct from 20 to 50 but this doesn't seem to make a difference. I have 3gb of memory on the system, but am low on free disk space so am unable to allocate any more to /tmp for these files.

Any ideas as to how to handle/manage/solve this problem??

[James R. Ferguson]

Hi Michelle:

You might try setting the TMPDIR variable to a filesystem name that you dedicate to temporary files. Try seting and exporting TMPDIR during Oracle's startup. The effect will be to create temporary files in the filesystem of your choice rather than '/var/tmp' or '/tmp'. See the man pages for 'mktemp(1)' for more information.

Regards!

...JRF...

[Michelle Weiss]

Thank you for the feedback, if I had extra disk space available that would be a viable option, unfortunately the 1gb on /tmp is all I can do...

[Steven E. Protter]

Check the startup script reports60_server

You can relocate cache and temporary files from /tmp to mounted filesystems.

Check this line.

REPORTS60_WEBLOC=/webcache; export REPORTS60_WEBLOC

Consider reolocategn it to a mounted fs with more space.

If your report is triggered by say a button in forms, check the forms60_server script for location of temporary files.

Oracle likes to store temporary output to disk, and you really need to have a place for that.

If the database is running on the same box(I think you said no) you might want to look at init.ora for the database and check where that's going. As far as database goes, I'd wonder about how big the rollback space is, redo segments and such.

If its reports server and the database is not on the same box, then it could be the building blocks for the reports output file.

There is a variable in forms/reports pointing to where pdf files get created. That needs to have plenty of free space.

If its the sql query doing this then its time to check the queries for efficiency. No matter how much disk space you have bad queries can kill you.

I found one of these files on my system and here were the permissions.
-rw-r--r-- 1 ias oinstall 72768 Jan 15 09:29 /var/tmp/AAAa00980.TMP

ias really doesn't do a lot of sql work. The backend sql work is on the database server as are temporary files, redo,rollback segments and archive logs.

Our server is pretty heavily used and the user ias is not the database owner(that would be oracle). In our setup, the database server runs on the same box as ias.

The obvious question from me,(Mr. Patch) is do you have the latest forms/reports patch installed? They are on Patch Level 13, I'm currently getting 12 rolled out.

SEP

[Rita C Workman]

Michelle,

On some other filesystem, do you have space? Even on a filesystem that Oracle uses...that, I believe is what JRF is referring to.

In Oracle's 'config.ora' the DBA has set /tmp as the TMPDIR. This can be set to any other filesystem that has some room.
By doing this, you keep your vg00 filesystems from hitting 100%...and creating problems for everyone.

If you don't have any other place to set Oracle's TMPDIR to, than you are in desperate need of ordering some diskspace.

Just a thought,
Rita

[Michelle Weiss]

I guess I'm just not sure how much is enough - - - 1gb certainly seems to me to be more than enough for temp files. How much can they possibly expect me to allocate for this??

[James R. Ferguson]

Hi (again) Michele [and Rita]:

Yes, Rita is correct. Since it appears that you cannot extend '/tmp', my next choice would be to look for enough physical space on another volumd group to compose a dedicated logical volume (filesystem) for these temporary files. In the absence of that, Rita's point is well-taken, you could use any filesystem with enough space.

Regards!

...JRF...

[Pete Randall]

With 18GB drives available on the used market for well under $500, you could easily remedy this situation. Take up a collection, start a raffle, something. You could even go so far as to beg accounting to cough up $500.

Pete

[Bill Hassell]

If the application requires multi-Gb files then you have two choices: buy (a lot) more disk, or rewrite the application. Multi-Gb temp files seems a bit odd and must require a very long time to create. So if multiple users issue similar requests, you had better look at getting dozens (hundreds?) of Gb more disk space.

This is not a memory issue, it is plain old disk space, so changing memory around won't help. (one exception is for very large SGA in Oracle but you'll need another 9Gb of RAM and Oracle (and related apps) must run in 64bit mode) I'd ask the DBA's and developers to look carefully at the indexes and if nothing can be done to improve the code, get a purchase order for a bunch of disk space.