ORA-12154: TNS:could not resolve service name

From: Frank Siegel <NorthernSnow_at_worldnet.att.net>
Date: Tue, 30 Mar 1999 12:56:34 -0500
Message-ID: <7dr39i$m16$1@bgtnsc02.worldnet.att.net>

Thanks so much in advance! I don't know what else to look for or do? Below, please find a lot of info.

Much Appreciated,
Frank Siegel
New Hampshire

BACKGROUND ***

--SQL/NET version 2
--ORACLE 7.4

Network is okay
tcp/ip is okay
--
schema d_warehouse is trying to SELECT from a table
called LOAD_CONTROL from the d_los schema on a
machine identified in TNSNAMES as los.
--
TNSNAMES is setup correctly because I can logon
from a client SQL/PLUS.
--
A public link called los_frank.world (the ".world"
was autmatically appended to the name los_frank)
was created.
--
WHAT IS WRONG?? CAN YOU HELP PLEASE. HERE ARE
ALL THE QUERIES I CAN THINK OF! if the LISTENER
was not setup correcly on instance "los" would
sql*plus work?
--
- ACTUAL QUERY ***

SQL> select *
2 from d_los.load_control_at_los_frank.world; from d_los.load_control_at_los_frank.world

ERROR at line 2:
ORA-12154: TNS:could not resolve service name

SQL> select *

from d_los.load_control_at_los_frank
SQL> /
from d_los.load_control_at_los_frank

ERROR at line 2:
ORA-12154: TNS:could not resolve service name

Notice This connection from client machine sql*plus: SQL> connect d_los/password_at_los Connected. SQL> -- *** This works from client SQL/PLUS - it resolves TNSname of los okay!!!!

Privieges for target table ****

SQL> connect d_los/password_at_los
Connected.

SQL>

  1  select substr(GRANTEE,1,10) Grantee,
  2         substr(OWNER,1,12) owner,
  3         substr(GRANTOR,1,12) grantor,
  4         substr(PRIVILEGE,1,15) privilege,
  5         substr(GRANTABLE,1,10)  grantable

6 from user_tab_privs
7* where table_name = 'LOAD_CONTROL'
SQL> / GRANTEE OWNER GRANTOR PRIVILEGE grantable
---------- ------------ ------------ --------------- ---

PUBLIC     D_LOS        D_LOS        ALTER                 NO
PUBLIC     D_LOS        D_LOS        DELETE              NO
PUBLIC     D_LOS        D_LOS        INDEX                  NO
PUBLIC     D_LOS        D_LOS        INSERT                NO
PUBLIC     D_LOS        D_LOS        SELECT              NO
PUBLIC     D_LOS        D_LOS        UPDATE              NO
PUBLIC     D_LOS        D_LOS        REFERENCES   NO

7 rows selected.

SQL>

DATABASE LINK ****
- I logged onto the machine (dataprod) that i want to link FROM!
- as the user d_warehouse and queried the dictionary for the link

SQL> connect d_warehouse/password_at_dataprod Connected.

SQL>

  1  select substr(OWNER,1,10) owner,
  2         substr(DB_LINK,1,20) db_link,
  3         substr(USERNAME,1,15) username,
  4         substr(HOST,1,10) host,
  5         CREATEd

6 from dba_db_links
7* where db_link = 'LOS_FRANK.WORLD'
SQL> / OWNER DB_LINK USERNAME HOST CREATED
---------- -------------------- --------------- ---------- ---------
PUBLIC LOS_FRANK.WORLD D_LOS LOS 30-MAR-99

Parameter file **** NUM NAME TYPE VALUE DESCRIPTION
--- ----------- ---- --------- -----------------------------
182 open_links 3 4 max # open links per process
- TNSNAMES *****

LOS.world =
(DESCRIPTION =
(ADDRESS_LIST =

        (ADDRESS =
          (COMMUNITY = tcp.world)
          (PROTOCOL = TCP)
          (Host = 1111.11.111.111)
          (Port = 1521)
        )
        (ADDRESS =
          (COMMUNITY = tcp.world)
          (PROTOCOL = TCP)
          (Host = 111.11.111.111)
          (Port = 1526)
        )

    )
    (CONNECT_DATA = (SID = LOS)
    )
  ) Received on Tue Mar 30 1999 - 11:56:34 CST