Database Links 全面瞭解---轉Metalink

PURPOSE
-------
Provide a overview of the Oracle Database Link
 
SCOPE & APPLICATION
-------------------
Database links can be used in all versions of Oracle and 
on all platforms


Database Links: An Overview
---------------------------
1) What are database links?

  Database links are connections between two databases on the 
  same or different machines. 

2) What are database links used for? 

  To query data on a different database (distributed query) 
  To do DML on data on a different database (distributed transaction)
  To either query or do DML on a non Oracle database (transparent 
  gateways and generic connectivity with 10g and earlier,database gateways from 11g)

3) The Anatomy of a Database Link

  A database link has 4 main parts:

    Owner 
    Link name 
    Username/password 
    Host (Service Name)

  3.1) The database link OWNER 

    Like most objects in an Oracle database ... database links have an
    owner (the user who creates the link) or may be owned by PUBLIC.

    If owned by PUBLIC ... the database link may be accessed by all 
    users on the database.

    Syntax: CREATE public DATABASE LINK ...
            CREATE DATABASE LINK ...

  3.2) The database link NAME 

    The database link name can be any valid Oracle name.  

    If global_names = TRUE in the init.ora then the database link name
    must be the global name of the remote database 
    (SELECT GLOBAL_NAME FROM GLOBAL_NAME).

    Syntax: CREATE DATABASE LINK kbcook ... 

  3.3) The database link USERNAME

    The database link username/password is an OPTIONAL clause.  If it
    is not specified then the current username/password for the local
    database are used to connect to the remote database. 

    If a username is specified then all connections through that link are
    connected as the user specified. 

    Syntax: CREATE DATABASE LINK kbcook 
            CONNECT TO scott IDENTIFIED BY tiger ...

  3.4) The database link HOST 

    The Host is the entry in the TNSNAMES.ORA for the database that is
    being linked to. 

    Syntax: CREATE DATABASE LINK kbcook 
            CONNECT TO scott IDENTIFIED BY tiger 
            USING 'rtcsol1_v805.us.oracle.com'

4) How do you locate the TNSNAMES.ORA?

  If the $TNS_ADMIN environment variable is defined then look in that
  directory for the TNSNAMES.ORA.

  If $TNS_ADMIN is not set then look in the $ORACLE_HOME/network/admin
  directory. Alternately the tnsnames may exist in */etc or 
  /var/opt/oracle as well.

5) How do I locate the HOST in the TNSNAMES.ORA?
  
  The TNSNAMES.ORA will look like: 

  RTCSOL1_V805.US.ORACLE.COM =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rtcsol1)(PORT = 1521))
      (CONNECT_DATA = (SID = V805))
    ) 

  The host name is everything before the = 

  Note: You can specify the full description as the host

  Syntax: CREATE DATABASE LINK kbcook 
          CONNECT TO scott IDENTIFIED BY tiger
          using '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)
                 (HOST = rtcsol1)(PORT = 1521)) 
                 (CONNECT_DATA = (SID = V805))'

6) How a database link works 

  Step 1: SELECT * FROM TABLE@DBLINK

  Step 2: The database will resolve DBLINK to a host name ... it will
          use the TNSNAMES.ORA unless fully described 

  Step 3: Naming resolution (DNS, NIS etc) will resolve the HOST to a
          TCP/IP address

  Step 4: A connection will be made to a listener at the TCP/IP address 

  Step 5: The listener for the PORT will resolve the SID and finish 
          the connection to the database (uses the listener.ora to 
          determine if it is listening for connections to that sid) 

7) How do you locate the LISTENER.ORA?

  If the $TNS_ADMIN environment variable is defined then look in that
  directory for the LISTENER.ORA.

  If $TNS_ADMIN is not set then look in the $ORACLE_HOME/network/admin
  directory. Alternately the listener.ora may exist in */etc or 
  /var/opt/oracle as well.

8) How do I locate the HOST in the LISTENER.ORA?

  The LISTENER.ora will look like: 

  LISTENER=
   (DESCRIPTION_LIST =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
        )
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = rtcsol1)(PORT = 1521))
        )
      )
      (DESCRIPTION =
        (PROTOCOL_STACK =
          (PRESENTATION = GIOP)
          (SESSION = RAW)
        )
        (ADDRESS = (PROTOCOL = TCP)(HOST = rtcsol1)(PORT = 14000))
      )
   ) 

  SID_LIST_LISTENER=
    (SID_LIST=
               (SID_DESC=
                  (SID_NAME=V805)
                  (ORACLE_HOME=/u04/app/oracle/product/8.0.5)
               )
      )
  )

9) The Syntax tree for CREATE DATABASE LINK

          /- SHARED -\ /- PUBLIC -\
          |          | |          |
  CREATE -----------------------------&gt DATABASE LINK ---&gt  ---&gt 


                    /-> CURRENT USER ----------------------------\
                   |                                              |
   /-> CONNECT TO -+                                   /- AUTH -\ |-\
   |               |                                   | CLAUSE | | |
   |                \->  IDENTIFIED BY ----------/  |
   |                                                                |
   |---&gt AUTHENTICATED_CLAUSE --------------------------------------|
   |                                                                |
  -------------------------------------------------------------------&gt

    /-> USING '' ---\
   |                                 |
  ----------------------------------------->  

10) Additional Clauses 

    The SHARED and AUTHENTICATED BY clauses are used to establish a 
    multi-threaded database connection and both are required to do so.

11) How to check if a Database Link is private / public or shared/not shared:
    If the value of the OWNER column of the view DBA_DB_LINKS is PUBLIC, 
    it is a public database link. All other values indicate it is a private 
    database link.

    SQL> select OWNER from dba_db_links where DB_LINK = 'MYLINK';

    To check for shared database links, query sys.link$
    SQL> select NAME from sys.link$ where AUTHUSR is not null;


12) Common Errors 
  
  12.1) Bad host name

    ORA-12154
    "TNS:could not resolve service name"

    *Cause:  The service name specified is not defined correctly in 
             the TNSNAMES.ORA file.

    *Action:  Make the following checks and correct the error:
               - Verify that a TNSNAMES.ORA file exists and is in the
                 proper place and accessible. See the operating system
                 specific manual for details on the required name and 
                 location.
               - Check to see that the service name exists in one of 
                 the TNSNAMES.ORA files and add it if necessary.
               - Make sure there are no syntax errors anywhere in the
                 file. Particularly look for unmatched parentheses or 
                 stray characters. Any error in a TNSNAMES.ORA file 
                 makes it unusable. See Chapter 4 in the SQL*Net V2 
                 Administrator's Guide. If possible, regenerate the 
                 configuration files using the Oracle Network Manager. 

  12.2) Bad port # 

    ORA-12224
    "TNS:no listener"

    *Cause: The connection request could not be completed because the
            listener is not running.

    *Action: Ensure that the supplied destination address matches one
             of the addresses used by the listener - compare the 
             TNSNAMES.ORA entry with the appropriate LISTENER.ORA file
             (or TNSNAV.ORA if the connection is togo by way of an 
             Interchange). Start the listener on the remote machine. 

  12.3) Bad SID

    ORA-12305
    "TNS:listener could not resolve SID given in connect descriptor"

    *Cause:  The SID in the CONNECT_DATA was not found in the 
             listener's tables.

    *Action: Check to make sure that the SID specified is correct. The
             SIDs that are currently registered with the listener can
             be obtained by typing "LSNRCTL SERVICES ".
             These SIDs correspond to SID_NAMEs in TNSNAMES.ORA, or 
             db_names in INIT.ORA.

    *Comment: This error will be returned if the database instance has
              not registered with the listener; the instance may need
              to be started. 

13) Reference Materials

    Server SQL Reference (Create command)
    DBA_DB_LINKS (view for existing links)
    Net8 Administrative Guide  
    Oracle9i Net Services Administrator's Guide, Release 2 (9.2)
    Oracle Database Net Services Administrator's Guide 10g Release 2 (10.2)
    Oracle Database Net Services Administrator's Guide 11g Release 1 (11.1)