Make non-public dblink public by modifying the base table (link$)

Some friends have created a non-public dblink, and now other users of the database need to use the dblink, under normal circumstances no access, need to re-build a dblink, or change the original dblink to public. However, the steps to create or modify Dblink cannot proceed because the password of the target segment of the original Dblink has been forgotten. This solves the problem by modifying the base table (link$).

Create Dblink

Sql>ShowUser;USER  is"SYS" SQL> Create Databaselink "Xff_dblink"2Connect toTEST3Identified by"Test"4Using'11.1.1.1:1521/mcrm'; DatabaseLink created. SQL> Select *  fromDba_db_linkswhereDb_link like 'xff_dblink%'; OWNER db_link USERN HOST CREATED----- ------------------------------------------- ----- ------------------ --------SYS Xff_dblink. Regress. Rdbms. DEV. RSI Oracle.com TEST11.1.1.1:1521/Mcrm in-MAR- ASQL> SelectSysdate fromDual@xff_dblink; Sysdate--------- in-MAR- ASQL>CONN TEST/Testconnected.sql> SELECTSysdate fromDUAL@XFF_DBLINK;SELECTSysdate fromDUAL@XFF_DBLINK                         *ERROR at line1: ORA-02019: Connection Description forRemoteDatabase  notfound--The Dblink is not public, so the test user is not authorized to access

Dblink Change to public type

Sql>CONN/  asSysdbaconnected.sql> Set Long  +SQL> Select  text  fromDba_viewswhereView_name='dba_db_links'; TEXT-------------------------------------------------------------------Selectu.name, L.name, L.userid, L.host, L.ctime fromsys.link$ l, sys.User$ uwherel.owner#=U.User#--query out Dblink related base tables with link$ and user$SQL> descsys.link$ NameNull? Type----------------------------- -------- --------------------owner# not NULL  NumberNAME not NULL VARCHAR2( -) CTIME not NULLDATE HOSTVARCHAR2( -) USERIDVARCHAR2( -) PASSWORDVARCHAR2( -) FLAG NumberauthusrVARCHAR2( -) AuthpwdVARCHAR2( -) PASSWORDXRAW( -) AUTHPWDXRAW( -) SQL> Selectowner# fromsys.link$whereName like 'xff_dblink%'; owner#----------         0--Xff_dblink The corresponding user ID is recorded in link$.owner#SQL> SELECT USER#,name from USER$WHERENameinch('SYS',' Public'); USER# NAME---------- ------------------------------         1  Public         0SYS--The current link$.owner# value is 0, which means that the dblink belongs to SYS and now makes the Dblink public--Now you need to make the dblink public, you need to modify the value of link$.owner# to 1.SQL> UPDATElink$SETowner#=1 WHEREName like 'xff_dblink%'; 1row updated. SQL> COMMIT; CommitComplete .--need to refresh Shared_poolSql> ALTERSYSTEM FLUSH Shared_pool; System altered. --View Dblink owned by, modified to publicSql> SelectOwner fromDba_db_linkswhereDb_link like 'xff_dblink%'; OWNER---------- Public --test whether the Dblink is successfulSql>CONN TEST/Testconnected.sql> SELECTSysdate fromDUAL@XFF_DBLINK; Sysdate--------- in-MAR- A

Make non-public dblink public by modifying the base table (link$)