Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, David.

Asked: August 24, 2000 - 11:07 am UTC

Answered by: Tom Kyte - Last updated: May 06, 2013 - 1:59 pm UTC

Category: Database - Version: 8.0.5.0.0.

Viewed 10K+ times! This question is

You Asked

Hi Tom,
I only discovered today that in Oracle8 a database link must have the same name as the database it is accessing. Is this a new feature (read limitation) that came in with Oracle8? I’m positive you always used to be able to give the link any name you wanted. How do you operate this if you wanted to have two links to the same database with different access privileges – say OPERATOR and SUPERVISOR? Firstly, can you tell me I haven’t gone crazy and that is how it used to be and, secondly, can you explain the reasoning behind imposing this condition?

Regards,

David.


and we said...

It has been this way and it depends on the database or session setting of GLOBAL_NAMES. (and it really truely is a feature, not a limitation)...

If global_names is true -- then the database link must be named AFTER the global name of the database it connects to. This prevents ambiguity in a replication environment (which must have global_names=true) and is in general a good thing to do. You can still have many dblinks to the same database -- as long as they all incorporate the global name (see second point below)

So, what can you do:

o 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.

o leave global_names on but use @ in the dblink name. Put the
global name first in the dblink name and then extra stuff
after an @

For example, I have a database whose global name is ora8i.world. I'll create three db links to it -- one won't work since it doesn't use the global_name, the other 2 will -- they use the global name and then extra qualifiers after the global name to distinguish between them -- allowing me to have >1 link to the same database with different authorizations:

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

Database link created.

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

Database link created.

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

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


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

D
-
X

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

D
-
X


and you rated our response

  (53 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Names for Database Links

April 18, 2001 - 8:40 am UTC

Reviewer: Tyler D. Muth from Columbus, OH USA

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

Referring DB Link inside a procedure

June 26, 2002 - 1:16 am UTC

Reviewer: Janna from Bangalore - India

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

Tom Kyte

Followup  

June 26, 2002 - 8:15 am UTC

you must need a role for that then. Roles are not enabled during the compilation of a stored procedure. See
</code> http://asktom.oracle.com/Misc/RolesAndProcedures.html <code>
for how to test for that (to see that it's the issue)



July 19, 2002 - 3:21 pm UTC

Reviewer: RN from NJ

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.



Tom Kyte

Followup  

July 19, 2002 - 5:15 pm UTC

what is the GLOBAL NAME of the remote database. The service name has nothing to do with it.

July 19, 2002 - 6:23 pm UTC

Reviewer: RN

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

Tom Kyte

Followup  

July 20, 2002 - 10:27 am UTC

Fine, what is the global name of the remote db. YOUR DB wants global naming to be observed. That is what counts here.

db links

July 19, 2002 - 9:53 pm UTC

Reviewer: munz from Reston, USA

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 Kyte

Followup  

July 20, 2002 - 10:36 am UTC

you do not have global_names=true set perhaps.

global naming is what affects this.

db links

July 20, 2002 - 11:12 am UTC

Reviewer: Munz from REston, USA

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?

Tom Kyte

Followup  

July 20, 2002 - 11:13 am UTC

one can be seen by the developer, the other can be seen by all.

just like a private synonym vs public synonym

July 20, 2002 - 1:02 pm UTC

Reviewer: RN from Rutherford, NJ

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.

Tom Kyte

Followup  

July 20, 2002 - 1:06 pm UTC

You can either:

o disable global naming on your database
o give the other database a domain -- so its name is rasd.something.something

once you have a domain, that domain is "there". Your databases global name has .world in it. By default every dblink you create will have .world (or whatever you specify when you create the dblink)



July 20, 2002 - 2:18 pm UTC

Reviewer: RN from NJ

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 Kyte

Followup  

July 20, 2002 - 2:38 pm UTC

sure you can -- your problem is

a) you have a database without a domain
b) you have a database with a domain
c) in the database that has a domain, when you create a link it'll get that domain by default
d) hence B cannot talk to A with global names since A doesn't have a domain and B will create a link with a domain

solution: give A a domain
solution: don't use global names

alter session set global_names = FALSE;

and you are done.

db links

July 20, 2002 - 4:54 pm UTC

Reviewer: Munz from Reston, USA

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 Kyte

Followup  

July 20, 2002 - 6:32 pm UTC

you have to tell me. I cannot tell you how they connected, only that they are.

no, that is not true. if the private database link is a "connect to username identified by password" type (NOT a proxy account), it'll work just dandy

db links

July 20, 2002 - 9:51 pm UTC

Reviewer: munz from Reston, USA

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?

Tom Kyte

Followup  

July 21, 2002 - 9:16 am UTC

yes

September 06, 2003 - 4:52 am UTC

Reviewer: Deepak Gupta from Delhi, India

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 ) ?




Tom Kyte

Followup  

September 06, 2003 - 9:06 am UTC

ora8i.world

is the tns entry required.

September 09, 2003 - 12:39 am UTC

Reviewer: Deepak Gupta from Delhi,India

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 

Tom Kyte

Followup  

September 09, 2003 - 11:31 am UTC

the global name of the remote database you are referencing via 'oracle.world' is ORCL, not ORACLE.

so,

create database link orcl@dp connect to .....

and select * from dual@orcl@dp;

thanks a lot !!!

September 10, 2003 - 6:43 am UTC

Reviewer: A reader


DBLink from local database (ver 8.1.7) to remote database (ver 8.1.6)

October 07, 2003 - 10:03 am UTC

Reviewer: Praveen from Bangalore

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

Tom Kyte

Followup  

October 07, 2003 - 10:10 am UTC

try

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


you want to name the dblink after the GLOBAL NAME

you want to use a tns connect string that actually works.


put the two together and wa-lah, you'll connect.

Problem with DB-Link to access a procedure

February 27, 2004 - 3:10 am UTC

Reviewer: Raju from India-Bangalore

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


Tom Kyte

Followup  

February 27, 2004 - 7:31 am UTC

instead of telling me what doesn't work - how about you state what you are attempting to achieve? (then I can probably tell you how to do that)

Problem with DB-Link to access a procedure

March 01, 2004 - 1:13 am UTC

Reviewer: Raju from India-Bangalore

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


Tom Kyte

Followup  

March 01, 2004 - 7:48 am UTC

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.
 

database link

March 27, 2004 - 6:13 am UTC

Reviewer: Mahesh Kumar from NEW DELHI , INDIA

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.
 

Tom Kyte

Followup  

March 27, 2004 - 10:51 am UTC

global_names are meant to be globally UNIQUE - same machine or not.

rename the "rep" site using alter database rename global_name to rep; and it'll work.

database links

March 28, 2004 - 11:41 pm UTC

Reviewer: Mahesh Kumar from New Delhi, India

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 

Tom Kyte

Followup  

March 29, 2004 - 9:42 am UTC

show me a full cut and paste (a real one!), not working that way for me.

DATABASE LINKS

March 29, 2004 - 11:45 pm UTC

Reviewer: Mahesh Kumar from New Delhi, India

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
 

Tom Kyte

Followup  

March 30, 2004 - 7:52 am UTC

you are doing stuff "as sysdba"

stop it. just stop doing that. arg....


You have a null login/password when you connect "/". Thats not allowed.


Log in as someone with a username and password. THEN you can use the link without a username password.


STOP doing things "as sysdba" unless you HAVE to (eg: startup, shutdown). Pretend SYS and SYSTEM do not exist when you create things. Use YOUR own accounts.

Use sys and system only when directed to.... pretend they are not there otherwise.

database links

March 30, 2004 - 11:34 pm UTC

Reviewer: Mahesh Kumar from New Delhi, India

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

Thanx & Regards

Tom Kyte

Followup  

March 31, 2004 - 8:33 am UTC

you can use DBMS_JOB to schedule the DDL to execute on the remote site after you commit.

dbms_job.submit@remote_site( l_job, 'execute immediate ''create table t ( x int )'';');
commit;


Database Links

March 31, 2004 - 2:17 am UTC

Reviewer: Mahesh Kumar from New Delhi, India

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 

Tom Kyte

Followup  

March 31, 2004 - 8:34 am UTC

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. 

Database Links

April 02, 2004 - 1:35 am UTC

Reviewer: Mahesh Kumar from New Delhi, India

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
 

Tom Kyte

Followup  

April 02, 2004 - 10:03 am UTC

when you connect via sqlplus with user/pass@connect_string

the connect_string is retrieved by default from the tnsnames.ora file on your client workstation.

when you create a database link using 'connect_string' -- the connect string is retrieved by default from the tnsnames.ora file on your database SERVER machine -- not your desktop workstation.



Database link

April 08, 2004 - 2:19 am UTC

Reviewer: Mahesh Kumar from New Delhi, India

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 Kyte

Followup  

April 08, 2004 - 10:08 am UTC

you have made a mistake somewhere -- it is pretty clear that the link connects to the other database.


do this, use the tns entry right in the create database link just to see for yourself:

create database link orcl.world
connect to scott
identified by tiger
using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS = (PROTOCOL = TCP)(HOST = ORACLE)(PORT = 1521)))(CONNECT_DATA =(SID = ORCL)))'
/




database link name

June 04, 2004 - 8:39 am UTC

Reviewer: MM from Lexington KY USA

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.



Tom Kyte

Followup  

June 04, 2004 - 10:51 am UTC

well, it is not a variable -- it is a substitution string -- sqlplus is just putting whatever value you have in db_link right in that string BEFORE sending it to the database.

therefore, if t@"whatever" works -- then t@"&&db_link" will work if db_link has whatever in it.

Oracle DB Link

June 30, 2004 - 10:53 am UTC

Reviewer: Kenneth Rowe from Gaithersburg, MD

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

what about squence or packages?

January 14, 2005 - 3:48 pm UTC

Reviewer: joachim mayer from austria/europe

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

Tom Kyte

Followup  

January 14, 2005 - 9:00 pm UTC

that looks "not right". please contact support

password

March 02, 2005 - 2:33 am UTC

Reviewer: pilo from india

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

Tom Kyte

Followup  

March 02, 2005 - 7:32 am UTC

plz?

but even if I translate plz into please, I have no idea what you mean.

Database Link

March 07, 2005 - 9:50 am UTC

Reviewer: Bipin from INDIA

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.

Tom Kyte

Followup  

March 07, 2005 - 3:03 pm UTC

what you missed out on is the maximum number of databases on a server should be

one.


why do you have two of them?

changing domain name

March 18, 2005 - 6:16 pm UTC

Reviewer: Dawar from LA,-- CA, USA


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




Tom Kyte

Followup  

March 18, 2005 - 8:15 pm UTC

networking -- sure, tnsnames, listener.ora... any parameter you set that has a hostname in it.

DB Link is getting created with .world

March 22, 2005 - 6:45 pm UTC

Reviewer: Arya from Reston, VA, USA

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



Tom Kyte

Followup  

March 23, 2005 - 1:06 am UTC

what is the db_domain set to. (parameter, init.ora, spfile)

DB Link create with default domain

March 23, 2005 - 10:59 am UTC

Reviewer: Arya from Reston, VA

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 Kyte

Followup  

March 23, 2005 - 6:07 pm UTC

select * from global_name;

alter database can be used to change it.

Data Base link

May 02, 2005 - 5:07 am UTC

Reviewer: vivek from India.

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

Tom Kyte

Followup  

May 02, 2005 - 8:33 am UTC

1) it picks up session settings from your session, your session was "true"

2) you don't even need that, alter database rename global name will do it. but could break other dblinks from databases using global names that don't have a domain setup themselves.

3) see #2

4) so we can be sure we know who we are talking to -- it is for safety.

database links

May 02, 2005 - 8:54 am UTC

Reviewer: vivek from india

excellent.

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


thanks

Tom Kyte

Followup  

May 02, 2005 - 9:04 am UTC

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. 

ORA-04052

May 24, 2005 - 5:20 am UTC

Reviewer: A reader

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 

Tom Kyte

Followup  

May 24, 2005 - 8:04 am UTC

Please contact support for assistance with 3113 errors (and 600's and 7445's)

global_name change

July 16, 2005 - 2:22 am UTC

Reviewer: Kuldeep from India

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,

Tom Kyte

Followup  

July 16, 2005 - 9:18 am UTC

you cannot, once a domain is there, there will always be a domain, you can make the domain be whatever you want, but "blank" is not an option.

global_name domain

July 18, 2005 - 8:40 am UTC

Reviewer: Kuldeep from India

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,


Tom Kyte

Followup  

July 18, 2005 - 8:57 am UTC

it is not a bug, no. According to the support database, this is the way it is implemented.

January 17, 2006 - 10:41 am UTC

Reviewer: San from Germany

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 

Tom Kyte

Followup  

January 17, 2006 - 4:04 pm UTC

[tkyte@me ~]$ oerr ora 12535
12535, 00000, "TNS:operation timed out"
// *Cause: The requested operation could not be completed within the time out
// period.
// *Action: Look at the documentation on the secondary errors for possible
// remedy. See SQLNET.LOG to find secondary error if not provided explicitly.
// Turn on tracing to gather more information.


firewall in the way?

DB Links

April 17, 2006 - 3:54 am UTC

Reviewer: M.P.Kiran Kumar from INDIA

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 Kyte

Followup  

April 17, 2006 - 8:03 am UTC

the dblink is the most logical approach.

DB links in procedures

April 17, 2006 - 9:10 am UTC

Reviewer: M.P.Kiran Kumar from INDIA

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

Tom Kyte

Followup  

April 17, 2006 - 9:59 am UTC

how's about asking one thing in one place... look to the other place you asked this for the simple answer (that a table@dblink is referenced just like a table is, no difference)

DB Links

May 07, 2006 - 10:42 am UTC

Reviewer: Ramesh from US

Hi

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


Regards
Ramesh

Tom Kyte

Followup  

May 07, 2006 - 11:40 am UTC

yes, it can be done. dblinks are transparent to the application using them.

Loopback database links to avoid invalidations

August 23, 2006 - 3:06 pm UTC

Reviewer: Rima from MN, USA

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.

Tom Kyte

Followup  

August 27, 2006 - 5:46 pm UTC

insufficient example - show the entire thing from start to finish here. When you repoint the synonym, the account accessing the dblink doesn't appear to have the right privileges anymore.

Loopback database links to avoid invalidation

August 24, 2006 - 1:15 pm UTC

Reviewer: Rima

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

June 19, 2008 - 11:31 am UTC

Reviewer: A reader

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?


Tom Kyte

Followup  

June 19, 2008 - 1:54 pm UTC

once the global name has a domain, it'll always have it. you can change the domain to whatever you like via the alter database command - but it'll have some domain making it different from the dbname

rename your global name to whatever you like.

June 20, 2008 - 2:53 am UTC

Reviewer: A reader

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


Tom Kyte

Followup  

June 20, 2008 - 10:16 am UTC

I told you - once the domain is there, there will ALWAYS be a domain. Add your own domain of your own choosing.

to atm.foo.bar;


How do you deal with moving schemas across instances?

August 07, 2008 - 12:08 pm UTC

Reviewer: Peter from Philadelphia, PA USA, Earth, Universe

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.
Tom Kyte

Followup  

August 07, 2008 - 1:37 pm UTC

the move of a schema is a one time operation - why wouldn't you just

a) create database link
b) copy it
c) drop database link


global names saves you not just in replication, but anytime you think you are connecting to a specific database - you know someone had to name the link after the database in question. You KNOW what you are connecting to.

variabled dblink in cursor for loop

August 07, 2008 - 5:27 pm UTC

Reviewer: HK from Finland

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
...

Tom Kyte

Followup  

August 07, 2008 - 6:27 pm UTC

no, you have to use dynamic sql anytime you change an identifier (table name, column name, dblink, any identifier)

because the query is very much changed anytime you change an identifier.

Also, think about it - at compile time - the plsql compiler would need to know what X and Y are - datatype wise.

And, at run time - there is no reason X is not a string at db1 and a number at db2 and so on (sure, you say it is always a number - but there is NOTHING stopping it from being different)....


so, it would look like this:
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;
      .....

dblink problem

June 22, 2010 - 8:12 am UTC

Reviewer: Saurabh Nolakha from Bangalore

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 Kyte

Followup  

June 22, 2010 - 1:53 pm UTC

totally insufficient data.

we do not know the type of id in some_view.


as for the ora-2019, what could I say about that - I have no idea if qacd.qdef.xyz.com should work or not - only you do.

Re: How do you deal with moving schemas across instances?

March 01, 2011 - 9:17 pm UTC

Reviewer: Bob from Stamford, CT

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
Tom Kyte

Followup  

March 02, 2011 - 7:17 am UTC

How does your #2 differ from your #1?

You can use private synonyms/views to hide the dblink name (typically a good idea anyway)

And in my opinion - if you move/copy the database a link points to - you would WANT to rename the remote dblinks. That give you the assurance you actually KNOW what you are pointing to. The goal is to have every database in your network have their own identifying name. It gives you peace of mind - you explicitly know what you are pointing to, what you are accessing.

As opposed to having more than one database named "production" or "test".

If you wanted to keep the dblinks the same, you would have (in your case) three separate environments - with all of the necessary databases - named (globally named) after their counterparts. It would be (in my opinion) dangerous to do so, the possibility of 'accidentally' connecting to the wrong instance would be high.


Re: How do you deal with moving schemas across instances?

March 02, 2011 - 10:37 am UTC

Reviewer: Bob from Stamford, CT

#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 Kyte

Followup  

March 02, 2011 - 11:14 am UTC

right - but #1 and #2 are basically the "same", #2 involves doing #1 first.

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

... The risk of accidentally connecting to the wrong place while using a single DB
link doesn’t seem very high to me ...

I see it all of the time, it is the cause of many "Oh, right command, wrong database" issues.

... there are firewalls between each layer
(dev/qa/prod) which should prevent it. ...

that is your environment, not the environment in general.

If you feel it isn't an issue, you can always just not use global names. Caveat Emptor.

Re: How do you deal with moving schemas across instances?

March 02, 2011 - 11:36 am UTC

Reviewer: Bob from Stamford, CT

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
Tom Kyte

Followup  

March 02, 2011 - 12:03 pm UTC

no, you have to create the synonym for the objects (tables, views, etc) you want to 'hide', you cannot create a synonym for a database link.


Is this a DB LINK name?

May 03, 2011 - 6:15 am UTC

Reviewer: Ton Killen from Luxembourg

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...
Tom Kyte

Followup  

May 04, 2011 - 12:24 pm UTC

it isn't a loopback or anything, it is just our representation of that function


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@!)




user is the same as USER@!


I don't know why the developers would use that however, it isn't documented, it isn't supported.

How to change the value in GLOBAL_NAME table

May 01, 2013 - 6:43 am UTC

Reviewer: Leena from CA USA

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.
Tom Kyte

Followup  

May 06, 2013 - 1:59 pm UTC

why do you think that a function would be faster than a scalar subquery (which can be cached nicely!)

http://www.oracle.com/technetwork/issue-archive/2011/11-sep/o51asktom-453438.html


I would not recommend this approach - not at all. In fact, I recommend replacing functions like that with scalar subqueries when you can!!

so, I would not go down the function route.


and I would definitely set up your database links with proper global names to avoid "accidentally" doing the right thing in the wrong database. Highly recommended.

How to change the value in GLOBAL_NAME table

May 01, 2013 - 6:43 am UTC

Reviewer: Leena from CA USA

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