联系:手机/微信(+86 17813235971) QQ(107644445)
标题:通过修改基表(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
好文,以SYSDBA 给用户建prive DBLINK 也可以用这个update link$的方法,今天又学到了一招。