Re: create a database link without modifying tnsnames.ora

christianboivin1_at_hotmail.com (Chris) wrote in message news:<da20daf0.0201230508.5513b599_at_posting.google.com>...
> Hi
>
> You don't have to change the tnsnames
> to create the DBLink
> i don't think you can connect to another DB
> in Pl/sql without a dblink
>
> hth
> Chris
>
> MJWood <maryjanewood_at_hotmail.com> wrote in message news:<k7ns4u40gqmg9rl4vddrrp2egulp5bgl1l_at_4ax.com>...
> > Hi all,
> >
> > I have the ip address, port number, database name, login name and
> > password of several Oracle databases. I usually write java program to
> > do all my job bacause a simple url can connect all these databases
> > easily, e.g. DriverManager.getConnection
> > ('jdbc:oracle:thin:_at_123.123.123.123:1521:ORCL', 'name', 'pwd')
> > will be enough to create a connection. Then I can get data from one
> > database, modify it and transfer to another database. It is slow, but
> > always works.
> >
> > Now I am asked to write some PL/SQL for similar batch processing. I
> > need to create some database links but I want to do it dynamically. I
> > don't want to modify the tnsnames.ora nor bother our DBA to create a
> > link for me (it will take forever to wait...).
> >
> > Anyone knows a convenient way to do it ? Any suggestion is welcome !

< > are not part of the syntax they only show what you have to put in A database link is not hard to create two lines will let you do that If you have some third party tool inspect it carefully most of them have
a GUI method to create a database link , you may have to ask your dba to grant you create public database link priviledge and that will be it or
if you do not have

There are two ways of doing it
1) create it as to connect and work as a user found in the other database.
2)create it to attach to the other database For security reasons I normally give more leverage to #2

1. create public database link <link name > using <'source connect string'>
2. create public database link as connect to <name of user you want to connect as> identified by < the users password> using <'the connect string'>

Tell your dba to grant your create public database link priviledges and you should hopefully already have create session priviledges. I normally use my link name the same as my remote database name. There may be some issues like setting global name to false which your dba should be able to alter from sqlplus. Hope this helps and the dba should be pretty cooperative when what he needs is all figured out for him
Hope that helps
Michael Tubuo Ngong(DBA) Received on Wed Jan 23 2002 - 20:46:21 CET