DBMS_METADATA.GET_DDL database link password missing

Another annoying bug that was “introduced” in 11.2.0.4.

11.2.0.3:

$ sqlplus test/test

SQL*Plus: Release 11.2.0.3.0 Production on Sat Apr 12 15:01:02 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management,                                                                                                                                                                                                                                              OLAP,
Data Mining and Real Application Testing options

SQL> create database link dblink connect to user1 identified by password1 using 'ORCL';

Database link created.

SQL> set long 99999 lines 100
SQL> col ddl format a100
SQL> select dbms_metadata.get_ddl('DB_LINK', 'DBLINK', user) as ddl from dual;

DDL
----------------------------------------------------------------------------------------------------

  CREATE DATABASE LINK "DBLINK"
   CONNECT TO "USER1" IDENTIFIED BY VALUES '063E28DC19A9C9D6724B8F76A9980676231666CA76CF563B1B91ABCC
DE3CA1352BF3BA1D50EF14843F59ED6289B6709A0360C3B4DBA099A09A88C4F40E317362DBF0171A43E87AF4F5E2E03BED6F
F7CB1DB100DDC531257D0CF9B11EB2E1773E53A82812F39BDEA33AA3BC837C367FFA5A3CBBD4B48B9B33CAC55F3306CA4A2B
'
   USING 'ORCL'

Now, do the same on 11.2.0.4:

$ sqlplus test/test

SQL*Plus: Release 11.2.0.4.0 Production on Sat Apr 12 15:04:56 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create database link dblink connect to user1 identified by password1 using 'ORCL';

Database link created.

SQL> set long 99999 lines 100
SQL> col ddl format a100
SQL> select dbms_metadata.get_ddl('DB_LINK', 'DBLINK', user) as ddl from dual;

DDL
----------------------------------------------------------------------------------------------------

  CREATE DATABASE LINK "DBLINK"
   CONNECT TO "USER1" IDENTIFIED BY VALUES ':1'
   USING 'ORCL'

IDENTIFIED BY VALUES ‘:1′.

Let’s check what happens at a Data Pump export/import:

$ expdp test/test directory=data_pump_dir include=DB_LINK
$ impdp test/test directory=data_pump_dir dumpfile=expdat.dmp sqlfile=dblink.sql
$ cat dblink.sql
-- CONNECT TEST
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: SCHEMA_EXPORT/DB_LINK
CREATE DATABASE LINK "DBLINK"
   CONNECT TO "USER1" IDENTIFIED BY VALUES ':1'
   USING 'ORCL';

Again. Lets try to create the dblink:

SQL> drop database link dblink;

Database link dropped.

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

Well, obviously. Now try to really import this time:

$ impdp test/test directory=data_pump_dir dumpfile=expdat.dmp

Import: Release 11.2.0.4.0 - Production on Sat Apr 12 15:18:29 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_FULL_01":  test/******** directory=data_pump_dir dumpfile=expdat.dmp
Processing object type SCHEMA_EXPORT/DB_LINK
Job "TEST"."SYS_IMPORT_FULL_01" successfully completed at Sat Apr 12 15:18:30 2014 elapsed 0 00:00:00

No errors.

$ sqlplus test/test
...
SQL> select dbms_metadata.get_ddl('DB_LINK', 'DBLINK', user) as ddl from dual;

DDL
----------------------------------------------------------------------------------------------------

  CREATE DATABASE LINK "DBLINK"
   CONNECT TO "USER1" IDENTIFIED BY VALUES ':1'
   USING 'ORCL'

Seems only the representation is affected. Of course, it is possible to get the “password” from the appropriate dictionary table:

SQL> select name, passwordx from sys.link$

NAME       PASSWORDX
---------- ------------------------------------------------------------------------------------------
DBLINK     06B821C0DDB4B807F089211BDF617CEEBD5583CA4B80ADDA2C8EDFCE574A31407C6D19E2BBD1F3D637556A94C8
           27A4B4CAC423767A69AD205BEDD97CF6D7B5112A40E06BB2C1E4AFE5C37C5AB63167D332B7E1217E69A10A7F6D
           8E212EDF2B5400DFDB9B29A23088C8F8E4760A27104AFCFC5F86D44CEB82B1C97169DE956A5

There is no fix for this yet, last update on MOS regarding this: “development is currently working on this bug”.

5 Responses to DBMS_METADATA.GET_DDL database link password missing

  1. mdinh says:

    Thanks so much for this as I was going crazy and glad I found your post!

  2. Pingback: Missing Password for Database Link Bug | Thinking Out Loud

  3. 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;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: