Solved

database link re-creation script

Posted on 2008-10-10
6
2,316 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
6 Comments
 
LVL 35

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

Want Experts Exchange at your fingertips?

With Experts Exchange’s latest app release, you can now experience our most recent features, updates, and the same community interface while on-the-go. Download our latest app release at the Android or Apple stores today!

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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 syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

631 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