database link re-creation script

How to create a script to generate a script to recreate existing database links in oracle 10g db.

Note :- Kindly note down this is oracle 10g version and passwords are encrypted.

Any suggestion/trick !!!!!!!!!


Thanks in advance.
kamisidAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
johnsoneConnect With a Mentor Senior Oracle DBACommented:
Here is how I used to figure these things out for myself.

Do an export of the user that owns the database link, or create a user and a dummy link and export that.  Then do an import with show=y and the command to recreate the link should be in the output.  There is probably an undocumented syntax to put the encrypted password back in.
0
 
hqassapConnect With a Mentor Commented:

Use this:
SELECT DBMS_METADATA.GET_DDL('DB_LINK', db_link, '{owner}') FROM all_db_links;


0
 
kamisidAuthor Commented:
is it possible to pull all daabase links from all users by using dbms_metadata.get_ddl('DB_LINK'.....)  ?
0
 
hqassapCommented:

Typically, yes; but practically, there are some bugs with extracting all DB_Links in one statement. It is better to extract DB_Links schema by schema by login to the DB_LINKS owner and then execute the following statement, then switch to the next schema and execute the statement again and so on..

SELECT DBMS_METADATA.get_ddl ('DB_LINK', db_link)
  FROM user_db_links;

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.