title: Can't create Database Link to remote DB in Oracle-DB
body: <p>We have a CRM system in our company, which uses an <strong>Oracle 11g database</strong>. It is developed by a third party vendor.</p>
  
  <p>We do not have access to the server which runs the CRM system. But nevertheless, we have working DBA login data available to us (SYS user). It consists of:</p>
  
  <ul>
  <li>server IP: 172.1.2.3</li>
  <li>port: 1521</li>
  <li>SID: abc</li>
  <li>user: sys</li>
  <li>password: <strong>*</strong></li>
  </ul>
  
  <p>We can use this to access the DB with <strong>Oracle SQL Developer 3.1</strong> (Connections >> Properties)</p>
  
  <p>Now parts of the data must be <em>copied</em> out of the CRM-database into an <em>other</em> Oracle database, which resides on another server.</p>
  
  <p>To my understanding, I'd need to create a database link in my target database. I tried something like this:</p>
  
  <pre><code>CREATE PUBLIC DATABASE LINK xxx CONNECT TO sys IDENTIFIED BY ***** USING 'MYTNSENTRY'
  </code></pre>
  
  <p>My <em>tnsnames.ora</em> is as follows:</p>
  
  <pre><code>MYTNSENTRY =
   (DESCRIPTION = 
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = 172.1.2.3)(PORT = 1521))
     )
   (CONNECT_DATA =
     (SERVICE_NAME = abc)
   )
  )
  </code></pre>
  
  <p>.... and my <em>listener.ora</em> look like this:</p>
  
  <pre><code>MYLISTENER=
    (DESCRIPTION=
      (ADDRESS_LIST=
        (ADDRESS=(PROTOCOL=tcp)(HOST=172.1.2.3)(PORT=1521))
        ))
  SID_LIST_MYLISTENER=
    (SID_LIST=    
      (SID_DESC=
        (SID_NAME=MYTNSENTRY)
        (ORACLE_HOME=C:\somepath) # path to Oracle home of target DB
        (PROGRAM=extproc)))
  </code></pre>
  
  <p>Is PROGRAM=extproc the right choice? There are a couple of other programs to pick. I couldn't even start the listener with <strong>lsnrctl</strong> because it could not "verify the user" or something. Ironically, the listener-setup and database link to a <em>MS SQL server</em> work smoothly.</p>
  
  <p>Now despite lacking some vital information about the CRM DB system, one can still connect to the DB in SQL Developer. Shouldn't it also be possible to make a connection between two Oracle DBs? Please help me with the setup and the creation of the database link.</p>
  
  <p><strong>----- EDIT: --------</strong></p>
  
  <p>Alex Poole's hint helped me get it to work. I used</p>
  
  <pre><code>  show parameters service_names;
  </code></pre>
  
  <p>to get the full service name. It has the form <strong>abc.def</strong>, with <em>def</em> being the domain. Thusly, I added the domain name to the TNS alias in <em>tnsnames.ora</em>:</p>
  
  <pre><code>MYTNSENTRY =
   (DESCRIPTION = 
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = 172.1.2.3)(PORT = 1521))
     )
   (CONNECT_DATA =
     (SERVICE_NAME = abc.def)
   )
  )
  </code></pre>
  
  <p>The connection can be tested with <strong>tnsping MYTNSENTRY</strong> on the command prompt of the target DB server. The tnsnames.ora is local. However, I deleted all changes to the "local" <em>listener.ora,</em> since the listener indeed resides on the CRM server.</p>
  
  <p>The SQL command is mostly unchanged, but now the connection works:</p>
  
  <pre><code>CREATE PUBLIC DATABASE LINK xxx CONNECT TO some_user IDENTIFIED BY ***** USING 'MYTNSENTRY'
  </code></pre>