Oracle DB link管理

如果DBLINK 遇到02063问题时，首先检查：

1 DBLINK两边的属主是否一致
2 表名是否存在引号的问题
3 表是否真实存在
4 是否有权限问题

创建Database Link的步骤

Q：
在local服务器上有数据库db_local，db_name = db_local，应用用户：user_local/pwd_local
在另1台远程服务器上有数据库db_remote，db_name = db_remote；应用用户：user_remote/pwd_remote
现在需要从db_local上用user_local用户访问db_remote上用户user_remote的表。
A：
1、        在db_local上创建database link
        (1) 设置db_local上的global_names = false  // alter system set global_names = false;
        (2) 设置db_remote上的global_names = false  //也可以在init.ora中配置
        (3) 在db_local上配置连接db_remote的tnsnames.ora
db_remote =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 202.96.38.19)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = db_remote)
    )
  )
注意：配置的tnsnames.ora应该是数据库服务器上的，而不是在客户端上配置。
        (4) 在db_local上创建用户（该用户必须与db_remote上的应用用户的user/password一致。因为创建database link后，是从db_local上直接用该用户去访问db_remote上的同用户的数据对象的，这样操作可以无需再进行授权）：
create user user_remote identified by pwd_remote;
        (5) 给db_local上的user_local用户授权操作create database link
grant create database link to user_local;
        (6) db_local上用户user_local创建database link
create database link db_remote connect user_remote identified by pwd_remote using ‘db_remote’;  // using 'db_remote' 是tnsnames.ora文件中配置的网络服务名。
2、        建立同义词
        create synonym syn_name for tbl_name@dblink_name;




Specify CURRENT_USER to create a current user database link. The current user must be a global user with a valid account on the remote database.

If the database link is used directly, that is, not from within a stored object, then the current user is the same as the connected user.

When executing a stored object (such as a procedure, view, or trigger) that initiates a database link, CURRENT_USER is the username that owns the stored object, and not the username that called the object. For example, if the database link appears inside procedure scott.p (created by scott), and user jane calls procedure scott.p, the current user is scott.

However, if the stored object is an invoker-rights function, procedure, or package, the invoker's authorization ID is used to connect as a remote user. For example, if the privileged database link appears inside procedure scott.p (an invoker-rights procedure created by scott), and user Jane calls procedure scott.p, then CURRENT_USER is jane and the procedure executes with Jane's privileges.


create database link <dblink name>
connect to <user name> identified by <password>
using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<host name>(PORT=<port number>)(CONNECT_DATA =(SERVICE_NAME = <service name>))'