DB link Migration (the password in Dblink is unknown)

I do not know whether the DBA will sometimes encounter such a embarrassed embarrassed situation, to do some schema migration, due to the use of the public DB link, however, because do not know the DB link target account password, it is not possible to re-create the DB link in the new environment.
The idea of this experiment is to migrate the base table link$ of the view dba_db_links and replace it with the new environment. Because the user's password ciphertext is saved in the link$ base table.
----first confessed to both sides of the environment, are Linux on the 11.2.0.3 version, on the 10.2.0.4 also tested.One side of the database seven, while the database Ginna. Assume that DB link named Test on the seven side is migrated to the Ginna side without knowing Scott's password.
Source-side operation:First, create a public DB link named Test on the source side.

Seven >create public database link test connect to Scott identified by Tiger using ' Ginna ';D atabase link Created.seven >select * FROM [email protected]; Global_name---------------------Ginnaseven >select * from Dba_db_links;owner                          db_link                        USERNAME                       HOST                           CREATED--------------------------------------------------------------------------------------------------- ------------------------------public                         TEST                           SCOTT                          Ginna                          06-mar-15

Create an intermediate table Trans1,ctas base table sys.link$.

Seven >create table trans1 as SELECT * from sys.link$; Table Created.seven >select Count (*) from trans1;  COUNT (*)----------         1

Then create a second intermediate table Trans2 under the seven user (Seven has DBA authority).

Seven >conn Seven/oracleconnected.seven >create table Trans2 as SELECT * from sys.link$;   -----Of course cannot directly ctas the base table. CREATE TABLE Trans2 as SELECT * from sys.link$                                         *error @ line 1:ora-01031:insufficient privilegesseven >create tab Le Trans2 as SELECT * from SYS.TRANS1; Table Created.seven >select Count (*) from TRANS2;  COUNT (*)----------         1seven >

OK, the intermediate table Trans2 created successfully.

target-side operation:Create a public DB link named Test2 on the target side.

Ginna >show useruser is ' SYS ' Ginna >select * from dba_db_links;no rows selectedginna >create public Database link Test2 connect to seven identified by Oracle using ' seven ';D atabase link created.ginna >select * from [email protected] ; Global_name----------------------Sevenginna >set line 200ginna >col db_link for A30ginna >col host for A30ginna & Gt;select * from Dba_db_links;owner                          db_link                        USERNAME                       HOST                           CREATED--------------------------------                         -------------------------------------------------------------------------------------------------Public TEST2                          SEVEN                          SEVEN                          06-mar-15

The seven-side trans2 table is ctas to table trans3 through the Test2 db link.

Ginna >create Table TRANS3 as SELECT * from [email protected]; Table Created.ginna >select Count (*) from TRANS3;  COUNT (*)----------         1

Truncate off the base table sys.link$, you can back up the base table and insert the table TRANS3 into the base table link$ for security reasons.

Ginna >truncate table sys.link$; Table Truncated.ginna >insert to sys.link$ select * from trans3;1 row Created.ginna >commit; Commit complete.

Now check the dba_db_links to find out that the DB link named Test has been successfully migrated on the Ginna side.

Ginna >select * from Dba_db_links;owner                          db_link                        USERNAME                       HOST                           CREATED-------------------------------------------------------------------------------------------------------- -------------------------public                         TEST                           SCOTT                          Ginna                          06-mar-15

Test the validity of the DB link:

Ginna >select * FROM [email protected]; Global_name------------------Ginna

OK, now there's another question, is there a DB link that was previously named Test2? Is it possible to rebuild? Obviously it is not possible to:

Ginna >create Public Database link Test2 Connect to seven identified by Oracle using ' seven '; Create public database Lin K Test2 Connect to seven identified by Oracle using ' seven '                            *error on line 1:ora-02011:duplicate database link name

The premise needs to be refreshed under the shared pool. Then you can rebuild it.

Ginna >alter system flush Shared_pool; System altered.ginna >create Public Database link Test2 Connect to seven identified by Oracle using ' seven ';D atabase Li NK Created.ginna >select * from Dba_db_links;owner                          db_link                        USERNAME                       HOST                           CREATED-------------------------------------------------------------------------------------------------------- -------------------------public                         TEST                           SCOTT                          ginna                          06-mar-15public                         TEST2                          SEVEN                          Seven                          06-mar-15ginna >

Of course, if you do not flush the shared pool directly delete db link will encounter ora-600 internal error:

sql> Drop Public Database link Test2;drop public database link Test2                          

Of course, we can not create test2 this DB link, the main is to trans1 this intermediate table to the Ginna library, there are many ways, you can directly use EXP/IMP or data pump can be. All the way to Rome, the method has many kinds, mainly is the seven end of the sys.link$ base table migrated to Ginna end and replace Ginna end of sys.link$.

DB link Migration (the password in Dblink is unknown)