Hi guys,

Some days ago, I was asked to check a problem with an Oracle procedure executed by a shell script. Since I don't have DBA access and I don't have the procedure source, I couldn't gather as much info as I wanted to, but I concluded that:

- The shell script is executed by OS user root (cron), with OS user oracle environment variables $ORACLE_HOME and $ORACLE_SID. It exits with ORA-01254 (TNS - Could not resolve service name). When I run the shell with OS user oracle (su - oracle), it works fine.

- I tested setting ALL the oracle user environment to root (NLS, ORACLE_BASE etc) but it didn't work either.

- I found out that the procedure accessed a remote view, and aparently the developer didn't know that, since the view had a public synonym and the db_link was correctly set. That explained the TNS problem.

- Both instances were in tnsnames, responded to tnsping and I could connect to them individually using sqlplus. However, when I tried to access the remote database using the shell script/sqlplus and root on OS, the error was shown. The authentication was on db level (not OS), there is a specific db user different than oracle and root, so that's not the problem.

- I reported the fact to the DBA and asked him to review the db_link configuration. So, he included the tns descriptor (instead of the service name) in the db_link and the procedure worked.

The problem is now considered solved, but I think that solution is a workaround, the problem was not detected. The db_link wasn't completely wrong, since the remote db was accessed using OS user oracle. For some reason, the remote db wasn't seen using db_link when OS user is root.

- I tried to reproduce the problem at home, but everything worked fine. (that was the worst part...).

I know the information may be not enough, but that's all I could check since I don't have DBA access and don't have the procedure source. Can you guys maybe give me some ideas?

thanks in advance.

Obs: Oracle 8.1.6 running on AIX