首页 > Linux操作系统 > Linux操作系统 > Database Links 全面了解---转Metalink

Database Links 全面了解---转Metalink

原创 Linux操作系统 作者:myhuaer 时间:2009-06-12 15:53:13 0 删除 编辑

初学者看完基本能对db link 有概要了解。


Subject: Database Links: An Overview
  Doc ID: 117278.1 Type: BULLETIN
  Modified Date : 27-MAY-2009 Status: PUBLISHED

Provide a overview of the Oracle Database Link
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:

    Link name 
    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 

    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 ''

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: 

      (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 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: 

        (ADDRESS_LIST =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = rtcsol1)(PORT = 1521))
          (SESSION = RAW)
        (ADDRESS = (PROTOCOL = TCP)(HOST = rtcsol1)(PORT = 14000))


9) The Syntax tree for CREATE DATABASE LINK

          /- SHARED -\ /- PUBLIC -\
          |          | |          |
  CREATE -----------------------------> DATABASE LINK --->  ---> 

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

    /-> 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$
    SQL> select NAME from$ where AUTHUSR is not null;

12) Common Errors 
  12.1) Bad host name

    "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 
               - 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 # 

    "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

    "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)

来自 “ ITPUB博客 ” ,链接:,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录


  • 博文量
  • 访问量