Re: Dblink in Oracle10g

  • From: Yechiel Adar <adar666@xxxxxxxxxxxx>
  • Date: Tue, 18 Nov 2008 18:39:39 +0200

This will not work with links that are private.
If user James has a private dblink you need to create dblink James.dblink
and for this you need the owner.
The name of the owner is not in link$. there is only user number that you need to link to all_users by user_id.

Adar Yechiel
Rechovot, Israel

Dan Norris wrote:
Cleaner how? Seems to me they are equivalent, but #2 takes a lot less setup, no?


Sent from my iPhone

On Nov 18, 2008, at 10:02 AM, "Bradd Piontek" <piontekdd@xxxxxxxxx <mailto:piontekdd@xxxxxxxxx>> wrote:

You may want to look at how datapump handles this in 10g. If memory serves (queue the Iron Chef music), datapump now uses the same syntax as 'create user identified by values 'HASH');

I see two ways to accomplish this:
1. use Datapump to export the database links.
     PARFILE would look something like this:

    you can then use datapump to import into your dev database.

2. I did a quick test of this with a database link and it worked.
      select name,userid,passwordx,host from$;

   I then constructed a create statement:
create database link <name> connect to <userid> identified by values '<passwordx>' using '<host>';
   I was able to use the link.

I think method 1 is a lot cleaner, however.

Bradd Piontek
  "Next to doing a good job yourself,
        the greatest joy is in having someone
else do a first-class job under your direction."
 -- William Feather

On Tue, Nov 18, 2008 at 7:27 AM, Nagaraj S <nagaraj.chk@xxxxxxxxx <mailto:nagaraj.chk@xxxxxxxxx>> wrote:

    Hi Gurus,

    I have a requirement. I need to drop all the DB links in the
    Development database  and recreate them as of Production
    database. How can i do that...

    Am aware on oracle 9i we can use dynamic script to create dblink
    by querying$. How can I over come this in 10g


Other related posts: