Hi,

I would like to view live baan table layout thru Oracle DB link within testing server, it raise error :


SQL> select count(*) from baan.ttfgld005xxx@mylink;

COUNT(*)
---------
xxx

SQL> desc baan.ttfgld005xxx@mylink
ERROR:
ORA-12663: Services required by client not available on the server

Do you know how to do could look at table structure by DB link ?

Thank you very much.


Warm regards,
Lucy

Matybe you can try the following:

SQL> connect baan/baan@mylink
SQL> desc baan.ttfgld005xxx

Have no link setup yet so I am not sure if it works correctly

Kind regards,
Dick

Hi,

in Baan testing Oracle DB sqlplus, it return when I do:

SQL> connect xxx/xxx@mylink
ERROR:
ORA-12154: TNS:could not resolve service name


Warning: You are no longer connected to ORACLE.
SQL>

I create DB link mylink sql is :

Create database link mylink
Connect to auser identify by xxx
Using ¡¥xxxx¡¦;

Regards,
Lucy

Hi Lucy,

The problem may revolve around your db_domain and global names

SVRMGR> show parameter db_domain
SVRMGR> show parameter global_names

Check in your sqlnet.ora file to see if you have the following entry

NAMES.DEFAULT_DOMAIN=WORLD

(if not present add this entry specifying your db_domain)

Now check to see that you are using these domain extensions in your tnsnames.ora file. If not, add an alias that has the domain extension (TEST.WORLD)

e.g.

TEST.WORLD, TEST, =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL=TCP)
(HOST=server.com)(PORT=15179))
)
(CONNECT_DATA=
(SID=TEST)
)
)

Post more details if you need further help.

Cheers,

Lucy,

Furthermore, it seems doing a describe across a gateway/link will not work in Oracle7.

It is possible in Oracle8, but there is a known bug (Bug:1291171).

It is fixed in Oracle9i.

Hi James,Hi,

I work around to get detail information.
I connect to A server with Oracle7 Server Release 7.3.4.0.1 -
SQL*Plus: Release 8.0.5.0.0 - Production on Thu Jan 23 9:24:13 2003
(c) Copyright 1998 Oracle Corporation. All rights reserved.
Connected to:
Oracle7 Server Release 7.3.4.0.1 - Production
With the distributed option
PL/SQL Release 2.3.4.0.0 ¡V Production
SQL>

Then connect to B server with Oracle8i Enterprise Edition Release 8.1.7.3.0 ¡V
SQL*Plus: Release 8.0.5.0.0 - Production on Thu Jan 23 9:25:50 2003
(c) Copyright 1998 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning option
JServer Release 8.1.7.3.0 - Production
SQL>

And I create Oracle DB link in B server in order to connect A server Oracle DB.

My local machine tnsnames.ora
A_Baan.world =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(Host = nn.nn.nn.nn)
(Port = 1521)
)
)
(CONNECT_DATA = (SID = baan)
)
)

B_Baan.world =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(Host = xx.xx.xx.xx)
(Port = 1521)
)
)
(CONNECT_DATA = (SID = baan)
)
)

in B server Unix environment :
$ svrmgrl
Oracle Server Manager Release 3.1.7.0.0 - Production
Copyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved.
Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning option
JServer Release 8.1.7.3.0 - Production
SVRMGR> connect internal
Connected.
SVRMGR> show parameter db_domain
NAME TYPE VALUE
----------------------------------- ------- ------------------------------
db_domain string world
SVRMGR> show parameter global_names
NAME TYPE VALUE
----------------------------------- ------- ------------------------------
global_names boolean FALSE
SVRMGR>

$ cat sqlnet.ora

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)


Thank you very much.


Warm regards,
Lucy

Lucy,

Add the following to your sqlnet.ora on your B server:

NAMES.DEFAULT_DOMAIN=WORLD

Also, let me know the entry you have in your tnsnames.ora file, on your B server, for A_Baan.world. It should be,

A_Baan.world, A_Baan =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(Host = nn.nn.nn.nn)
(Port = 1521)
)
)
(CONNECT_DATA = (SID = baan)
)
)

Hi James,

Regarding you mention add the following to sqlnet.ora on B server:

NAMES.DEFAULT_DOMAIN=WORLD

Any risk should be concerned when I edit sqlnet.ora on B server ? because both servers are Baan live and developing environment.



Warm regards,
Lucy

Lucy,

This change will only affect SQLNet / Net*8 connections going out of server B, in your case, the DB Link to server A.

Of course, in the unlikely event of a problem, just comment the entry out.

Hi James,

It could be fixed if Oracle DB version is 8.1.7.3, because I view B server¡¦s Oracle8173 table using A server DB link within 7.3.4.0, it is no error messages return.

Thanks.


Regards,
Lucy

Hi,

I would like to view live baan table layout thru Oracle DB link within testing server, it raise error :


SQL> select count(*) from baan.ttfgld005xxx@mylink;

COUNT(*)
---------
xxx

SQL> desc baan.ttfgld005xxx@mylink
ERROR:
ORA-12663: Services required by client not available on the server

Do you know how to do could look at table structure by DB link ?

Thank you very much.


Warm regards,
Lucy

If you are in SQL*Plus you can simply connect to the remote DB using the info in USER_DB_LINKS and do the desc.

Also you can use something like

select column_name,data_type,data_length
from user_columns@mylink
where table_name='TTFGLD005xxx'
order by column_id

I have noticed the same strange error with desc through a DB link in oracle 8, with version 9 it is working fine.

Rgds, Pierre