DBMS_METADATA.GET_DDL database link password missing | Oracle 11.2.0.4 DB_LINK password metadata 

SQL> CREATE DATABASE LINK "DBLINK"
   CONNECT TO "USER1" IDENTIFIED BY VALUES ':1'
   USING 'ORCL';  2    3
   CONNECT TO "USER1" IDENTIFIED BY VALUES ':1'
                                           *
ERROR at line 2:
ORA-02153: invalid VALUES password string

SELECT col1,REPLACE(To_Char(col2),':1',(SELECT PASSWORDX FROM link$ WHERE owner#=v3.owner# AND name=v3.name))
  FROM
    (SELECT 'DROP PUBLIC DATABASE LINK '||v1.name||' ;' AS col1,dbms_metadata.get_ddl('DB_LINK',v1.name,v2.name)||';' AS col2, v1.owner#,v1.name FROM link$ v1 , user$ v2 WHERE v1.owner#=v2.user# AND v1.owner# = 1
) v3;


Kamil Piórek
kamil.piorekk@gmail.com

Komentarze

  1. Unknown2 czerwca 2017 11:53

    Hi Kamil,

    It did not work..

    Your script got me Drop and Create commands fine.

    but while executing the create command.. I have got the following.

    14:50:10 02-JUN-17_SYS_TRNDB01>CREATE PUBLIC DATABASE LINK "TEST1.WORLD"
    CONNECT TO "SHEDI" IDENTIFIED BY VALUES '06D04CB63F9E2712772C153397AC6EBC5C6412B4A5E4830D7251120E4E4A782AD6AAFB8262C57E9E0E51CE3DDCADA95F5AC906DC1EE1B26587FE963E24AB9E0F0C7149E05F5044711EA10F8F90E402F5000187BBB499B9601108C45634D8BDCB2A8556B2B11C8D9A280322B72A4B4411C6E71EBEA130F0CDBCDF5AFF95BE7997'
    14:50:23 2 14:50:23 3 USING 'ODSQA.WORLD';
    CREATE PUBLIC DATABASE LINK "TEST1.WORLD"
    *
    ERROR at line 1:
    ORA-02153: invalid VALUES password string

    OdpowiedzUsuń

Prześlij komentarz