SQL Server Gateway (/tmp 100% FULL)

Trouble Shooting :=)

hostname:/home/oracle$ df -h /tmp
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg01-tmplv
                       12G   12G  4.2M 100% /tmp
hostname:/home/oracle$

hostname:/home/oracle$ lsof +D /tmp
lsof: WARNING: can't opendir(/tmp/lost+found): Permission denied
COMMAND   PID   USER   FD   TYPE DEVICE    SIZE/OFF    NODE NAME
dg4msql  7319 oracle   10u   REG 253,26 11290202112      39 /tmp/qe9vfnzS
java    18849 oracle  mem    REG 253,26       32768 1441794 /tmp/hsperfdata_oracle/18849
hostname:/home/oracle$

hostname:/home/oracle$ rm -fv /tmp/qeOGiDuK
removed `/tmp/qeOGiDuK'
hostname:/home/oracle$

The process dg4msql (Oracle Database Gateway for SQL Server) used up 100% tmp space.

Temp space for dg4msql is analogous to temporary tablespace for Oracle database.

There can be Multiple Temporary Tablespaces in Oracle and why not multiple tmp directory for OS!

Solution provided by Oracle support.

Those temp files starting with “qe*” are a necessity by the driver and they will be created nevertheless.

One option to avoid to generate in /tmp files if you are running out of space is to set the variable QE_TMPDIR.

This variabe would only change the location where the tmp files are stored. In case you have a partition with more space than the default location of /tmp

You can set this parameter choosing one of the below options –

A) – Set the env variable in the gateway init file specifying the new location of the files, adding the below parameter

HS_FDS_CONNECT_INFO=…
:
#
SET PRIVATE QE_TMPDIR=/var/tmp

B) – Or place it into the listener.ora entry for the gateway using the ENV= parameter, for example:

(SID_DESC=
(SID_NAME=sid_name)
(ORACLE_HOME=)
(ENVS=QE_TMPDIR=/var/tmp,LD_LIBRARY_PATH=/dg4msql/driver/lib:/lib)
(PROGRAM=dg4msql)
)

Either specifying in the gateway init file or in the listener.ora file, stop/start the listener and monitor again.

One suggestion is to check the contents of the QE_TMPDIR or /tmp once a week and see if there are files there, how big they are and how old they are.

If the contents of the QE_TMPDIR or /tmp seem empty and suddenly it fills up; then it is one of your queries that is passing thru a lot of data.

You would need to have that query or session identified and check if there is one way you can make it spread the work a little bit, using a PL/SQL stored procedure that does a few commits along the way, releasing some space.

Closing thought, FS and DB TEMP space should be the same size to prevent issues.