Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question.

Asked: March 03, 2001 - 3:11 am UTC

Last updated: November 03, 2010 - 9:44 am UTC

Version: 8.1.6

Viewed 10K+ times! This question is

You Asked

i create a database link like in the following:
i am creating a link to CANDB from another database called IASDB

SQL> create public database link candb using 'candb';

Database link created.

SQL> select db_link from all_db_links;

DB_LINK
------------------------------------------------
CANDB.US.ORACLE.COM

when i see the name it has a domain attached to it: US.ORACLE.COM


i check the sqlnet.ora ,init.ora etc..
the db_domain is hashed out.
the global_names is true
sqlnet.ora doesnt have any entry
i connect thru svrmgrl
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------
IASDB.US.ORACLE.COM

SQL> alter database rename global_name to IASDB;

SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------
IASDB.US.ORACLE.COM


it doesnot change...i am facing difficulty during multimaster replication setup and alaso while accessing some tables ..it says that candb.us.oracle.com refers to the database candb





and Tom said...


If you issue an 'alter database rename global_name to <new_global_name>;' and you don't specify a db_domain, then the db_domain will remain the same as that which was last set using the rename global_name command regardless of what db_domain is set to.

Thus if, in your init you have db_name = testone and db_domain = world and you issue the following: 'alter database rename global_name to testtwo.us.oracle.com' and then select from global_name you will see the following
'testtwo.us.oracle.com'.

Now if you issue 'alter database rename global_name to testthree;' (note that here we don't specify a db_domain) and then select from global_name you will see 'testthree.us.oracle.com' even though db_domain
is set to world in our init.ora.


You have to use a domain. It will default to world (when hashed out). You MUST use a domain. The database name will be at least x.y but can by x.y.z.a.b.c.....




Rating

  (18 ratings)

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

Comments

Network domain and database domain.

A reader, September 25, 2002 - 12:39 pm UTC

Tom:
Can we use a database domain other than the network domain? (I know we can still connect, but is it advisable?)

We have all our unix database servers under network domain domain say xyz.com (FQN like: server1.xyz.com, server2.xyz.com etc..). The db_domain of all the databases are set to .world (explicitely in init.ora, I don't know why, but people here are against changing it..). We have names.default_domain=world in sqlnet.ora and we can connect using sqlplus. Now, I was trying to install a monitoring tool and it failed to connect because the network domain and database domain does not match! The tech support confirmed that it is the problem! Why would that be a problem if Oracle sqlplus can connect, I asked. They just said that is how it works!

Can you please throw some light? What is the purpose of the domain_name init.ora parameter? Why is the difference in network domain and database name not a problem for Oracle, while it is for some? Also, are there any other problems if we set the db_domain different from network domain name?

TIA,
AR

Tom Kyte
September 25, 2002 - 1:05 pm UTC

They are confused. The db domain affects database links only.

It should have no affect on a 3rd party tools ability to connect -- IN FACT, the third party tool cannot even tell the domain until *after* they connect.

Anyway, maybe this tool just needs a tnsnames entry setup special for them. try that. Maybe they are just taking your tnsnames entry you give them and adding the domain afterwords (my domains are .worlds and some .oracle.com but my "network" domain is us.oracle.com -- I've never ever had an issue)

A reader, September 25, 2002 - 4:47 pm UTC

That was the fix! (This is pretty boring, how come you *always* have the right answers? Just kidding...)

Could you please explain the purpose of db_domain parameter? Is it just for organizing your databases heirarchically? Is there anyway we can inherit the network domain name automatically as db_domain?

Thanks for hosting this great site.

Tom Kyte
September 25, 2002 - 6:39 pm UTC

The db_domain is something I don't generally really use or even pay much attention to.

from the reference guide:

...
In a distributed database system, DB_DOMAIN specifies the logical location of the database within the network structure. You should set this parameter if this database is or ever will be part of a distributed system. The value consists of the extension components of a global database name, consisting of valid identifiers, separated by periods. Oracle Corporation recommends that you specify DB_ DOMAIN as a unique string for all databases in a domain.

<note from tom -- that means the db domain is recommended to NOT be your network domain but named after your "group">

This parameter allows one department to create a database without worrying that it might have the same name as a database created by another department. If one sales department s DB_DOMAIN is JAPAN.ACME.COM, then their SALES database (SALES.JAPAN.ACME.COM) is uniquely distinguished from another database with DB_NAME = SALES but with DB_DOMAIN = US.ACME.COM.

If you omit the domains from the name of a database link, Oracle expands the name by qualifying the database with the domain of your local database as it currently exists in the data dictionary, and then stores the link name in the data dictionary. The characters valid in a database domain name are: alphanumeric characters, underscore (_), and number sign (#).

<note from tom -- that part is a little vital -- and is the time when the db_domain most affects you>
.........




domain name in tnsnames.ora

Oracle-leo, November 08, 2002 - 8:24 am UTC

When i add an alias in tnsnames.ora using net8 configuration asst on win2k/8.1.7, the alias is suffixed with <alias>.world or <alias>.in.oracle.com.
But when i connect using system/manager@alias, it throws error as tns could not resolve name and expects full name like <alias>.in.oracle.com.

Is it normal behavior or there is some problem somewhere. How can i avoid suffixing of domain name in tnsnames.ora.

Thanks,

Tom Kyte
November 08, 2002 - 9:07 am UTC

edit your sqlnet.ora and add a

NAMES.DEFAULT_DOMAIN = in.oracle.com


and you won't have to supply alias.in.oracle.com - just alias.

Why suddently cannot work?

Junior, May 23, 2003 - 3:19 pm UTC

Tom:
I created database link and worked fine in the very begining. However, couple weeks later, this link is not working any more. I worked on both database every day, and I am able to switch between these 2 databases by using connect scott/scott@db1 and connect scott/scott@db2.
The only thing I did is I have a batch file which shoutdown db1 every night and make a cold backed up. It will bring up the db1 after the backed up is done.
Will this cause problem? Do you know why the link suddently not work? I am using Oracle 9.2. and running on Window 2000.

Thank you




Tom Kyte
May 24, 2003 - 9:54 am UTC

hey, my car won't start this morning.

its worked every day.

No, I won't tell you the make or model.
I won't tell you the symptoms.
I won't tell you anything really....

so, tell me -- why won't my car start?


(sorry, could not resist. think about it -- what possible answer could I sitting here in my office -- without the ability to see your computer screen and read the error message -- give you? no error code, no messages, no cut and paste, NOTHING)



Database v/s car

A reader, May 25, 2003 - 5:50 am UTC

HA HA HA HA HA HA HA

database links

Junior, May 27, 2003 - 8:42 am UTC

Tom:

Sorry, you are right, I should paste error message here.

SQL> select db_link from all_db_links;

DB_LINK
--------------------------------------------------------------------------
db14

SQL>  desc person@db14;
ERROR:
ORA-12545: Connect failed because target host or object does not exist

I checked tnsname and I believe it should be fine. I am able to switch the connection between those 2 databases. This links used to work fine, but suddently it just not worked. I droped this link and I am able to recreate it again. However, it still not works. So...

Thanks.


 

Tom Kyte
May 27, 2003 - 9:35 am UTC

someone changed the hostname or ip address of your server.

goto your tnsnames.ora file, verify it is correct (and it won't be). either the hostname/ipaddress will be wrong.


[tkyte@tkyte-pc Desktop]$ oerr ora 12545
12545, 00000, "Connect failed because target host or object does not exist"
// *Cause: The address specified is not valid, or the program being
// connected to does not exist.
// *Action: Ensure the ADDRESS parameters have been entered correctly; the
// most likely incorrect parameter is the node name. Ensure that the
// executable for the server exists (perhaps "oracle" is missing.)
// If the protocol is TCP/IP, edit the TNSNAMES.ORA file to change the
// host name to a numeric IP address and try again.
[tkyte@tkyte-pc Desktop]$


database link between 7.1.5 and 9.2

John, June 05, 2003 - 9:06 pm UTC

Hi Tom,

We have a database server 9.0.1. We want to upgrad to 9.2.

We must have a database link to one oracle 7.1.5 database(our client database, they plan to upgrade it next year. We don't have the control.). Through the link, we use select statement to retrieve infomation from 2 tables frequently(almost "select" every couple of seconds), no other complicated stuff. I heard about 9.2 has issues to access v7 database. Could you please confirm that 9.2 can't "select" from v7 through the database link at regular basis?

Thanks.

Tom Kyte
June 05, 2003 - 9:26 pm UTC

you cannot, in production, connect 9.2 to 7.x

not supported.
not supportable.

Remote database users

A reader, September 09, 2003 - 5:54 am UTC

Hi Tom,
We have three oracle sites let's say site1, site2 and site3. Currently every site connects to each other using database links for transaction processing. I am working at site1.
How can I know that how many users (with details) or sessions are currently connected to site1 from site2 or site3??
Thanks in advance


Tom Kyte
September 09, 2003 - 11:44 am UTC

look at the program column in v$session

connect 9.2 to 7.x

Jones, January 15, 2004 - 11:52 pm UTC

you cannot, in production, connect 9.2 to 7.x

Is there any work around ? or any suggestions ?
example - export the data into flat files from 7.x, ftp to the 9.2 box and load the data files into 9.2

Your response is highly appreciated

Tom Kyte
January 16, 2004 - 2:50 am UTC


7.x has been out of support for so long -- so so long. There is no way to reliably connect 7.x and 9.2 (and above).

A reader, March 16, 2004 - 1:38 am UTC

I created db link using the below statement. (ver 9.2.0)

create public database link DL_DEVDMDB connect to GECIS_EDW identified by "edw~123" using
'(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = giahyd004)(PORT = 1521))
)
(CONNECT_DATA = (SID = devdmdb)(SERVER=DEDICATED))
)
'

I dont have the any entry related to this server in TNS ( Hope not required).

Pls confirm if i need to do any changes in the server setting or this is all set to create a db link.

The problem is, i created the link 2 days back, but today i dont see it. Could have been deleted by some one or i missed out anything.

Sorry for my ignorance. Thanks for your time and consideration


Tom Kyte
March 16, 2004 - 7:45 am UTC

if the link "isn't there" someone dropped it on you.


Yes, that works -- you do not need to have a tns entry, you can put it right in there like that.

Thanks ...

A reader, March 16, 2004 - 8:12 am UTC


link puzzle

robert, March 30, 2004 - 9:47 am UTC

tom, How come I have referenced link names not in the all_db_links list ?
thanks

SQL> select REFERENCED_LINK_NAME from dba_DEPENDENCIES where REFERENCED_LINK_NAME is not null order by 1;

REFERENCED_LINK_NAME
--------------------------------------------------------------------------------
CTRACK.ZITE.COM
CTRACK.ZITE.COM
DBLINK_HFOD.ZITE.COM
JSH_EPDB_LNK.ZITE.COM
JSH_EPDB_LNK.ZITE.COM
JSH_EPDB_LNK.ZITE.COM
LNK_COMPANY.ZITE.COM
LNK_COMPANY.ZITE.COM
LOADPOC.ZITE.COM
LOADPOC.ZITE.COM

10 rows selected

SQL> select db_link from all_db_links order by 1;

DB_LINK
--------------------------------------------------------------------------------
JSH_EPDB_LNK.ZITE.COM
LNK_CNTXT_USER.ZITE.COM
LNK_COMPANY.ZITE.COM
LNK_HFOD_LIVE.ZITE.COM
LOADPOC.ZITE.COM
MANDAREGION_DBLINK.ZITE.COM
NEWS_DBLINK.ZITE.COM
TEST.ZITE.COM

8 rows selected 

Tom Kyte
March 30, 2004 - 10:55 am UTC

all_db_links are dblinks you are allowed to see the definition of.

scott@ORA9IR2> select count(*) from all_db_links;
 
  COUNT(*)
----------
         0
 
scott@ORA9IR2> @connect /
Connected.
ops$tkyte@ORA9IR2> select count(*) from all_db_links;
 
  COUNT(*)
----------
         1

different people get to see different things. 

domain name

vivek saxena, April 19, 2005 - 1:17 am UTC

Tom,

I have a query for domain name. I installed oracle 9.2 on my PC (OS windows )and created a database by name of "albert". I have following setting in my init file
###########################################
# Database Identification
###########################################
db_domain=""
db_name=albert

and sqlnet.ora has

NAMES.DEFAULT_DOMAIN = www.netedge.com

whereas the global name is ALBERT.US.ORACLE.COM

vivek@ALBERT.US.ORACLE.COM> select global_name from global_name;

GLOBAL_NAME
--------------------------
ALBERT.US.ORACLE.COM

I want to know
a) from where DB has picked up the domain 'US.ORACLE.COM'
b) can i change the doamin name like global name

Regards
Vivek

Tom Kyte
April 19, 2005 - 7:25 am UTC

alter database rename global_name to a.b.c.d;

it got it when it was installed/created.

domain name

vivek, April 19, 2005 - 8:33 am UTC

Tom,

Some where in thread you mentioned that '.world' is default domain, so how come 'us.oracle.com' comes into picture. Is it stored somewhere in dictionary. Can i change domain name like you have shown for global name.

As i created a database link via OEM by name of 'sunnet' and it added 'us.oracle.com' to it, 'sunnet.us.oracle.com'.
So my domain name is "" (from init.ora) or 'us.oracle.com'

Regards
Vivek

Tom Kyte
April 19, 2005 - 9:01 am UTC

it got it from your global name, it happened during install, one of the configuration files you had -- had it in there (overriding the default). A simple rename will change it.

invalid ROWID ORA-02063

anupam pandey, October 12, 2009 - 5:24 am UTC

Hi Tom ,
I am executing a select query over a db link to dump the data from remote database .
The query was running fine but from few days back it has started throwing "ORA-01410: invalid ROWID ORA-02063:"

I fired the query from outside and it ran fine without problem .
But again when next day process was running that select again caused the problem .
Following is the query .

SELECT /*+ driving_site(a) LEADING(a b) */
a.order_id,
b.line_id,
translate(c.pos,' ','+') ,
a.id
FROM scott.a@a a1,
scott.b@a b1 ,
scott.c@a c1
WHERE a.delivery_key = b.delivery_key
AND b.key = c.key
AND a.date_id = <DATE>

Please suggest why this problem is coming .

Thanks And Regards,
Anupam

Why not skip using db_domain

A reader, April 08, 2010 - 10:10 pm UTC

Tom - You said a few posts up you basically never bother with db_domain and don't have much use for it. I feel the same way because it just makes everything more complicated. Witness all these posts about global_name and links etc., But the Oracle document you posted suggests using it so that you don't end up with duplicate databases in an enterprise environment like 'SALES' etc., I am inheriting a migrated environment that USED to use a db_domain as an account standard. Whether to use it in a new environment is up to me. I'm inclined to skip it. If someday, another organization comes up with a duplicate database name, kind of a long shot, I would think the tnsnames.ora and IPs would be different and although it might be confusing, it could be addressed later. What are your thoughts? Any real downside risk to not implementing it? I would think if it REALLY became necessary, your links would change, and any views or mviews referring to links etc., a major effort to be sure, but skipping it avoids all this confusion. Your two cents?
Tom Kyte
April 13, 2010 - 8:33 am UTC

... If someday, another organization comes up with a
duplicate database name, kind of a long shot, I would think the tnsnames.ora
and IPs would be different and although it might be confusing, it could be
addressed later....

in a distributed environment where database A talks to database B, you want them to have globally unique, permanent names - to be completely unambiguous - that is their point. And using a db_domain that is unique to your group would "help" that.

that is all.

DB Link Limitaions.

Stuar Broad, November 03, 2010 - 1:46 am UTC

Tom,

First of all thanks for enlighting us as always with your huge knowledge.

I have a question ,
1.Is is always FTS when we do Select from a remote table.
2. Can we give Partition name in select from a remote database using DB link.
3. Can you enlighten us the things which are not possible to perform using DB link which can be performed in Local database.

It would be helpful if you can guide to any oracle docs or your own book which explains operation over DB link in details.
Tom Kyte
November 03, 2010 - 9:44 am UTC

1) no, indexes are frequently used and are always available.

2) no

ops$tkyte%ORA11GR2> select * from t@ora11gr2@loopback partition(part2);
select * from t@ora11gr2@loopback partition(part2)
*
ERROR at line 1:
ORA-14100: partition extended table name cannot refer to a remote object


3) you'd have to "go to the thing" you want to do in the documentation and see if it is permitted. Most things 'are'. There is not a master list of what isn't - a feature that isn't would be (should be) documented as such.

for example:

http://docs.oracle.com/docs/cd/E11882_01/server.112/e17118/sql_elements009.htm#sthref801

documents the fact that #2's answer is "no"

DB Link Limitaions.

Stuar Broad, November 03, 2010 - 1:46 am UTC

Tom,

First of all thanks for enlighting us as always with your huge knowledge.

I have a question ,
1.Is is always FTS when we do Select from a remote table.
2. Can we give Partition name in select from a remote database using DB link.
3. Can you enlighten us the things which are not possible to perform using DB link which can be performed in Local database.

It would be helpful if you can guide to any oracle docs or your own book which explains operation over DB link in details.