通过修改基表(link$)让非public dblink变为public

联系：手机(13429648788) QQ(107644445)
链接：http://www.xifenfei.com/2793.html
标题：通过修改基表(link$)让非public dblink变为public
作者：惜分飞©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]

有些朋友创建了一个非public的dblink，现在该数据库的其他用户需要去使用该dblink，在正常情况下无访问权限，需要重新建一个dblink，或者将原dblink修改为public。但是由于忘记了原dblink的目标段的密码，使得创建或者修改dblink的步骤无法进行下去。这里通过修改基表(link$)，解决该问题。

创建dblink

SQL> show user ; USER is "SYS" SQL> create database link "xff_dblink"    2  connect to TEST    3  identified by "test"    4  using '11.1.1.1:1521/mcrm' ;   Database link created.   SQL> select * from dba_db_links where db_link like 'XFF_DBLINK%' ;   OWNER DB_LINK                                     USERN HOST               CREATED ----- ------------------------------------------- ----- ------------------ -------- SYS   XFF_DBLINK.REGRESS.RDBMS.DEV.US.ORACLE.COM  TEST  11.1.1.1:1521/mcrm 29-MAR-12   SQL> select sysdate from dual@xff_dblink;   SYSDATE --------- 29-MAR-12   SQL> CONN TEST/TEST Connected. SQL> SELECT SYSDATE FROM DUAL@XFF_DBLINK; SELECT SYSDATE FROM DUAL@XFF_DBLINK                           * ERROR at line 1: ORA-02019: connection description for remote database not found --该dblink不是public的，所以test用户无权访问

dblink变为public类型

SQL> CONN / AS SYSDBA Connected. SQL> set long 1000 SQL> select  text from dba_views where view_name= 'DBA_DB_LINKS' ;   TEXT ------------------------------------------------------------------- select u. name , l. name , l.userid, l.host, l.ctime from sys.link$ l, sys. user $ u where l.owner# = u. user # --查询出dblink相关的基表有link$和user$   SQL> desc sys.link$   Name                          Null ?    Type   ----------------------------- -------- --------------------   OWNER#                        NOT NULL NUMBER   NAME                          NOT NULL VARCHAR2(128)   CTIME                         NOT NULL DATE   HOST                                   VARCHAR2(2000)   USERID                                 VARCHAR2(30)   PASSWORD                               VARCHAR2(30)   FLAG                                   NUMBER   AUTHUSR                                VARCHAR2(30)   AUTHPWD                                VARCHAR2(30)   PASSWORDX                              RAW(128)   AUTHPWDX                               RAW(128)   SQL> select owner# from sys.link$ where name like 'XFF_DBLINK%' ;        OWNER# ----------           0 --XFF_DBLINK对应的用户标识记录在link$.owner#中   SQL> SELECT USER #, NAME FROM USER $ WHERE name in ( 'SYS' , 'PUBLIC' );         USER # NAME ---------- ------------------------------           1 PUBLIC           0 SYS --现link$.owner#值为0，表示该dblink所属用户为SYS，现在让该dblink变为public --现需要让该dblink变为public，需要做的是修改link$.owner#的值为1   SQL> UPDATE LINK$ SET OWNER#=1 WHERE name like 'XFF_DBLINK%' ;   1 row updated.   SQL> COMMIT ;   Commit complete.   --需要刷新shared_pool SQL> ALTER SYSTEM FLUSH SHARED_POOL;   System altered.   --查看dblink所属者，已经修改为public SQL> select owner from dba_db_links where db_link like 'XFF_DBLINK%' ;   OWNER ---------- PUBLIC   --测试dblink是否成功 SQL> CONN TEST/TEST Connected. SQL> SELECT SYSDATE FROM DUAL@XFF_DBLINK;   SYSDATE --------- 29-MAR-12