Nice answer Tom. This one was puzzling Joel and I.

Hi,

How to refer DB link inside a procedure ?

insert into emp select * from emp@aaa@test;
is working from SQL PLUS where aaa@test is my dblink

But procedure

create or replace procedure proc_ins_emp
as
begin
insert into emp select * from emp@aaa@test;
end;
/

Is created with errors mentioning

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/2 PL/SQL: SQL Statement ignored
4/32 PLS-00201: identifier 'EMPAAA@TEST' must be declared

Thanks for your help.

Regards,
Janna

From your original response:

<QUOTE>

scott@ORA816> create database link wont_work
2 using 'ora8i.world'
3 /

Database link created.

scott@ORA816> select * from dual@wont_work
2 /
select * from dual@wont_work
*
ERROR at line 1:
ORA-02085: database link WONT_WORK.US.ORACLE.COM connects to ORA8I.WORLD

</QUOTE>.

Your 'CREATE DATABASE LINK WONT_WORK ....' commad created a database link named WONT_WORK.US.ORACLE.COM. Where did the US.ORACLE.COM came from? Is it the default domain name from sqlnet.ora? Why would that be appended to the database link name? Is there anyway to create the database link without this appending?

I have a situation like this: I created a dblink (named RASD) pointing to another server (with db_name and service_name RASD.). It does not, however, work (gettin ora-2085) as the dblink created on my db has a name RASD.world (world is my default domain name) and that is not equl to the service name of the remote DB. The DBA of the remote DB is not ready to change the service name to RASD.world. And, I am not allowed to change the global_names setting on this db. What can I do here? Thank you for your valuable response.

RN.

Global_name=false in remote DB and =true in the db where I want to create the dblink.

Tom:

I an using 8.1.7 database and have no problem in creating database links with any name. I did not see this limitation.

Create database link anyname
connect to scott identified by tiger
using 'emsd';

can you explain how am i not limited?

2. Would there be a difference if a developer account created a link rather than "PUBLIC" owner.

thanks,

TOm:

1. what about whether is there a difference if a developer created a link or a dba.

one is owned by "public" and the other by "dev". would it affect anything?

Sorry I confused global_name and global_names!

The global_name of the remote DB is RASD. And I am trying to create a dblink called RASD. But Oracle won't let me do that - it instead creates a dblink called RASD.world. Can you tell me why? Since RASD != RASD.world, I am getting the error.

So this means: If you have global_names=true, you can not create database links pointing outside your database domain? Did I understand it right?

I don't know how I can get around this. The DBA of the remote database is not willing to change anything - he does not want to touch his 'perfectly running' database. And my DBA is also reluctant for changing the global_names=true setting (eventhough he didn't put it..It seems to be there historically....!). We have several database links in this db (all pointing to databases within our database domain). Can you think of any negative effect of setting global_names=false?

Thank you for being there for us even on holidays. We greatly appreciate your help.

rgds,

RN

Tom:

When you have a web application as stored procedure in the database, how is the user connecting to the database.

Is it the account defined in the DAD which is either developer or public?

2. If the DAD username is public account then he can not run the database links created by developer. Is this true?

Thanks

Tom:

As a followup to the previous message, the user runs a login procedure where he enters username/password.

However that means he has already logged in since he ran the procedure. I am assuming you always loggin in with the userid/password defined in the DAD. Correct?

Hi Tom,

scott@ORA816> create database link ora8i.world@scott_user
2 connect to scott
3 identified by tiger
4 using 'ora8i.world'
5 /

scott@ORA816> select * from dual@ora8i.world@scott_user
2 /

D
-
X

base on above example

How the entry will start in tnsnames.ora of server

for example

scott_user.WORLD=(DESCRIPTION=
(ADDRESS_LIST =(
ADDRESS = (COMMUNITY = NMP.world)
(PROTOCOL = NMP)(SERVER = 192.9.200.250)
(PIPE = ORAPIPE))(ADDRESS = (PROTOCOL = TCP)
(Host = 192.9.200.250)(Port = 1521))(ADDRESS = (PROTOCOL = TCP)
(Host = 192.9.200.250)(Port = 1526))(ADDRESS = (PROTOCOL = TCP)
(Host = 127.0.0.1)(Port = 1521))
(ADDRESS = (PROTOCOL = SPX)(Service = host_doc_lsnr)))
(CONNECT_DATA=(SID=ORCL)
(SERVER=DEDICATED)
)
)


is this right (scott_user.WORLD ) ?

Hi tom,

I have tried following way still I am getting error 

SQL> select substr(name,1,20) name ,value from V$parameter
  2  where name like 'global_name%'
  3  /

NAME                 VALUE
-------------------- -----------------------------------------------
global_names         TRUE

SQL> set  ARRAYSIZE 1
SQL> select * from global_name;
GLOBAL_NAME
---------------------------------------------------
ORACLE.WORLD

SQL> CREATE DATABASE LINK ORACLE.WORLD@DP
  2  CONNECT TO DPMS
  3  IDENTIFIED BY DPMS
  4  USING 'ORACLE.WORLD'
  5  /

Database link created.

SQL> select * from dba_db_links;

OWNER      DB_LINK              USERNAME   HOST                 CREATED
---------- -------------------- ---------- -------------------- ---------
NS         ABC.WORLD            DPMS       ABC                  06-SEP-03
NS         DLF.WORLD            DPMS       dlf                  13-MAR-02
NS         ORACLE.WORLD@DP      DPMS       ORACLE.WORLD         09-SEP-03
PAYROLL    DLF.WORLD            PAYROLL    dlf                  13-MAR-02


SQL> select * from dual@ORACLE.WORLD@DP;
select * from dual@ORACLE.WORLD@DP
                   *
ERROR at line 1:
ORA-02085: database link ORACLE.WORLD@DP connects to ORCL

Regards + Thanks 

Deepak 

Hi Tom,

As you have said above I created a database link from the local database (Oracle version is 8.1.7) to a remote database (Oracle version is 8.1.6).

CREATE DATABASE LINK service_name@scott_tiger
CONNECT TO scott
IDENTIFIED BY tiger
USING 'service_name';

Here service_name is the remote database service name which I use to connect directly to that database. (like connect scott/tiger@service_name)

Now

SELECT * from dual@service_name@scott_tiger;

gives me the error:

ORA-02085: database link servive_name.US.ORACLE.COM connects to service_name

Then I obtained the global database name to the remote database from the listene.ora file (ie remote_dbname.world)
and created another database link as

CREATE DATABASE LINK remote_dbname.world@scott_tiger
CONNECT TO scott
IDENTIFIED BY tiger
USING 'remote_dbname.world';

Any attempt to executed a

SELECT * from dual@remote_dbname.world@scott_tiger;

is resulting in a ORA-12154.

1)Is that because of any version difference ?

2)Or is it not possible to get data from a remote database
from within a local instance using just the service_name?

3)Where did I went wrong?

Thanks for your valuble answer

Praveen

I have created the following script in Database A and database B
TYPE PLSQL_TAB IS TABLE OF VARCHAR2(100);

and created a DB link from B to A

and created the following procedure in A

CREATE OR REPLACE PROCEDURE proc_test(
out_tab OUT plsql_tab)
IS
BEGIN

--INSERT INTO TABLE CAST(out_tab AS plsql_tab)

SELECT ctry_cd
BULK COLLECT INTO out_tab
FROM ctry;

EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/


and i made a synonym of
create or replace proc_test for proc_test@;

and i tried getting the table data accesing the proc from b by using the following script

declare
a plsql_tab;
begin
proc_test(a);
end;
/

it gives me the following error

ERROR at line 1:
ORA-06550: line 4, column 1:
PLS-00306: wrong number or types of arguments in call to 'PROC_TEST'
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored

Please let me know if there is any solution for this. And i think we cannot pass the data thru refcursors also.

Thanks in advance
Raju

I have created the following script in Database A and database B
TYPE PLSQL_TAB IS TABLE OF VARCHAR2(100);

and created a DB link from B to A

and created the following procedure in A

CREATE OR REPLACE PROCEDURE proc_test(
out_tab OUT plsql_tab)
IS
BEGIN

--INSERT INTO TABLE CAST(out_tab AS plsql_tab)

SELECT ctry_cd
BULK COLLECT INTO out_tab
FROM ctry;

EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/


and i made a synonym of
create or replace proc_test for proc_test@;

and i tried getting the table data accesing the proc from b by using the following script

declare
a plsql_tab;
begin
proc_test(a);
end;
/

it gives me the following error

ERROR at line 1:
ORA-06550: line 4, column 1:
PLS-00306: wrong number or types of arguments in call to 'PROC_TEST'
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored

Please let me know if there is any solution for this. And i think we cannot pass the data thru refcursors also.

Thanks in advance
Raju

ops$tkyte@ORA920> @connect 'ops$tkyte/xxx@aria-dev.us.oracle.com'

ops$tkyte@ORA920> create or replace package my_pkg
  2  as
  3          type array is table of varchar2(100);
  4
  5          procedure proc_test( out_tab out array );
  6  end;
  7  /
Package created.
 
ops$tkyte@ORA920> create or replace package body my_pkg
  2  as
  3
  4  procedure proc_test( out_tab out array )
  5  is
  6  begin
  7          select username bulk collect into out_tab
  8            from all_users;
  9  end;
 10
 11  end;
 12  /
Package body created.
 
ops$tkyte@ORA920> @connect /
 
ops$tkyte@ORA920PC> create database link ora920
  2  connect to ops$tkyte
  3  identified by xxx
  4  using 'aria-dev.us.oracle.com'
  5  /
 
Database link created.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> create or replace synonym my_pkg for my_pkg@ora920
  2  /
 
Synonym created.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> declare
  2          a  my_pkg.array;
  3  begin
  4          my_pkg.proc_test( a );
  5          dbms_output.put_line( a.count );
  6  end;
  7  /
41
 
PL/SQL procedure successfully completed.
 

Hi Tom
  
 I am trying make a database link to be used for replication
but the link wasn't working giving the following error 

ORA-02085: database link REP.WORLD connects to ORCL
 
ORCL - MASTER SITE
REP  - REPLICATION TO BE SENT

I have created with the following command

CREATE DATABASE LINK rep.WORLD CONNECT TO repadmin IDENTIFIED BY repadmin USING 'rep.WORLD';

Required information :
SQL> select * from  global_name;

GLOBAL_NAME
-------------------
ORCL

* same for both SITE coz i am creating on same machine

PARAMETERS VALUES

GLOBAL_NAMES      TRUE
DB_DOMAIN         world

i also tried with GLOBAL_NAME
 CREATE DATABASE LINK orcl@rep CONNECT TO repadmin IDENTIFIED BY repadmin USING 'rep.WORLD'

It gives the following error
ORA-23378: connection qualifier "@REP" is not valid for  
            object group "PUBLIC"."MYREPGRP"

urgent help is required from your site.
 

Hi Tom

   Thanx, I alter my database global_name to REP.WORLD and links are working fine.

Now i wanna know why links created wtihout using " CONNECT TO username IDENTIFIED BY password" are retuns this error  
ORA-01005: null password given; logon denied
  
e.g

SQL> CREATE DATABASE LINKS rep.world USING 'rep.world';
SQL> SELECT * FROM scott.emp@rep.world;

ORA-01005: null password given; logon denied
Pls tell me where i m wrong. Also tell how can we execute DDL using database links.

Thanx & Regards 

Hi Tom
   
             The full cut and paste is ......

ORCL:SQL> CONN / @ORCL.WORLD AS SYSDBA
connected
ORCL:SQL>select name from v$database;

NAME
---------
ORCL

ORCL:SQL>SHOW PARAMETER GLOBAL

ORCL:SQL>SHOW PARAMETER GLOBAL_NAME

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------global_names                         boolean     TRUE

ORCL:SQL>SELECT * FROM GLOBAL_NAME;

GLOBAL_NAME

ORCL.WORLD

ORCL:SQL>CONN / @REP.WORLD AS SYSDBA
Connected.

ORCL:SQL>select name from v$database;

NAME
---------
REP
ORCL:SQL>SHOW PARAMETER GLOBAL_NAME

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------global_names                         boolean     TRUE

ORCL:SQL>SELECT * FROM GLOBAL_NAME;

GLOBAL_NAME

REP.WORLD

ORCL:SQL>CONN / @ORCL.WORLD AS SYSDBA
Connected.

ORCL:SQL>CREATE DATABASE LINK REP.WORLD USING 'REP.WORLD';

Database link created.

ORCL:SQL>SELECT * FROM SCOTT.DEPT@REP.WORLD;
SELECT * FROM SCOTT.DEPT@REP.WORLD
                        *
ERROR at line 1:
ORA-01005: null password given; logon denied


ORCL:SQL>DROP DATABASE LINK REP.WORLD;

Database link dropped.

ORCL:SQL>CREATE DATABASE LINK REP.WORLD CONNECT TO SYSTEM  
          IDENTIFIED BY MANAGER USING 'REP.WORLD';

Database link created.

ORCL:SQL>SELECT * FROM SCOTT.DEPT@REP.WORLD;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

 Please, tell me why i am not able to fetch rows with database links created without "CONNECT TO system IDENTIFIED BY manager" clause. If possible give me cut and paste of syntax

Thanx $ Regards
 

Thanx Tom, my links are working. Can we execute ddl using database links, if yes then how?

Thanx & Regards

Hi Tom
 
Now problem is that my database links in one database is working fine  but in 2nd database giving the error:

ORA-02085: database link ORCL.WORLD connects to REP.WORLD

For your information cut and paste of both database is:

Database: ORCL.WORLD

ORCL:SQL>CONN SYSTEM/MANAGER@ORCL.WORLD
Connected.
ORCL:SQL>SELECT NAME FROM V$DATABASE;

NAME
---------
ORCL

ORCL:SQL>CREATE DATABASE LINK REP.WORLD USING 'REP.WORLD';

Database link created.

ORCL:SQL>SELECT GLOBAL_NAME FROM GLOBAL_NAME@REP.WORLD;

GLOBAL_NAME

REP.WORLD

ORCL:SQL>SELECT GLOBAL_NAME FROM GLOBAL_NAME
  2  ;

GLOBAL_NAME

ORCL.WORLD

ORCL:SQL>show parameter global_names

NAME                      TYPE        VALUE
----------------------- ----------- -----------
global_names              boolean     TRUE

Database: REP.WORLD

REP:SQL>CONN SYSTEM/MANAGER@REP.WORLD
Connected.
REP:SQL>SELECT NAME FROM V$DATABASE;

NAME
---------
REP

REP:SQL>CREATE DATABASE LINK ORCL.WORLD USING 'ORCL.WORLD';

Database link created.

REP:SQL>SELECT GLOBAL_NAME FROM GLOBAL_NAME@ORCL.WORLD;
SELECT GLOBAL_NAME FROM GLOBAL_NAME@ORCL.WORLD
                                    *
ERROR at line 1:
ORA-02085: database link ORCL.WORLD connects to REP.WORLD


REP:SQL>SELECT GLOBAL_NAME FROM GLOBAL_NAME
  2  ;

GLOBAL_NAME

REP.WORLD

REP:SQL>show parameter global_names

NAME               TYPE        VALUE
---------------- ----------- --------------
global_names      boolean     TRUE

For your Reference entery is tnsnames.ora is :

REP.world =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ORACLE)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID =REP)
    )
  )

ORCL.world =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ORACLE)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = ORCL)
    )
  )

Pleas help

Thanx & Regards 

REP:SQL>SELECT GLOBAL_NAME FROM GLOBAL_NAME@ORCL.WORLD;
SELECT GLOBAL_NAME FROM GLOBAL_NAME@ORCL.WORLD
                                    *
ERROR at line 1:
ORA-02085: database link ORCL.WORLD connects to REP.WORLD

says it all.


orcl.world on that server is pointing to rep.world, not your ORCL instance.

remember the tnsnames on the SERVER - on the database SERVER is what counts.  not your local copy. 

Hi Tom
      Yes ORCL.WORLD database link connects to REP database not to ORCL database! but why i don't know. if i try to connect using 
 
SQL> CONNECT system/manager@ORCL.WORLD
connected

SQL> SELECT name from V$DATABASE;
----------------
REP
  
I am in deep trobule why this happened, why not it connects to ORCL database ?.  

but, a trick works  a new LISTENER is created with the folowing configuration 
NAME : LISTENER1 HOST : oracle PORT : 1525

sample  tnsnames.ora
 ORCL.WORLD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ORACLE)(PORT = 1525))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL.WORLD)
    )
  )

now i am able to connnect to ORCL database using ORCL.WORLD service. My links are also working.

but pls tell me why this type of thing happened also explian your statement --- " local copy "

" remember the tnsnames on the SERVER - on the database SERVER is what counts. not your local copy. "

Thanx & Regards
 

Hi Tom

I am doing this excercise on same machine so, no client and server tnsnames.ora file. Only one file resides.

Thanx & Regards

Tom,
I want to create the database link dynamically. I am able use 'create database link' statement and create the database link dynamically. I need to then use the for loop to loop through and create synonyms for the tables that need the data to be moved. When I use the
for x in (select * from data_tab@&&db_link) loop
I am getting the error stating that the database name has an invalid character.
My database domain name has a hyphen '-'. (instance_name.ab.cde-efg.com). When I hardcode the value in the for loop like
for x in (select * from data_tab@"instance_name.ab.cde-efg.com") it works fine. How can I use the variable in the for loop?

Thank you for your help.

You solved my problem two different ways.

Tahnks so much!

ERROR at line 1:
ORA-02085: database link xxxLINK.US.ORACLE.COM connects to
CAIS2.US.ORACLE.COM

hi tom,

i have a db-link.

manhatten@TKXXL.XXX.YYY.ZZZ> select * from user_db_links;

DB_LINK
--------------------------------------------------------------------------------------------------------------------------------
USERNAME PASSWORD
------------------------------ ------------------------------
HOST
--------------------------------------------------------------------------------------------------------------------------------
--------------------
CREATED
-------------------
IWM.XXX.YYY.ZZZ
MANHATTAN XXXXXXXXX
IWM
2005-01-14 13:27:06

manhatten@TKXXL.XXX.YYY.ZZZ> select * from global_name@iwm;

GLOBAL_NAME
--------------------------------------------------------------------------------------------------------------------------------
--------------------
IWM.XXX.YYY.ZZZ

when i look at the global name at the remote database i think all is fine.

i have the synonym UCLK_TO_WM_LOCATE_LPN from UCLK_TO_WM_LOCATE_LPN@iwm. i can insert into
this remote table without problem

manhatten@TKXXL.XXX.YYY.ZZZ> insert into UCLK_TO_WM_LOCATE_LPN ( msg_id ) values ( 1);

1 Zeile wurde erstellt.

manhatten@TKXXL.XXX.YYY.ZZZ> rollback;

Transaktion mit ROLLBACK rückgängig gemacht.

but when using a sequence

manhatten@TKXXL.XXX.YYY.ZZZ> insert into UCLK_TO_WM_LOCATE_LPN ( msg_id ) values ( seq_locate_lpn.nextval );
insert into UCLK_TO_WM_LOCATE_LPN ( msg_id ) values ( seq_locate_lpn.nextval )
*
FEHLER in Zeile 1:
ORA-02019: connection description for remote database not found
ORA-02063: preceding line from IWM

then i do as required

manhatten@TKXXL.XXX.YYY.ZZZ> alter session set global_names=true;

Session wurde geändert.

but i still have a problem.

manhatten@TKXXL.XXX.YYY.ZZZ> insert into UCLK_TO_WM_LOCATE_LPN ( msg_id ) values ( seq_locate_lpn.nextval );
insert into UCLK_TO_WM_LOCATE_LPN ( msg_id ) values ( seq_locate_lpn.nextval )
*
FEHLER in Zeile 1:
ORA-02019: connection description for remote database not found
ORA-02063: preceding line from IWM

manhatten@TKXXL.XXX.YYY.ZZZ> rollback;

Transaktion mit ROLLBACK rückgängig gemacht.

also when running a package...

manhatten@TKXXL.XXX.YYY.ZZZ> begin
2 dbms_output.put_line(manhatten_com.STATE_WRITTEN);
3 end;
4 /
10

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

manhatten@TKXXL.XXX.YYY.ZZZ> insert into UCLK_TO_WM_LOCATE_LPN ( msg_id ) values ( manhatten_com.STATE_WRITTEN );
insert into UCLK_TO_WM_LOCATE_LPN ( msg_id ) values ( manhatten_com.STATE_WRITTEN )
*
FEHLER in Zeile 1:
ORA-04052: error occurred when looking up remote object MANHATTEN.MANHATTEN_COM@TKXXL.XXX.YYY.ZZZ
ORA-00604: error occurred at recursive SQL level 1
ORA-02019: connection description for remote database not found
ORA-02063: preceding 3 lines from IWM


manhatten@TKXXL.XXX.YYY.ZZZ> rollback;

Transaktion mit ROLLBACK rückgängig gemacht.

manhatten@TKXXL.XXX.YYY.ZZZ> select * from user_db_links;

DB_LINK
----------------------------------------------------------------------------
USERNAME PASSWORD
------------------------------ ------------------------------
HOST
----------------------------------------------------------------------------
--------------------
CREATED
-------------------
IWM.XXX.YYY.ZZZ
MANHATTAN XXXXXXXXX
IWM
2005-01-14 13:27:06

the db-link is still o.k.

cant i use sequences and packages this way on a db-link? I got the impression the database
wants to use the package and sequence from the remote database not from mine.

thanks

Joachim

hi i want to break any yahoo id/yahoo mail so plz give me the method for it that what is processes

Hi Tom,
We are using Oracle 9.0.1.3.0 and Forms6i. Two tier architechure.
We have 2 database on same machine db1 and db2 having usr1 and usr2 as user respectively on the database.We are fetching the data for usr2 by using dblink "dblink". But some time it is very slow.Can u tell me ,whether i missed out anything which causes the database.

Hello Tom,

We have oracle Database installed on RED HAT AS 3.1.
Database version is 9.2.0.5.0.

We also have two web servers connected to above database server.
Just before live production, Management asks me to change domain name.
1) My question is “Does changing domain name effect any thing from Oracle DB perspective"?
2) Do I need to configure any thing from Oracle database?

Regards,
Dawar

Hi Tom,

When I create database link ex. test it is getting created as test.world instead of test.<domain_name>.

I looked in user_db_links there are 3 db links with db_link_name.<domain_name> and 2 db links with db_link_name.world, so my question is how does oracle appends as .world some times and .domain_name some times what is the logic behind this?

on both the databases global_names=false. when I issue " select global_name from global_name" on remote db It is returning <domain_name>, when I issue same sql on the database where I am creating link it is giving me instance_name.world. What is the source of this synonym global_name which table does it refer and which init parameter it takes?

I request to show some pointers to drill this down.

Thanks
Arya

Hi Tom,

db_domain=xyz.com(our company domain) on both the databases. global_dbname in the listener file also our company domain. default_domain under sqlnet.ora file is also good.

I am not able to understand from where it is picking value of global_name as devdb.world, since all the files refer to xyz.com.

Thanks
Arya

Tom,

I have two environment (test and production) and trying to create databae link from Test to Production for replication purposes. Followings are the parameter values in both DBs.

Parameters Test DB | PROD DB
-----------------------------------
db_name DEV1 IASDB
db_domain www2
global_name DEV1.www2 IASDB
global_names true false

I create a link :

1 CREATE DATABASE LINK "IASDB" CONNECT TO "PARGUS"
2 IDENTIFIED BY "kaNYA83"
3* USING 'PRODUCTION'

pargus@TestServer> /

Database link created.

pargus@TestServer> select count(*) from patmodule@IASDB;
select count(*) from patmodule@IASDB
*
ERROR at line 1:
ORA-02085: database link IASDB.WWW2 connects to IASDB

To make it work, i altered the session for global names to false on test. it worked.

I have some queries, please advise

1. I was underimpression that DB link IASDB will connect to PROD using 'connect' and 'using' parameters and saying don't worry for global_names as it is false on PROD. Why it looks for global_names value on TestServer.

2. Some where in this thread you suggested tp provide PROD with a db_domain value : is it as simple as adding db_domain ='ANYDOMAIN' in init.ora and restart the PROD.

3. If i alter the global name on production to say "IASDB.WWW2" (and global_names is true), Which way you reckon is better (2 or 3 step) and will have minimum impact on any other existing links.

4. For replication we need to have global_names to be true on both DB, why it is manadatory to have this parameter to true if two DB can talk to each other, while having false value.

Regards
Vivek

excellent.

But just want to know, how to chnage db_domain parameter
(any alter command or put db_domain in init.ora)


thanks

it is a parameter set in your parameter file.


ops$tkyte@ORA10GR1> alter system set db_domain = localhost scope=spfile; 
System altered.

and then restart. 

Hi Tom

I have 2 databases A and B on 2 diffrent servers.I have created a public Database link from A to B. 

SQL> create public database link B connect to b identified by manager using 'B';
(the schema/username is the same as the SID on the remote Database i.e. 'B')

I am able to select tables from database B using this database link, 
SQL> select count(*) from gt_do@B;

  COUNT(*)
----------
     64538

but when I use the same in a PL/SQL function and try to compile the function I get disconnected from Oracle with the Error
ORA-03113: end-of-file on communication channel

After connecting again via Enterprise manager and looking up the same function, the error is reported as 

Line # = 9 Column # = 10 Error Text = PL/SQL: ORA-04052: error occurred when looking up remote object B.GT_DO@B.WORLD ORA-00604: error occurred at recursive SQL level 1 ORA-12154: TNS:could not resolve service name

Do I need to contact support?

Thanks 

Dear Tom,

I have changed my 9i test database's global name by using

alter database rename global_name to dlf.orcl;

system@dlf.orcl> select * from global_name;

GLOBAL_NAME
-------------------------------------------
DLF.ORCL

system@dlf.orcl>

NOW, i want to change database's global_name back to previous setting i.e. only DLF. But i am unable to do this

i have tried all options (guessing) like "DLF", "DLF." "DLF.''"
but the domain name is still appearing in global_name

my setting of db_domain was NULL and it is still null.

How can i restore my old value in GLOBAL_NAME.

old : DLF
new : DLF.ORCL
I want DLF back.

Regards,

Dear Tom,

Thanks for your replay.

Is this inability of removing domain name from GLOBAL_NAME is a bug in oracle. When I can change the domains then why can't I set my global name with no domain, which actually oracle is also permitting (as in my previous setting).

What is the logic not to blank the domain again. This means I have to bear it for the rest whole of my life. why cant I divorce here if once I married here accidentally?

Regards,

Hi Tom,
Appreciate your help. The scenerio is like this :
Database A
SQL> select substr(name,1,20) name ,value from V$parameter
  2  where name like 'global_name%'
  3  /

NAME
------------
VALUE
--------------
global_names
FALSE

SQL> select * from global_name;

GLOBAL_NAME
--------------
PT_OMS.WORLD

Database B
SQL> select substr(name,1,20) name ,value from V$parameter
  2  where name like 'global_name%';

NAME
------------------------------------------------------------
VALUE
------------------------------------------------------------
global_names
FALSE
SQL> select * from global_name;

GLOBAL_NAME
-------------------------------
OMS.US.ORACLE.COM

From Database A, we want to access objects of Database B. So i created a DB link as follows :

CREATE DATABASE LINK OMS.US.ORACLE.COM
CONNECT TO oms IDENTIFIED BY oms
USING '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST= WEP06324)(PORT=1521)))(CONNECT_DATA=(SID=OMS.US.ORACLE.COM)(SERVER=DEDICATED)))'

Database Link created

When i try to describe any object from Database B :
desc oms_kunde@OMS.US.ORACLE.COM

The session hangs for sometime & throws ORA-12535.

Why is this happening ?

We are on Oracle 9.2.0.6.

Regards 

Hi Tom,

I have two oracle databases on different servers, one is billing system and another one is oracle CRM application , I have to retrive the customer data from billing system database to CRM application , is there any other way to get the data aprart from using DB links. If only DB links is only the best one then, whether dblinks will have any performance issues. Your inputs is appreciated.


Thanks and regards
Kiran

Tom,

How to use the DB links in pl/sql i.e. in procedures, functions, cursors. Please can you show me with one simple example.

Regards
M.P.Kiran Kumar

Hi

Can I use DB links in Oracle application as a concurrent programs registered under stored procedure.


Regards
Ramesh

I have the following setup:
1. Database - global name = 'DEVL.RFC.COM'
2. Object owner - 'POOL'
3. Application user = 'C_APOOL'
4. Two tables he_asset_unsold_a and he_asseT_unsold_b.
5. Synonym he_asset_unsold that points to any one of the two tables at a given point in time.
6. I also have a view HE_ASSET that is defined as

select * from he_Asset_unsold
union all
select * from he_Asset_sold

8. I have the following grant :

grant select on he_Asset to pool_Admin_Grp (this role includes c_apool id).

With this setup, everytime I switch the synonym around, the he_Asset view goes invalid.

You had provided a workaround (when you were here in Minneapolis on Aug 7th/8th) to use loopback database links to avoid the invalidations. So, I tried this

1. create public database link devl@loopback using 'DEVL.RFC.COM'
2. he_Asset definition is now:

select * from he_Asset_unsold@devl@loopback
union all
select * from he_Asset_sold@devl@loopback

The rest is the same.

Now, when I switch the synonym the he_Asset view does not go invalid. However when I try to query the view using the c_apool id I get the following error :

ORA 00942 table or view does not exist.

What's going on?

Thanks for your help.

Please ignore the previous question. It was a grant issue. Sorry for the trouble.

hi
1-)
global_names is false
and global name is:
select * from global_name;
ORCL.REGRESS.RDBMS.DEV.US.ORACLE.COM

During installation I gave the dbname as orcl.
What should I do to make dbname and global_name same?

2-)

CREATE PUBLIC DATABASE LINK ASNET
CONNECT TO ASNET
IDENTIFIED BY <pwd>
USING 'RAC1';

somehow the link name appears like:
ASNET.REGRESS.RDBMS.DEV.US.ORACLE.COM

what should I do?

despite
I rename global name
alter database rename global_name to ATM;
it still has old name.
select * from global_name
ATM.REGRESS.RDBMS.DEV.US.ORACLE.COM

OK, let me send a philosophical question, going way, way back to the original thread question.

You recommend not to:
alter session set global_names=false;
in your session, dblinks no longer have to be the global_name.
You can use your existing naming convention. Not recommended.

We are a large shop, and on occasion (not all that infrequently) move schemas and accounts from one instance to another. If you require all link names to equal the instance name, plus the addition of the qualifier representing the taret account, how do you easily handle moving a target account to a new instance? Wouldn't the practice of having global_names = false, and then creating link names independent of physical location (i.e., the instance), make this less impactful - that is, you only need change the definition of the link (the instance it targets) and then no query or other SQL that uses it need change?

Is the only drawback to having global names turned off related to replication - so, if we don't do much replication, is using link names that are independent of instance actually a best practice?
Thanks much.

Is it possible to get this idea working:

create procedure getdata(v_linkname varchar2)
as
cursor c_whatever is
select x, y from t@|| v_linkname || where x < 10;

begin
for z in c_whatever loop
...

as
  cursor c is sys_refcursor;
  l_x whatever_type;  
  l_y whatever_type;
begin
  
  open c for 'select x, y from t@' || dbms_assert.sql_object_name(v_linkname)
           || ' where x < 10';
  loop
      fetch c into l_x, l_y;
      .....

Hi Tom,

I am executing the following query:
select * from some_view@dblink_ex where id in(select to_number(id) from table );

the error its giving is
ORA-01722: invalid number ORA-02063: preceding line from dblink_ex

I tried using synonyms and views on some_view@dblink_ex too but the error persists.
Also when I am using the query as below then its working fine:
select * from some_view@dblink_ex where id in(1,2,3,4,5,6,7,8,9,10);

and when i am using the query as:
select * from datamanager.some_view@QABC.QDEF.XYZ.COM
its giving the following error:
ORA-02019: connection description for remote database not found

Please provide your suggestions.

Thanks and Regards,
Saurabh

Tom,

I have roughly the same question as Peter. You suggest that it is better to maintain Global_names = true and to re-create the link when moving a database schema. Why would re-creating the link help? As the name of the remote server is now different, wouldn't you also need to change each place in the PLSQL code (dozens or hundreds of statements) which refers to this link? I think it would be much easier to change a single dblink, rather than modifying each instance where the link is used.

One other situation which comes to mind is Dev vs QA vs Production.

My understanding is that if Global_names = true, you have two choices:

1.) Use a different DBLink across the environments
or
2.) Creating a remote synonym for each object (table, package, etc) used

For example, with Global_names = true:

select * from dual@dev_cust.mydomain.com;
select * from dual@qa_cust.mydomain.com;
select * from dual@prod_cust.mydomain.com;

vs (for all 3 environments)

select * from dual@cust;

Is this an accurate description or am I missing another option?

Thanks,
Bob

#1 differs from #2 in that for #1 the DB link would be directly used in the code, while for #2 there would be remote synonyms for each object:

#1:
select * from dual@dev_cust.mydomain.com;
select * from dual@qa_cust.mydomain.com;
select * from dual@prod_cust.mydomain.com;

#2:
Create synonym rs_cust_dual for dual@dev_cust.mydomain.com;
Create synonym rs_cust_dual for dual@qa_cust.mydomain.com;
Create synonym rs_cust_dual for dual@prod_cust.mydomain.com;

In all layers:
Select * from rs_cust_dual;


So the problem I see with #1 is that when the link changes, all your code needs to change (a bad thing). Also, your Dev, QA, and Production code would all need to differ- not something which would be easy to manage.

#2 may be workable, but it seems like more maintenance overhead. For instance, if a link changes from “qa1_cust.mydomain.com” to “qa2_cust.mydomain.com” (a common occurrence, as some of our apps have 3-4 QA environments to handle parallel development), you would need to re-create all the remote synonyms in the database, rather than just re-creating the link.

Can a synonym be created for a DB link itself, rather than the underlying remote objects? If so, that would seem ideal. But from what I’ve seen, it isn’t possible.

The risk of accidentally connecting to the wrong place while using a single DB link doesn’t seem very high to me. Not only would the DBA need to re-point the link to the wrong place (possible), there are firewalls between each layer (dev/qa/prod) which should prevent it. Using remote synonyms for each object actually sounds riskier to me. When changing from QA1 to QA2, it would definitely be possible to miss one of the synonyms. That would be a very hard issue to catch, as things may “sort of” work, with the code using both environments.

Tom,

>>> "I agree that using a private synonym or view would be a wise approach."


Are you saying that it is possible to create a synonym for a DBLink and use it directly, rather than creating one for each remote object? If so, can you please suggest how I would do this?

Thanks,
Bob

Hi Tom,
I have a statement like this (from a third party) as the top SQL in my ASH and AWR reports but it does not show in a trace file of the same session/time period.

SELECT "USER_ID", "NOUTLET" FROM "QRY_V_OUTLETS_RIGHTS" "A1" WHERE "USER_ID"=USER@! AND (:1 LIKE 'Technical Addressee%' OR "NOUTLET"=:2)

I don't know what the "@!" syntax is. I tried googling it but google seems to strip out non-ASCII chars so I can't find it anywhere. Is it a DB LINK or some kind of local loopback for the USER function?

I'm wondering if this makes it a remote/pseudo-remote/recursive statement and hence it doesn't appear in the trace file.

Thanks in advance...

ops$tkyte%ORA11GR2> select * from dual where 'x' = user;

Execution Plan
----------------------------------------------------------
Plan hash value: 3752461848

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     2 |     2   (0)| 00:00:01 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter('x'=USER@!)

Hi Tom,

Posting my first ever question...little scared after reading fair warnings on your page :)

My question is also about global_names.I am using 11g db version.
Due to performance issue in code I wanted to replace inner query using dblinks with a function as below -
original query -
Select localtab.x,
(Select line_id from rdbtab1@dblink,rdbtab2@dblink where rdbtabl1.x=rdbtab2.x
and rdbtab2.y=localtab.y)line_id
from localtab
where localtab.z='hello';

to be replaced with function
get_line_id(l_num_y)
return number
is
begin

Select line_id
into l_line_id
from rdbtab1@dblink,
rdbtab2@dblink
where rdbtabl1.x=rdbtab2.x
and rdbtab2.y=l_num_y ;
end;

return l_line_id;

end get_line_id;

and modified query as
Select localtab.x,
get_line_id(localtab.y) line_id
from localtab
where localtab.z='hello';

when i execute package after doing this change its error with ORA-02069: global_names parameter must be set to TRUE for this operation

But al_names parameter is set false in our system as dblink name is diff from dbase name. Also when i set it true for this session then it error with ORA-02085: database link XYZ_SJ_VK.DB.COM connects to SJ.DB.COM for obvious reason.

Please suggest if we can make this function to work.Also, what can be better replacement for dblinks apart from function.

Thanks,
Leena


Global_names can be set to false if dblink name dbname.

Hi Tom,

Posting my first ever question...little scared after reading fair warnings on your page :)

My question is also about global_names.I am using 11g db version.
Due to performance issue in code I wanted to replace inner query using dblinks with a function as below -
original query -
Select localtab.x,
(Select line_id from rdbtab1@dblink,rdbtab2@dblink where rdbtabl1.x=rdbtab2.x
and rdbtab2.y=localtab.y)line_id
from localtab
where localtab.z='hello';

to be replaced with function
get_line_id(l_num_y)
return number
is
begin

Select line_id
into l_line_id
from rdbtab1@dblink,
rdbtab2@dblink
where rdbtabl1.x=rdbtab2.x
and rdbtab2.y=l_num_y ;
end;

return l_line_id;

end get_line_id;

and modified query as
Select localtab.x,
get_line_id(localtab.y) line_id
from localtab
where localtab.z='hello';

when i execute package after doing this change its error with ORA-02069: global_names parameter must be set to TRUE for this operation

But al_names parameter is set false in our system as dblink name is diff from dbase name. Also when i set it true for this session then it error with ORA-02085: database link XYZ_SJ_VK.DB.COM connects to SJ.DB.COM for obvious reason.

Please suggest if we can make this function to work.Also, what can be better replacement for dblinks apart from function.

Thanks,
Leena