Re: Dblink in Oracle10g

From: Nagaraj S <nagaraj.chk_at_gmail.com>
Date: Wed, 19 Nov 2008 16:38:44 +0530
Message-ID: <e921f8570811190308m3e557aeah9b3f8bd08a20be01@mail.gmail.com>

perfect... thanks a lot ..i used method 2 and created a dynamic script as below to create the dblink

SELECT
'create '||DECODE(U.NAME,'PUBLIC','public ')||'database link '||CHR(10)

||DECODE(U.NAME,'PUBLIC',Null, U.NAME||'.')|| L.NAME||chr(10)
||'connect to ' || L.USERID || ' identified by values'''
||L.PASSWORDX||''' using ''' || L.host || ''''
||chr(10)||';' TEXT
FROM  sys.link$       L,
sys.user$       U

WHERE L.OWNER# = U.USER# ; On Tue, Nov 18, 2008 at 11:39 PM, Dan Norris <dannorris_at_dannorris.com>wrote:

> Bradd tested both methods and both work fine (note "identified by values"
> clause in #2). So, it seems to be a matter of preference.
>
> Dan
>
> Sent from my iPhone
>
> On Nov 18, 2008, at 12:54 PM, "Nagaraj S" <nagaraj.chk_at_gmail.com> wrote:
>
> I think step 1 will be ok..because in 10g we will not be able to get
> dblink password from sys.link$, password will be in hexadecimal format
>
> Regards,
> Nagaraj.
>
> On Tue, Nov 18, 2008 at 10:04 PM, Dan Norris <dannorris_at_dannorris.com>wrote:
>
>> Cleaner how? Seems to me they are equivalent, but #2 takes a lot less
>> setup, no?
>>
>> Dan
>>
>> Sent from my iPhone
>>
>> On Nov 18, 2008, at 10:02 AM, "Bradd Piontek" <piontekdd_at_gmail.com>
>> 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:
>> CONTENT=METADATA_ONLY
>> FULL=Y
>> INCLUDE=DB_LINK
>>
>> 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 sys.link$;
>>
>> 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_at_gmail.com>
>> nagaraj.chk_at_gmail.com> 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 sys.link$. How can I over come this in 10g
>>>
>>>
>>> Regards,
>>> Nagaraj
>>>
>>
>>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Nov 19 2008 - 05:08:44 CST