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

最新推荐文章于 2022-08-30 00:30:14 发布

heqiyu34

最新推荐文章于 2022-08-30 00:30:14 发布

阅读量2.8k

点赞数

分类专栏： Oracle

Oracle 专栏收录该内容

161 篇文章 0 订阅

订阅专栏

联系：手机(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