Recently we were executing a script that failed because of a missing tablespace, which had been specified by the ‘define’ statement in the script. The truly odd thing about the failure is that it had been successfully tested in another database on a different box. After investigating and a little research we determined the problem.
The initial suspicion was that we had a ‘glogin.sql’ file that contained the extra ‘defines’ since they showed up by default when logging into SQL*Plus. None of the ‘glogin.sql’ scripts on this box had the ‘defines’, so we ran a ‘truss’ on a SQL*Plus session that allowed us to determine the source of the settings. The truss utility is excellent for finding out what files are opened by a process; the short form of our output looked a little like this:
access("./login.sql", 0) = 0
access("././login.sql", 0) = 0
access("./login.sql", 0) = 0
access("././login.sql", 0) = 0
__libc_sbrk(0x0000000000030020) = 0x000000011025C480
access("././login.sql", 0) = 0
statfs("././login.sql", 0x0FFFFFFFFFFFD4B0) = 0
mntctl(2, 512, "") = 0
mntctl(2, 6376, "") = 24
kopen("/etc/vfs", O_RDONLY) = 9
kioctl(9, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
kioctl(9, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
kread(9, " # @ ( # ) v f s @".., 4096) = 1772
close(9) = 0
kopen("././login.sql", O_RDONLY|O_LARGEFILE) = 9
So, it looked like we were getting these values from a ‘login.sql’ instead of a ‘glogin.sql’ file. Since it looked like default directories were included in the search order, we looked in the home directory of the non-oracle UNIX account, and we found the culprit. We renamed the ‘login.sql’ file and the ‘defines’ disappeared.
During the research, we found a lot of references to the SQLPATH variable, but ours was empty. The driver for our problem turned out to be the ORACLE_PATH variable, which contains our home directory, the current ‘.’ directory, and several others that had been added over the years. According to Metalink Note 1012298.6, this setting controls the search path for ‘glogin.sql’ and ‘login.sql’, which explains why our ‘login.sql’ file, residing in our $HOME directory, was causing our default ‘defines’. The most disturbing find of this whole exercise was that, from 10g on, the ‘login.sql’ script is executed every time a ‘connect’ occurs in a script. So, this means that even if ‘defines’ are created by a primary script, the values can be accidentally overwritten following a connect, if those values are defined in a ‘login.sql’ file, forcing all downstream scripts use the default values.
