Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2336
  • Last Modified:

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.
0
kamisid
Asked:
kamisid
  • 2
2 Solutions
 
johnsoneSenior 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
 
hqassapCommented:

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

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now