Oracle 11.2.0.3 on Oracle Linux 5, 64 bit
I probably just need a second set of eyes ...
SQL> show user
USER is "ESTEVENS"
SQL> select owner, db_link from dba_db_links
2 ;
PUBLIC AAA
PUBLIC BBB.WORLD
PUBLIC CCC.WORLD
PUBLIC CCC
PUBLIC DDD.WORLD
PUBLIC EEE
PUBLIC FFF
PUBLIC GGG
PUBLIC HHH
PUBLIC JJJ
PUBLIC KKK_UPDATE.WORLD
PUBLIC KKK.WORLD
PUBLIC LLL.WORLD
PUBLIC MMM_UPDATE.WORLD
PUBLIC MMM.WORLD
15 rows selected.
SQL> --
SQL> declare c clob;
2 begin
3 for t in (select db_link
4 from dba_db_links
5 )
6 loop
7 select dbms_metadata.get_ddl('DB_LINK',t.db_link) into c from dual;
8 dbms_output.put_line(c);
9 dbms_output.put(';');
10 end loop;
11 end;
12 /
declare c clob;
*
ERROR at line 1:
ORA-31603: object "AAA" of type DB_LINK not found in schema
"ESTEVENS"
ORA-06512: at "SYS.DBMS_METADATA", line 5088
ORA-06512: at "SYS.DBMS_METADATA", line 7589
ORA-06512: at line 1
ORA-06512: at line 7
Yes, user 'ESTEVENS' has dba role.
Same pl/sql block works as expected when selecting from dba_tablespaces and dba_directories.
Googled the error message and the most promising link was ORA-31603 on dbms_metadata.get_dll for dblinks only but it was abandoned without final resolution. There, the OP seemed to have the same issue - works for "everything" except dba_db_links.