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@xxxxxxxxxxxxx>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@xxxxxxxxx> 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@xxxxxxxxxxxxx>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@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: >> 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@xxxxxxxxx> >> 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 sys.link$. How can I over come this in 10g >>> >>> >>> Regards, >>> Nagaraj >>> >> >> >