Outerjoin über zwei Datenbankinstanzen in Oracle

[Robert_G]

Mit DB Links ist das ziemlich easy.

DB liegt auf anderer Maschine:

SQL-Code:

CREATE DATABASE LINK ServiceName.Domain.de
CONNECT TO CURRENT_USER
   USING 'ServiceName';

Auf der gleichen Maschine reicht das:

SQL-Code:

CREATE DATABASE LINK ServiceName
CONNECT TO CURRENT_USER;

Bei beiden Schnipseln wird mit dem gleichen Username/password des aktuellen Schemas eine Anmeldung auf die Remote DB gemacht.

ServiceName ist der Service Name der Remote DB
Domain.de muss mit der richtigen Domäne ersetzt werden

SQL-Code:

SELECT emp.ename, emp.DeptNo, dept.loc
FROM   scott.emp emp
      ,scott.dept@DeinDBLink dept
WHERE  emp.DeptNo = dept.DeptNo

Mit Shared DB Links geht die Netzwerk Performance auch bei vielen parallelen sessions nicht in die Knie.

Zitat von Oracle Doku -> Managing a Distributed Database:

...
Shared database links are not useful in all situations. Assume that only one user accesses the remote server. If this user defines a shared database link and 10 shared server processes exist in the local database, then this user can require up to 10 network connections to the remote server. Because the user can use each shared server process, each process can establish a connection to the remote server.

Clearly, a non-shared database link is preferable in this situation because it requires only one network connection. Shared database links lead to more network connections in single-user scenarios, so use shared links only when many users need to use the same link. Typically, shared links are used for public database links, but can also be used for private database links when many clients access the same local schema (and therefore the same private database link).

Creating Shared Database Links
To create a shared database link, use the keyword SHARED in the CREATE DATABASE LINK statement:

CREATE SHARED DATABASE LINK dblink_name
[CONNECT TO username IDENTIFIED BY password]|[CONNECT TO CURRENT_USER]
AUTHENTICATED BY schema_name IDENTIFIED BY password
[USING 'service_name'];


The following example creates a fixed user, shared link to database SALES, connecting as SCOTT and authenticated as JANE:

CREATE SHARED DATABASE LINK link2sales
CONNECT TO scott IDENTIFIED BY tiger
AUTHENTICATED BY keith IDENTIFIED BY richards
USING 'sales';


Whenever you use the keyword SHARED, the clause AUTHENTICATED BY is required. The schema specified in the AUTHENTICATED BY clause is only used for security reasons and can be considered a dummy schema. It is not affected when using shared database links, nor does it affect the users of the shared database link. The AUTHENTICATED BY clause is required to prevent unauthorized clients from masquerading as a database link user and gaining access to privileges information.
...