Solved

database link re-creation script

Posted on 2008-10-10
6
2,304 Views
Last Modified: 2013-12-18
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
Comment
Question by:kamisid
  • 2
6 Comments
 
LVL 34

Accepted Solution

by:
johnsone earned 250 total points
ID: 22690548
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
 
LVL 4

Assisted Solution

by:hqassap
hqassap earned 250 total points
ID: 22747807

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


0
 

Author Comment

by:kamisid
ID: 22780833
is it possible to pull all daabase links from all users by using dbms_metadata.get_ddl('DB_LINK'.....)  ?
0
 
LVL 4

Expert Comment

by:hqassap
ID: 22802263

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question