oracle 01031 dblink,通过修改基表(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