Tom, I know DBLINKs can be created like that but never tried from sqlplus (until I read this)

gave it a try..it isnt working but I may be missing something very basic

from tnsnames.ora
=================
MYDBTEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hppkg_test)(PORT = 1515))
(CONNECT_DATA =
(SID = TESTDB)
)
)

Testing from Sqlplus
====================
C:\orahome1\ora92\bin>sqlplus system@mydbtest

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Jul 28 12:45:25 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter password: ***

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SYSTEM@TESTDB>exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

trying copy and paste from tnsnames.ora
----------------------------------------
C:\orahome1\ora92\bin>sqlplus system@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hppkg_test)(PORT=1515))(CONNECT_DATA =(SID = TESTDB))
Usage: SQLPLUS [ [<option>] [<logon>] [<start>] ]
where <option> ::= -H | -V | [ [-L] [-M <o>] [-R <n>] [-S] ]
<logon> ::= <username>[/<password>][@<connect_string>] | / | /NOLOG
<start> ::= @<URI>|<filename>[.<ext>] [<parameter> ...]
"-H" displays the SQL*Plus version banner and usage syntax
"-V" displays the SQL*Plus version banner
"-L" attempts log on just once
"-M <o>" uses HTML markup options <o>
"-R <n>" uses restricted mode <n>
"-S" uses silent mode


Enclosing with double quotes:
----------------------------
C:\orahome1\ora92\bin>sqlplus system@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=
hppkg_test)(PORT=1515))(CONNECT_DATA =(SID = TESTDB))"

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Jul 28 12:46:34 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where <logon> ::= <username>[/<password>][@<connect_string>] | /
Enter user-name:

Enclosing within single quotes
--------------------------------
C:\orahome1\ora92\bin>sqlplus system@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=
hppkg_test)(PORT=1515))(CONNECT_DATA =(SID = TESTDB))'
Usage: SQLPLUS [ [<option>] [<logon>] [<start>] ]
where <option> ::= -H | -V | [ [-L] [-M <o>] [-R <n>] [-S] ]
<logon> ::= <username>[/<password>][@<connect_string>] | / | /NOLOG
<start> ::= @<URI>|<filename>[.<ext>] [<parameter> ...]
"-H" displays the SQL*Plus version banner and usage syntax
"-V" displays the SQL*Plus version banner
"-L" attempts log on just once
"-M <o>" uses HTML markup options <o>
"-R <n>" uses restricted mode <n>
"-S" uses silent mode

C:\orahome1\ora92\bin>

more info:
am trying from windows 2k (sqlplus client)
db server is in hpunix

any help would be appreciated

Try getting rid of your spaces, sometimes throws a muddle in the middle of it all

TWO_TASK is also a viable alternative to specifying the connect string (tnsnames file needs exist with your entry in though)

Sorry ..I think I created that confusion by bringing in database links here.. 
what I meant was..

you can either create a dblink using service names or straight away in your sql statement (like below)

SQL>Create database link testdblink using 'TESTDB'; -- looks up server tnsnames.ora
Database link created. 
SQL>select instance_name from v$instance;

INSTANCE_NAME
----------------
DEV_80

SQL>select instance_name from v$instance@testdblink
  2  /

INSTANCE_NAME
----------------
TESTDB

SQL>drop database link testdblink;

Database link dropped.

    or 

SQL>CREATE DATABASE LINK testdblink
  2  USING
  3  '(description=(address=(protocol=TCP)(host=hppkg_test)(port=1515))(connect_data=(sid=TESTDB)))'
  4  /

Database link created. -- bypasses tns

SQL>select instance_name from v$instance;

INSTANCE_NAME
----------------
DEV_80

SQL>select instance_name from v$instance@testdblink
  2  /

INSTANCE_NAME
----------------
TESTDB


==============

and thanks for the tip..eliminating all spaces around '=' made the sql connection work fine (thanks..one more cool thing up the sleeve..) 

SQL>conn system@'(description=(address=(protocol=TCP)(host=hppkg_test)(port=1515))(connect_data=(sid=TESTDB)))'
Enter password: *********
Connected.
SQL>

 

Hi
Following the tips, I have tried using the entry shown below but ended up got the error message shown below. Earlier, I have forgot to mentioned that I am using Oracle Instanst Client on Win2K to connect to my company databases through windows command shell.

sqlplus user/password@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=PODS.GATE.COM)(PORT=1521)))(CONNECT_DATA=(SID=ODS))'

ORA-12154: TNS:could not resolve the connect identifier specified

Enter user-name:

So I tried this
sqlplus 'user/password@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=PODS.GATE.COM)(PORT=1521)))(CONNECT_DATA=(SID=ODS))'

but got this error
SQL*Plus: Release 10.1.0.2.0 - Production on Fri Jul 29 15:47:37 2005

Copyright (c) 1982, 2004, Oracle. All rights reserved.

Enter password:
ERROR:
ORA-12560: TNS:protocol adapter error

Enter user-name:

Is this method applicable for windows?

Thanks again for all the help.

You have unmatched brackets in the connect string.

Kah Hing,
I am also connecting thru windows command prompt.
Please remove the single quotes.
I ve tried in my system and see the response

C:\Documents and Settings\moh6033>sqlplus test/test@(description=(address_list=(
address=(protocol=TCP)(host=ht85af6)(port=1521)))(connect_data=(service_name=chu
bb)))

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Jul 29 15:44:25 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

Thanks again for all the help. It is working now.

Tom,
I have a similar issue. I am getting the same kind of error using sqlplus to connect to a remote db
sqlplus topprv/atlas@ '(description = (address_list=(address=scsatlspdb1.mydomain.com)))'
Usage: SQLPLUS [ [<option>] [<logon>] [<start>] ]
where <option> ::= - | -? | [ [-M <o>] [-R <n>] [-S] ]
<logon> ::= <username>[/<password>][@<connect_string>] | / | /NOLOG
<start> ::= @<filename>[.<ext>] [<parameter> ...]
"-" displays the usage syntax
"-?" displays the SQL*Plus version banner
"-M <o>" uses HTML markup options <o>
"-R <n>" uses restricted mode <n>
"-S" uses silent mode
then i did this :
sqlplus topprv/atlas@ '(description=(address=(protocol=TCP)(host=scsatlspdb1)(PORT=1521)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=P416)'

i keep on getting syntax error.What am i doing wrong?

Tom,
I have a similar issue. I am getting the same kind of error using sqlplus to connect to a remote db
sqlplus topprv/atlas@ '(description = (address_list=(address=scsatlspdb1.mydomain.com)))'
Usage: SQLPLUS [ [<option>] [<logon>] [<start>] ]
where <option> ::= - | -? | [ [-M <o>] [-R <n>] [-S] ]
<logon> ::= <username>[/<password>][@<connect_string>] | / | /NOLOG
<start> ::= @<filename>[.<ext>] [<parameter> ...]
"-" displays the usage syntax
"-?" displays the SQL*Plus version banner
"-M <o>" uses HTML markup options <o>
"-R <n>" uses restricted mode <n>
"-S" uses silent mode
then i did this :
sqlplus topprv/atlas@ '(description=(address=(protocol=TCP)(host=scsatlspdb1)(PORT=1521)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=P416)'

i keep on getting syntax error.What am i doing wrong?

>> i keep on getting syntax error.What am i doing wrong?

Your answer is in the error. You have some parentheses to close. Try this string: (description=(address=(protocol=TCP)(host=scsatlspdb1)(PORT=1521)(CONNECT_DATA=(
SERVER=DEDICATED)(SERVICE_NAME=P416)))

Hi Tom,

With you answer I am able to connect to database without having entry in tnsnames.ora file. 

How did you find that when try to connect to sqlplus with connect string as defined by tnsnames.ora is actually getting replaced by complete string ( what you shown to us ) ? I tried to do 

echo "sqlplus parag/parag@infod" > p.sh
sh -vx p.sh

But it does not show

[atlas]@mumsa128200: /f205/prod/jclsite > sh -vx parag.sh
sqlplus atlas/atlas@infod
[1]+ sqlplus parag/parag@infod

SQL*Plus: Release 9.2.0.6.0 - Production on Tue Aug 2 17:40:59 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

17:40:59 SQL>

Pl tell me how did you find this.

regards & thanks
pjp 

Hi Tom,

I have few more questions reagarding this thread

Q1/

On AIX when I am trying to connect to database by this method in export command, it is giving me an error

exp t/t@(DESCRIPTION = (ADDRESS = (COMMUNITY = TCP) (PROTOCOL = TCP) (Host = mumsa000200)(Port = 1521))(CONNECT_DATA =(SID = infod) ))

but while same string is working for sqlplus connection

Q2/

From oracle 8.1.7 server when I am trying to connect to Oracle 9.2 server with global_name parameter it is giving me error

sqlplus t/t@(DESCRIPTION = (ADDRESS = (COMMUNITY = TCP) (PROTOCOL = TCP) (Host = mumsa000200)(Port = 1521))(CONNECT_DATA =(SID = infod)(GLOBAL_NAME = infod)))

{4_TProd}/f205/prod/site/jcl > test.sh

SQL*Plus: Release 8.1.7.0.0 - Production on Thu Aug 4 22:04:28 2005

(c) Copyright 2000 Oracle Corporation. All rights reserved.

SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}] | [INTERNAL]
where <logon> ::= <username>[/<password>][@<connect_string>] | /
Enter user-name:

but same thing if I use "/" as user

sqlplus /@(DESCRIPTION = (ADDRESS = (COMMUNITY = TCP) (PROTOCOL = TCP) (Host = mumsa000200)(Port = 1521))(CONNECT_DATA =(SID = infod)(GLOBAL_NAME = infod)))

it is working very fine. So kindly explain me what is global_name and where it will be useful, secondly why error is comming when I am using global_name parameter ?

regards & thanks
pjp

Hi Tom,

Thanks for your answer. After removing spaces I am able to connect by SQL but with exp command in unix still I am not able to connect. Pl guide me

exp t/t@(DESCRIPTION=(ADDRESS=(COMMUNITY=TCP)(PROTOCOL=TCP)(Host=sinsa1100054)(Port=1521))(CONNECT_DATA=(SID=atpp0
1)(GLOBAL_NAME=atpp01)))

regards & thannks
pjp

I think I am mature in databae industry due to this site.

Problem : Have to select from a table in remote database 'R' from local database 'L'

The select on remote table works from other databases 'A','B' and 'C' without dblinks!! (Am not sure how! . I always thought dblinks are the only way).
The select on table from 'A','B','C' works like the following:
select * from owner.table@R.world

My questions:
There is no dblink in databases 'A','B' or 'C' named R.world. How does the select work?
How to make it work in 'L' in the same manner?

Hi,

I try to connect using sqlplus to my oracle without connect identifier
SQL> conn scott
Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory

but i can connect using scott@orcl
the orcl connect identified point to my oracle.

Thank you for you help 

I am not able to find the document 186296.1 in metalink. Is the no. correct?

seems to be restricted - can find links to it but not the doc itself

I can't - it doesn't pops up neither with "all sources" nor with "document id (knowledge base, forum, or bug)" - tried with and without the trailing ".1" just in case.

I get "Your search returned no hits!".

OPN-linked account currently without "SR Create" privilege due to snail-bureaucracy.

Alberto - In the quick find list of values you select "All Sources" and then the search works ( returns the said document)

Ciao

> Alberto - In the quick find list of values you select "All Sources"

Not quite - it pops up a forum thread named:

"Re : Strange behavior with NONEXISTENT db links "

that contains the text

"... The note is 186296.1: Without creating the database link in the database,can ..."

(I already tried that yesterday, as stated in my previous post)

Hey btw - I'm not the one interested in the document, I'm just answering Tom's request above :)

Another option for connecting with export is to escape out the '=' signs and include single quotes.

For example,

exp username/password@'(DESCRIPTION\=
(ADDRESS_LIST\=(ADDRESS\=(PROTOCOL\=TCP)(HOST\=hostname)
(Port\=1521)))(CONNECT_DATA\=(SERVICE_NAME\=ora9i)))'

This works for UNIX, I don't have a windows machine to test on.

Will this technique work if the listener is down?

On unix systems, both double quotes " and single quotes ' will do:

exp scott@'(DESCRIPTION\=(ADDRESS_LIST\=(ADDRESS\=(PROTOCOL\=TCP)(HOST\=test.com)(PORT\=1527)))(CONNECT_DATA\=(SERVICE_NAME\=TEST)))'

and

exp scott@"(DESCRIPTION\=(ADDRESS_LIST\=(ADDRESS\=(PROTOCOL\=TCP)(HOST\=test.com)(PORT\=1527)))(CONNECT_DATA\=(SERVICE_NAME\=TEST)))"

work both

An environment variable can be used ( both quotes ):

export DB="(DESCRIPTION\=(ADDRESS_LIST\=(ADDRESS\=(PROTOCOL\=TCP)(HOST\=test.com)(PORT\=1527)))(CONNECT_DATA\=(SERVICE_NAME\=TEST)))"

exp scott@$DB

No spaces are allowed in the string in either case; this will not work because of the space after the = sign:

"(DESCRIPTION\= (ADDRESS_LIST\=(ADDRESS\=(PROTOCOL\=TCP)(HOST\=test.com)(PORT\=1527)))(CONNECT_DATA\=(SERVICE_NAME\=TEST)))"

On Windows ( Windows client but db on a unix server ), only double quotes " work.
The environment variable can be used:

SET DB="(DESCRIPTION\=(ADDRESS_LIST\=(ADDRESS\=(PROTOCOL\=TCP)(HOST\=test.com)(PORT\=1527)))(CONNECT_DATA\=(SERVICE_NAME\=TEST)))"

exp scott@%DB%

Regards

Mauro Papandrea

Hi
This site is help full but it does not address this
e:\OTest()\sqlplus scott/tiger@<ConnectString instead of TNS>
please let me know if anyone has a solution to this.

Hi Tom

In your first answer you said:
...
that url like syntax is supported by (much) later releases of sqlplus.
...

What release of sqlplus does/will support something like this?
sqlplus USER/PASSWORD@//PODS.GATE.COM:1521/ODS

Hi Tom,

If I am having entry defined in tnsnames.ora, by your trick can I replace only one parameter ? for e.g. can I replace only service_name then what have defined in tnsnames ?

thanks & regards
PJP

Hi tom,

I can connect to sqlplus without entry in tnsnames.ora by defining all network aliases (same as tnsnames.ora ). for e.g.

conn scott/tiger@(description=(address=(protocol=tcp)(host=aaaa1100055.in.net.intra)(port=1521))(connect_data=(service_name=atinp2)))

Suppose, If entry has been defined in tnsnames.ora can for e.g can I replace only serivce_name parameter

thanks & regards
PJP

Why do Oracle clients need to use such complicated connect strings to reach a server? Other brands of DBs only need to know the hostname of the server (and optionally port number), username, password, and name of the database. It would be nice if the modern Oracle libraries would allow connections with this information alone, instead of requiring a TNS-formatted string with lots of field names, parenthesis, and equal signs.

As tom said, from 10g release 2 sqlplus client, "easy connect" works dandy.

Tom,
Whenever I attempt to connect via Easy Connect, I get this error:
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Can you please show an example so I can figure out where I am making a mistake? I already have tnsnames.ora setup and I can connect correctly using tnsnames.

Thanks


C:\>sqlplus system/password@db

SQL*Plus: Release 10.2.0.2.0 - Production on Thu Feb 22 15:54:41 2007

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL>

C:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.2.0 - Production on Thu Feb 22 15:55:20 2007

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

SQL> connect system/password@server:1521/db
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


SQL>

SQL> connect system/password@server:1521/db.domain
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


SQL>

I found the answer on Metalink. In the sqlnet.ora file I have to create an entry like:
NAMES.DIRECTORY_PATH= (TNSNAMES,EZCONNECT)

Now I can connect using either tns entry or easy connect. I learned something new.

Thank you very much for this segment:
I need to run EXP and IMP from my Java widget and use either the (workstation-specific) tnsNameKey, or the server-port-instance derived description format discussed above.
Adding the LOCAL env-var to the Java Process launcher allowed me to dodge the mis-parsing of the connectString in Oracle9i exp and imp.
It is a pity that this valuable tip is hidden in your archive; I found it via google.

I really appreciate the answers sent by you. I have some similar question.
I am trying to connect using:
sqlplus pic/pic@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(COMMUNITY=tcp.world)(PROTOCOL=TCP)(Host=phgp0418)(Port=65021))(GLOBAL_NAME=fln7730P.world))(CONNECT_DATA=(SID=fln7730P)))

I am getting the following error:
SQL*Plus: Release 9.2.0.1.0 - Production on Wed May 7 15:04:23 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where <logon> ::= <username>[/<password>][@<connect_string>] | /
Enter user-name:

Even If I am doing it with double quotes I am getting the same error. Please let me know how can I contact it?

Very nice way to connecting .But i believe LDAP set-up would be nice to avoid writting long syntax over command line , and would be a miss with space SQLPLUS will be not recognzing to connect to DB.

Hi Tom

We have just upgraded from 10.1 to 10.2.  

We have noticed that the EZCONNECT no longer works when specifying localhost.  Pre-upgrade both of the
following commands were successfull.  Although I don't think the problem is related to the 
upgrade itself. 

Note:IP addresses changed for security.

A side-effect is our Web Server could no longer connect to the database.  It had

    PlsqlDatabaseConnectString    localhost:1521:db

in its dads.conf.

We have altered it to 

    PlsqlDatabaseConnectString    111.111.111.111:1521:db

and it now works.

However we are at a loss as to why localhost connections don't work, and how to resolve the issue.  

Any advice would be very much appreciated.

Operating System : Microsoft Windows 2000

Oracle Versions:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

Working command.

C:\Documents and Settings\Administrator>sqlplus user/pass@//111.111.111.111:1521/db.myip.net

SQL*Plus: Release 10.2.0.4.0 - Production on Fri May 9 14:39:28 2008

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>


C:\Documents and Settings\Administrator>sqlplus user/pass@//localhost:1521/db.myip.net

SQL*Plus: Release 10.2.0.4.0 - Production on Fri May 9 14:40:09 2008

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

ERROR:
ORA-12541: TNS:no listener


Enter user-name:

The listener is running as we have many connections to the database across the network.

C:\Documents and Settings\Administrator>ping localhost

Pinging dbname.myip.net [127.0.0.1] with 32 bytes of data:

Reply from 127.0.0.1: bytes=32 time<10ms TTL=128
Reply from 127.0.0.1: bytes=32 time<10ms TTL=128
Reply from 127.0.0.1: bytes=32 time<10ms TTL=128
Reply from 127.0.0.1: bytes=32 time<10ms TTL=128

Ping statistics for 127.0.0.1:
    Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
    Minimum = 0ms, Maximum =  0ms, Average =  0ms


Copies of config files follow.

# listener.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 111.111.111.111)(PORT = 1521))
    )
  )


# sqlnet.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to 
# install "Software Only", this file wont exist and without the native 
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

# tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

DB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 111.111.111.111)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = db.myip.net)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )


We upgraded another server at the same time and both syntaxes work with it.


Regards

Steve

Tom:

1. would ez connect work with 9i and would that service name be the name of service i nthe tnsnames.ora file on the server.

CONNECT username/password@host[:port][/service_name]


2. Is there a way for all clients to use one common tnsnames.ora file on the oracle server?

Tom:

Do you mean ldap is a lot of work? or the simple file thing where there is a tns file on every machine.

The site shutdownabort.com has a very simple tool called tnsManager ( http://www.shutdownabort.com/tnsmanager ).

It acts as an Oracle Internet Directory with a very simple setup.

Tom:

If you are upgrading from 9i to 11g, what would be the easiest way to implement TNS and oracle client.

We have 50 machines with Power builder client on it. Do we need to install the oracle 11g client on each machine.

We can use EZ connect instead of TNS file but do we still need to install remove the 9i client and install 11g client on every machine

Tom:

Thanks for the info. But is there also another alternative.

Like not having oracle client on each machine but rather on a server that everyone uses for easier maintenance OR use EZ connect ( or that is only for connection strings).

I assume PB also can use ODBC and then you wont need oracle client which supports native connections.

Tom:

I think what you saying is for any client machine that has some software application regardless what language it is developed with (PB, VB, Java) that needs to talk to an Oracle database, i need to install something called "oracle Client" software.

There is no way around this. if i have 1000 machines i have to do 1000 oracle client insalls with each install of the application software. There is no way to remove the dependability of the application software from the oracle client software for connections nor there is a way to host this oracle client software on a server for all machines to use.

Tom:

thanks, great info.

But why a "browser" client can connect to oracle without any oracle client. I assume the reason is that browser does not connect directly to the DB. it is browser to web http server to mod_plsql to DB. So the client must be part of mod_plsql. correct.

Tom:

Does oracle provide free alternative to datadirect ODBC drivers. some ad hoc users use Winsql with datadirect to connect to oracle 9i server and we wont renew the datadirect license. Can we use oracle native drivers or soemthing else free?

thanks,

Tom:

It seemes oracle does provide its own ODBC drivers. would those work with any client connecting to oracle. why everyone seems to use datadirect, are they better.

http://www.oracle.com/technology/software/tech/windows/odbc/index.html

2. How do we use native connection instead of ODBC. is not this better.

tom:

According to this company their wire ODBC driver can connect from qinsql directly to oracle without the need of the oracle client software. The picture is on the right.
so does this mean some ODBS drivers may not need oracle client.

http://www.synametrics.com/SynametricsWebApp/OdbcDrivers.jsp

Tom:

Would oracle odbc drivers work instead of datadirect?
Cna we also use Net*8 (instead of ODBC) if the client is installed?

Hi tom,
my schema contains 219 tables. I got this result by using query "selct count(*) from user_tables".
Now i need to know about master tables and concern child tables from these 219 tables.

please guide me.

Thanks and Regards,
Venkat

ops$tkyte%ORA11GR1> select table_name
  2    from user_constraints
  3   where constraint_type = 'R'
  4  /

TABLE_NAME
------------------------------
MHS_ERRORS
C
SALES

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> select table_name
  2    from user_constraints
  3   where constraint_name in
  4   (select r_constraint_name
  5      from user_constraints
  6     where constraint_type = 'R')
  7  /

TABLE_NAME
------------------------------
MHS_TRANSACTIONS
P
TIME_HIERARCHY

To take it a little further, one can create a file with all connect strings (similar to tnsnames), and export them as shell vars. Then running sqlplus will be even simpler - sqlpus scott/tiger@$MY_DB
This is especially useful when using oracle instantclient, which does not have tnsnames.ora

example:
(in .tnsnames)
export MY_DB='(DESCRIPTION=....)'

(in .bashrc)
# instead of tnsnames.ora
# to connect: sqlplus unname/passwd@$MY_DB
if [ -f ~/.tnsnames ]; then
source ~/.tnsnames
fi


Hope someone finds it useful.

Hi Tom,

In my enterprise, we do not connect with tnsnames.ora. we only have sqlnet.ora and ldap.ora file with the following entries.

I am unable to configure SQL Developer tool to use sqlnet.ora for connecting to database. It has only following options. Basic, LDAP, TNS and Advanced. Could you please help me out with this. I searched all over the web but couldn't find any help.

I have hidden the company name with <domain> for security reasons.

sqlnet.ora

DISABLE_OOB = ON
AUTOMATIC_IPC = OFF
NAMES.DEFAULT_DOMAIN = <domain>.com
NAMES.DEFAULT_ZONE = <domain>.com
NAMES.REQUEST_RETRIES = 3
NAMES.INITIAL_RETRY_TIMEOUT = 30
SQLNET.EXPIRE_TIME = 10
NAMES.DIRECTORY_PATH = (TNSNAMES,LDAP)

ldap.ora

DEFAULT_ADMIN_CONTEXT = "dc=<domain>,dc=com"
DIRECTORY_SERVERS= (tns-prod:5000 )
DIRECTORY_SERVER_TYPE = OID

Thanks

Hi Tom,
I am successfully able to connect to DB, but when is try to run a sql script, it doesn't seem to work.

sqlplus 'user/pwd@(...)'
START file.sql

when i try this using a unix script, the SQL> prompt is opened and it gets stuck. Please help

Hi Naren,

You can do like this
I am connection from windows host so i had put double quotes

H:\>sqlplus xxxxx/xxxxx@"(DESCRIPTION =(ADDRESS= (PROTOCOL = TCP)(HOST=xxxx)(PORT = xxxx))(CONNECT_DATA=(SID = xxxx)))" @test.sql

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 25 10:32:41 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options


       SID
----------
        93

xxxxx@xxxxx >

Where test.sql contains

select sid from v$mystat where rownum=1;

create local tnsnames.ora file
in unix
say tnsnames.ora as below
MyPROD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = myprodhost)(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prodsid)))
then
export TNS_ADMIN=.
sqlplus username/passwd@Myprod

Use full when the host is no network but $ORACLE_HOME/network/admin/tnsnames.ra is not upto date for some reason.

Hi,

I have a Windows 2008 Box(VM Ware clone) from which I can connect to all the oracle databases on the network. There were few tnsnames and I renamed all of them. Still I can connect to all of the dbs.
Then I looked at environment variables and found no variable. So, I added a system variable TNS_ADMIN/ C:/Test and the connection works still(with a Test directory that was just created to test this).
I'm at a lost to find out how this is happening. Even in the renamed tnsnames files, there was only one db listed and that's what made me see where the system is reading other databases.

I'm sure I'm missing something here, but would appreciate if you can put few things that I should check.

Thanks,

Sujith

Thanks Tom,

That was it.

Hi Tom,

Thanks in advance.
I have the following case scenario:
Client/remote host Sun Solaris 5.9 runing Oracle 9i(9.2.0.5)
Target/db Sun Solaris 10 running Oracle 10gR2(10.2.0.4)

we've scripted several data collection jobs that are started on a remote host and connect to the prod dbs on both our primary and dr sites using follow string:
$ORACLE_HOME/bin/sqlplus -s "sys/<pwd>@gl_service_name as sysdba";

The challenge here is that we manually have to input and remove the password from these scripted-jobs every-time we need to execute.
Is there a way to hide the pwd in a variable or a file like the orapwd file instead of hardening it on the script itself?
This would be secure and convenient to maintain one central pwdfile and have all the jobs to call it a run time.

Any ideas are greatly appreciate.

Cheers
Alf

Thanks Tom,

Convenient external securing external passwords with Wallets, excellent pointer.

I've wanting to upgrade and get rid of couple of these old db/boxes, only thing is that these have to the rescue when I've needed to assist and support the developers/users of some application still on 9i. I still occasionally have users throwing questions regarding their 8i environment that was decommission about a year ago.

Cheers
Alf

u can connect to sqlplus using the below syntax. No need to have a connect_identifier in tns.ora

sqlplus username/password@host:port/ppvt

Hi Tom,

Is there any oracle view which stores the path of the tnsnames.ora which we can look.

Recently I faced one such issue.
Me and my friend were connecting to a database through Unix (DB and unix users are in different machines), but while I was able to connect my friend was getting tns error. I thought may be his client was picking up wrong tnsnames.ora file. His GID and group matched with mine. He even copied my .profile. Still was getting the same error.

Thanks
Snehasish Das.

We have certain tables with LONG datatype.  The IMPDP didn't work for those tables.  Later I used "copy" command.  to make this work, I created DB link with the same name as database name and it worked.  The problem now is, the source database and the target database are of the same name and if I couldn't use DB link like the way I mentioned above.

I tried copy command by putting the entire TNS description but it said the length is too high.
SQL> copy from tempstrmadm/tempstrmadm1_@(DESCRIPTION=(SDU=32768)(enable=broken)(LOAD_BALANCE=yes)(ADDRESS=(PROTOCOL=TCP)(HOST=gct90318.houston.hp.com)(PORT=1526))(CONNECT_DATA=(SERVICE_NAME=STRMB))) to tempstrmadm/tempstrmadm1_@(DESCRIPTION=(SDU=32768)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=g9u0173c.houston.hp.com)(Port=1525)))(CONNECT_DATA=(SERVICE_NAME=STRMB)(INSTANCE_NAME=STRMB1))) insert HR.X1 using select * from HR.X1;
SP2-0756: FROM clause length 184 bytes exceeds maximum length 122

Is there a way to overcome this problem?  I don't want to go through tnsnames.ora file by adding an entry since this whole migration is an automated process.

Thanks,

Tom,
I believe that the reviewer above must be using impdp over network link which does not support LONG data type.

Thanks...

Hi Tom,
When I login to my linux machine as oracle and try
sqlplus hr/hr
, it works fine. I could try
sqlplus hr/hr@orcl
and that also works. I try logging in as root and tried sqlplus hr/hr. It gave me
Enter user-name: [root@tcctest ~]# sqlplus hr/hr

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 26 09:19:51 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

ERROR:
ORA-12546: TNS:permission denied

I tried setting TWO_TASK = orcl and it worked. In the case where it did not work for root, ORACLE_SID was set to orcl, as seen below.
Enter user-name: [root@tcctest ~]# env | grep SID
ORACLE_SID=orcl
Could you please explain what is the difference between these connection modes (with '@', without '@', with TWO_TASK etc)? I got here by trying to trouble-shoot a goldengate replication process that throws
"OGG-00664 OCI Error during OCIServerAttach (status
= 12546-ORA-12546: TNS:permission denied)"
I am hopign your answers to my questions above will help me trouble-shoot the goldengate process.

hello there,
i want to work with oracle 11g, but the problem is that my computer is not connected with server and it is required to fill up the host string field, without that i could not get enter in it, so what should i do? Give me proper reason and solution for this problem...

i want to do salaries exam im at hyd India would you tell me from where can i do my training ??

The solution given in here was very useful.. Thanks a bunch

Hi Tom,

I have Oracle 10g client installed in my office workstation (not the server). I have no idea about the server and other related. With this client i connect to my project database where i cannot create sample tables and others for my personal learning through sql plus. Is it not possible for me to work on sql plus with the client where i learn queries and create dummy tables. Please help. It asks for username, password and host string which is the server(host) name if i am not wrong, Please help where i can practice basic sql queries.

Where to find username & password

What different connectivity methods are avialable to connect to oracle datase. I have following client how can connection establised for these.
Web Service - Delphi
Windows Service - Delphi
Web Application - ASP .Net and C#

Hi Tom,
Why it takes (10-15) SECONDS form ORACLE to return back with this message "Could not resolve connect identifier" while trying to connect to a non-existing alias ??
I mean in my tnsnames.ora these is no alias with this name "ORCL3", when i issue sqlplus usern/passw@orcl3 i know it must return the message, but what about the TIME ??

Hi Tom,
Thanks for your quick answer,
to be clear I'm working on my personal PC (name=user-hp, which is my db server, down is my tnsnames.ora
# tnsnames.ora Network Configuration File: F:\Installed\DB11gR1\app\user\product\11.1.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

DB11GR1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = user-HP)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db11gr1)
)
)


DB10GR2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = user-HP)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db10gr2)
)
)


and here is my sqlnet.ora
# sqlnet.ora Network Configuration File: F:\Installed\DB11gR1\app\user\product\11.1.0\db_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)


my question:
When i'm trying this:
sqlplus username/passw@test
it takes 10 seconds to return the message "could not resolve...", why ??????

i'm sure if you try it on your machine, it will take the same time, because i faced it many times on different machines (My Machine, Work, Customers) !!!

probably we are talking in a different levels :)
could you please rewrite (in a low level) what do you mean of "comment both out and report back, just put a # in front of them. "
i scanned all the text i wrote and i didn't got it??

sorry tom, my bad, didn't get it in the first time,

anyways, i tried it out but it still hang before the message,

Just to be more accurate,
Before comment the mentioned lines, it was taking the following:
* 2-3 seconds via TNSPING
* 7-8 seconds via SQLPLUS
after commenting:
* 4-5 seconds via TNSPING
* 17 seconds via SQLPLUS
My DB > 11.1.0.7.0

Hi Tom,

Can we know waht are the services running in DB , given host and port number?.. Please let me know

Regards
Deekshita

Hi Tom,

I would like to connect to a remote database table/views pragmatically. It has a dynamic IP which keeps on changing. But i Know the IP before connecting. Is it possible i create dynamic DB links on the fly. Using a pl/sql program and use some thing like

select * from emp@my_newly_created_dblink

Or there is any other way to connect.

Regards,

Vikas sharma

Thanks for the reply Tom.

Actually My server is at a remote location where i cannot rely on one ISP to provide internet. I would also need a mobile data card internet connection which cannot have Static IP.

Regards,
Vikas Sharma

Thanks to @Dave from UK!

Those tricky spaces! If you hadn´t mentioned, that they need to be eliminated, I would have searched for hours on end, trying to find the error why my, otherwise correct, connection string doesn´t work.

sqlplus user@'//host:port/sid'

hi,

how to connect sql developer with jdbc using cmmand prompt

Hi All,
I was unable to connect to SQL Plus as I have forgotten my user name and password. Tried many different ways, but it didn't work out can anyone of you suggest me how to proceed further.
Tried to uninstall and reinstall Oracle but it didn't work out.

Waiting for your reply

Oracle 18c installed on Windows Server 2012 R2 SE

I tried to stop any outside connections to a DB. When local TNS Listener is disabled, I scanned the server and found this random port open for Oracle,

PORT STATE SERVICE REASON VERSION
49183/tcp open oracle syn-ack ttl 125 Oracle Database

then I can use this port and Oracle SID to connect to the DB by using SQLDeveloper or Toad from other machines. Is this a security concern and how can you stop it? Thanks!

Great!
Your solution works for me just using connection string without tnsnames.ora.
Thanks a lot!

Tom's first suggestion worked ONLY after I put the cxn string incl parenthesis in quotes like this:

sqlplus user/pw@"(description=(address_list=(address=.......ODS)))"