Skip to Main Content
  • Questions
  • How to connect SQLPlus without tnsnames.ora

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Kah Hing.

Asked: July 27, 2005 - 8:24 pm UTC

Last updated: June 09, 2020 - 4:55 pm UTC

Version: 8.1.7

Viewed 100K+ times! This question is

You Asked

Hi
I am trying to use SQLPlus to connect to database directly using just the connect string (ie without referencing to tnsnames.ora)

I have this in my tnsnames.ora
POD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = PODS.GATE.COM)(PORT = 1521))
)
(CONNECT_DATA =
(SID = ODS)
)
)

I can connect to the database if I use the command below, obviously the SQLPlus is refering the tnsnames.ora that I have)
sqlplus USER/PASSWORD@POD

However, if I do this, I will the error shown below.
sqlplus USER/PASSWORD@//PODS.GATE.COM:1521/ODS

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

Is there any way, I can connect to the database without refering to tnsnames.ora as defined in the TNS_ADMIN system environment variables?

Thanks in advance for your help

KH Ting

and we said...

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

will work (use quotes if in Unix else the () are interpreted by the shell)

that url like syntax is supported by (much) later releases of sqlplus.

Or you can use the EZconnect syntax

sqlplus user/password@//hostname/service_name

sqlplus user/password@//hostname:port/service_name

Rating

  (84 ratings)

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

Comments

oraboy

oraboy, July 28, 2005 - 1:06 pm UTC

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

Tom Kyte
July 28, 2005 - 1:24 pm UTC

that is not a "dblink", it is a tns connect string. very very VERY different


[tkyte@desktop tkyte]$ sh -vx test.sh
sqlplus 'scott/tiger@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ora9ir2.kyte.com)))'
+ sqlplus 'scott/tiger@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ora9ir2.kyte.com)))'

SQL*Plus: Release 9.2.0.4.0 - Production on Thu Jul 28 13:17:47 2005

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


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

scott@ORA9IR2>


try losing the spaces or use " in windows I think.

Dave, July 28, 2005 - 1:13 pm UTC

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)

It worked

oraboy, July 28, 2005 - 2:35 pm UTC

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>

 

How to connect SQLPlus without tnsnames.ora", version 8.1.7

Kah Hing Ting, July 29, 2005 - 4:00 am UTC

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.

Tom Kyte
July 29, 2005 - 8:49 am UTC

C:\Documents and Settings\tkyte>sqlplus scott/tiger@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=tkyte-pc.us.oracle.com)(POR
T=1521))(CONNECT_DATA=(SERVICE_NAME=ora10g)))

SQL*Plus: Release 10.1.0.4.0 - Production on Fri Jul 29 08:41:13 2005

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


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

scott@ORA10G>




' are unix

" are windows - you can use "



Att: Kah Hing Ting

Colin 't Hart, July 29, 2005 - 4:43 am UTC

You have unmatched brackets in the connect string.

Attention Kah Hing

mohammad illiyaz, July 29, 2005 - 6:32 am UTC

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

"How to connect SQLPlus without tnsnames.ora", version 8.1.7

Kah Hing Ting, July 29, 2005 - 9:34 am UTC

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



HELP ME TOO

A reader, July 30, 2005 - 12:13 pm UTC

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 Kyte
July 30, 2005 - 2:01 pm UTC

well, if you are on windows stop using the quotes as stated above.

HELP ME TOO

Big M, July 30, 2005 - 12:16 pm UTC

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?

In reply to...

Stephen Horn, August 01, 2005 - 4:16 pm UTC

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

How did you find this ?

Parag Jayant Patankar, August 02, 2005 - 8:22 am UTC

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 

Tom Kyte
August 02, 2005 - 10:17 am UTC

the tnsnames is just a bunch of ALIASES, aliases are optional. I just "knew", I don't remember where or when I learned it - it just works.

Few more questions

Parag Jayant Patnkar, August 04, 2005 - 10:16 am UTC

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









Tom Kyte
August 04, 2005 - 10:32 am UTC

lose the spaces

still exp command I can not use connect

Parag Jayant Patankar, August 05, 2005 - 1:12 am UTC

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

Tom Kyte
August 05, 2005 - 10:58 am UTC


export TWO_TASK="(DESCRIPTION=(ADDRESS_LIS....DATA=(SERVICE_NAME=ora10g)))"
exp userid=scott/tiger


will fix that up, appears that export is "parsing" the information and not liking the ()

Rajeev, August 08, 2005 - 8:34 am UTC

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

Unable to select from table in remote database

A reader, September 30, 2005 - 10:28 am UTC

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?





Tom Kyte
September 30, 2005 - 11:48 am UTC

see note Note 186296.1 in metalink

Got error without connect identifier

Peter, October 15, 2005 - 12:57 am UTC

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 

Tom Kyte
October 15, 2005 - 9:49 am UTC

what do you need?

if the database is remote, then you use a tns connect string to get to it.

Only if you are on the same machine with the server would you just use "scott/tiger"


If you have a remote machine and want to default a connect string, on unix:

export TWO_TASK=orcl

on windows,

set LOCAL=orcl

(you can use control panel -> system to set environment variables once and for all in windows)

186296.1

A reader from VA,USA, January 07, 2006 - 1:27 pm UTC

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

Tom Kyte
January 07, 2006 - 4:44 pm UTC

Just cut and pasted the number into the search field and it popped right up. I don't think it is protected. can anyone else not see it?

Dave, January 07, 2006 - 5:06 pm UTC

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

Alberto Dell'Era, January 07, 2006 - 5:18 pm UTC

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.

works

A reader, January 08, 2006 - 8:58 am UTC

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

Ciao


Tom Kyte
January 08, 2006 - 11:44 am UTC

thanks! appreciate that.

works for you, not for me

Alberto Dell'Era, January 08, 2006 - 11:57 am UTC

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

Tom Kyte
January 09, 2006 - 7:37 am UTC

gist of note:

you do not need to create a database link if you are using naming services and global names. You can select * from dual@global.database.name; without having issued create database link global.database.name

Other option for EXP

A reader, January 10, 2006 - 6:45 am UTC

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.

Tom Kyte
January 10, 2006 - 8:31 am UTC

of course, sound of hand smacking forehead! That is why exp wouldn't take it directly on the command line, the "=" ! well done.

Does this always work?

James Valenti, January 10, 2006 - 4:47 pm UTC

Will this technique work if the listener is down?

Tom Kyte
January 10, 2006 - 7:29 pm UTC

this describes how to connect to a listener! You need the listener to be up...

A very small addition

Mauro Papandrea, January 12, 2006 - 3:05 am UTC

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

Mypath is e:\OTest()\sqlplus scott/tiger@<ConnectString instead of TNS>

Pravin Wadekar, February 03, 2006 - 9:18 am UTC

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.

Tom Kyte
February 03, 2006 - 5:16 pm UTC

address WHAT exactly?


e:\OTest()\

what is that???


what is a "connect string" instead of "TNS"???

url like syntax

beat, March 09, 2006 - 9:09 am UTC

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

Tom Kyte
March 09, 2006 - 2:56 pm UTC

that was new with 10gr1

Can I replace only part of tnsnames.ora

Parag Jayant Patankar, September 28, 2006 - 4:11 am UTC

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



Tom Kyte
September 28, 2006 - 4:28 am UTC

what 'trick' are you referring to?

not really following what you are asking here.

partly replace tnsnames.ora

Parag Jayant Patankar, September 28, 2006 - 5:15 am UTC

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

Tom Kyte
September 28, 2006 - 6:56 am UTC

i don't know what you mean by "can i replace only service_name parameter" in this context.

You can put this entry in a tnsnames.ora file and use a short name instead of it.

If you are asking:

can I put something like:

myconnection= (description=(.....))

in my tnsnames.ora and then somehow use it but override the service name on the command line, that would be "no"

you either use an alias or you do not.

Oracle Connect Strings Unnecessarily Complicated

Lance E Sloan, December 13, 2006 - 11:49 am UTC

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.

Tom Kyte
December 14, 2006 - 9:55 pm UTC

we call that "easy connect", it exists, you can use it, it be there.
</code> http://docs.oracle.com/docs/cd/B19306_01/server.102/b14196/network001.htm#sthref193 <code>

guess oracle isn't so hard after all? it is "different", which many people equate with 'hard'

but guess how hard the "easy" ones would be if you knew Oracle... but not them...


think about it :)

A reader, December 14, 2006 - 11:38 pm UTC

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

Easy connect

A reader, February 22, 2007 - 4:03 pm UTC

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>

Easy Connect

A reader, February 23, 2007 - 8:57 am UTC

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.

Running EXP and IMP from Java

John sisson, June 27, 2007 - 6:00 pm UTC

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.

How to connect using sqlplus without tns

Mohit, May 07, 2008 - 3:29 pm UTC

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?
Tom Kyte
May 08, 2008 - 3:30 am UTC

no operating system
no cut and paste of the actual command line

I don't know what you are doing wrong. It works for me.

You must be making some mistake on your operating system as far as escaping special characters go

(and it might be single quotes you need)

Connecting SQLPLUS wityhout TNS entry

Bhabani, May 08, 2008 - 7:44 am UTC

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.
Tom Kyte
May 12, 2008 - 8:30 am UTC

umm, of course it would.

so would using a tnsnames.ora.

so would ez-connect

but, that is not what was requested...

EZCONNECT problems using localhost

Steve Mangan, May 09, 2008 - 10:03 am UTC

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 Kyte
May 12, 2008 - 10:32 am UTC

localhost is 127.0.0.1 - that would be a safe IP address to publish since everyone has one of those, it is the loopback link.


I doubt this has anything to do with oracle

I think this has everything to do with a network configuration you have - were you really using localhost? Was mod plsql running on the same machine as the database? (127.0.0.1 would require that). What did localhost resolve to, what DOES localhost resolve to now.


Your ping shows 127.0.0.1
Your configuration files show an entirely different IP address - whats up with that?

tnsnames

sam, September 21, 2008 - 11:06 pm UTC

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 Kyte
September 22, 2008 - 5:06 pm UTC

1) it was a 10g new feature way back when 10g was new.

2) if you use a global naming service (ldap) - sure sort of (you wouldn't use tnsnames.ora, that is just a simple file).

if you use a simple file, you'd have to make that file available to all clients (network share, nfs, whatever). Not highly recommended.

tns

A reader, September 22, 2008 - 6:29 pm UTC

Tom:

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


Tom Kyte
September 24, 2008 - 3:19 pm UTC

it should be rather obvious that a) setting up, installing, maintaining, backing up an ldap server would be more work than b) not doing so. However, if the decreased work you would have by not maintaining multiple tnsnames.ora files all over the place is offset by that work - then you have a net reduction in work.

That is: as with all things, it depends. You sort of need to analyze this in the CONTEXT of who you are, what you do, what your needs are, etc etc etc (the same thing I keep saying to you smk)

The 'simple file' thing is called a file server. You would have to mount that simple file to everyone - it would have to always be available, it is not easy to make redundant, it too requires work (setting up the file share, making sure it is available, etc)

tnsManager

To m Bouwman, September 23, 2008 - 3:31 pm UTC

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.

tns

A reader, October 07, 2008 - 9:17 am UTC

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 Kyte
October 08, 2008 - 9:22 pm UTC

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

no, you do not need to - the 9i clients can connect to an 11g database just fine. You will not be able to take advantage of any new 11g stuff - but since powerbuilder doesn't use anything special that is probably OK.

client

A reader, October 08, 2008 - 11:29 pm UTC

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 Kyte
October 09, 2008 - 10:41 am UTC

... Like not having oracle client on each machine but rather on a server that
everyone uses for easier maintenance ...

describe that for me please.


... OR use EZ connect ( or that is only for
connection strings).
...

that is only for connect strings.


You can use the "instant client" - just the networking libraries - that is available, but that means upgrading the clients. It would be smaller than what is currently installed but you would have to verify powerbuilder supports it (and that release of it) and then deinstall/install the instant client on each machine.

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

In order for ODBC to work, since it is simply an API layer, you would need to have the Oracle client installed. ODBC is just an API, it doesn't do anything unless you use it against Microsoft (it is their API). You need to install the Oracle client to have ODBC work against Oracle.

Maybe you are thinking about the type-4 JDBC thin drivers, you can install them and have connection to Oracle (assuming you install the Oracle type-4 drivers) because the type-4 drivers have the Oracle client burned into them already.

connection

sam, October 09, 2008 - 6:45 pm UTC

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 Kyte
October 13, 2008 - 2:24 am UTC

Until you said "java", sure.

Java has a 'thin jdbc driver', a pure 'java driver'. A java distributed application can include this in their distribution.

Other things that use odbc, oci, anything like that - need the Oracle client software.

client

A reader, October 13, 2008 - 6:01 pm UTC

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 Kyte
October 14, 2008 - 6:02 pm UTC

a browser connects to a web server.

the web server in turns connects to the database. and the web server (application server, whatever you want to call it) has the oracle client.

browser talks http to web server, web server talks sqlnet to Oracle.

drivers

A reader, October 14, 2008 - 1:07 pm UTC

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,

drivers

A reader, October 14, 2008 - 1:31 pm UTC

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.

odbc

A reader, October 14, 2008 - 5:55 pm UTC

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 Kyte
October 14, 2008 - 6:15 pm UTC

I think what you'll find is you run their listener on the server - meaning they have their protocol, they talk to their server, their server talks to Oracle.



odbc

A reader, October 14, 2008 - 6:19 pm UTC

Tom:

Would oracle odbc drivers work instead of datadirect?
Cna we also use Net*8 (instead of ODBC) if the client is installed?
Tom Kyte
October 15, 2008 - 5:16 pm UTC

ODBC is just an api.

In order to use ODBC, you need sqlnet and oci at some level.

So, do you want to program using the API that is "ODBC" - if so, follow the installation directions for your odbc driver of choice. That might entail

a) installing and configuring the oracle client on the client machine along with the odbc driver which is just a layer on top of oci - the oracle call interface.

b) installing and confuring the ODBC client on the client machine along with their odbc driver which in this case is a layer on top of their API which talks to their server which then talks to our server using OCI.



How to find out child tables

Venkat, October 15, 2008 - 10:59 am UTC

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
Tom Kyte
October 15, 2008 - 5:41 pm UTC

...Now i need to know about master tables and concern child tables from these 219
tables.
....

that does not "compute" right off - not sure what you mean. If you mean "how many tables are parent tables in a parent/child relationship and how many tables are child tables", you would use user_constraints.

the following shows "child tables" in my schema, then parent tables in my schema.

I assume everything is in a single schema, else you have to use the ALL or DBA views and include the OWNER column to do it right...


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

A quick followup to the sqlplus url syntax

Alex Yelluas, October 22, 2009 - 12:31 pm UTC

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.
Tom Kyte
October 23, 2009 - 1:41 pm UTC

I would not put that in a file .tnames.ora like that


we look for ~/.tnsnames.ora naturally, we expect that file to be a tnsnames.ora file


use another file name please - we already use that one.

sql developer connect with sqlnet.ora

Ravi, February 19, 2010 - 12:03 pm UTC

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

Running sql scripts

Naren, February 19, 2010 - 12:38 pm UTC

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

Tom Kyte
February 25, 2010 - 12:53 am UTC

.. when i try this using a unix script ...

that makes me suspicious, are you trying to run sqlplus from a script? You'd need to show us how, probably - what you are missing is "EXIT" after start - if your goal was to

a) start sqlplus
b) run file.sql
c) exit back to your script


Running SQL Script

A reader, February 25, 2010 - 10:36 am UTC

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;

Mahesh another thought

Mahesh, February 28, 2010 - 11:09 pm UTC

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.


I can connect to remote db with no tns. How does the system do that??

Sujith, May 10, 2010 - 3:50 pm UTC

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
Tom Kyte
May 10, 2010 - 7:13 pm UTC

EZCONNECT

Sujith, May 11, 2010 - 8:17 am UTC

Thanks Tom,

That was it.

hide password on scripted jobs

Alf, May 27, 2010 - 10:47 am UTC

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
Tom Kyte
May 27, 2010 - 10:58 am UTC

http://docs.oracle.com/docs/cd/B19306_01/network.102/b14266/cnctslsh.htm#i1006413


but that would be 10g and above, it would be an *excellent* reason to consider upgrading - at least upgrading the client that runs the scripts (you don't have to upgrade everything - just the client that does the scripting)

hide password on scripted jobs

A reader, May 30, 2010 - 11:30 pm UTC

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

coonect like this

Mayank Jain, April 19, 2011 - 12:03 am UTC

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
Tom Kyte
April 19, 2011 - 6:35 pm UTC

"U" is dead, look it up, you'll see.

You sound like a 12 year old when YOU use "U" as a "word". It isn't.


and you can connect to an Oracle database (you do not conect to "sqlplus", sqlplus is a program, not a server), using that ezconnect method IF and ONLY IF it has been configured on the server. Read the above page for information on that.

Oracle View Contaning path of tnsnames.ora

snehasish das, April 29, 2011 - 3:44 pm UTC

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.
Tom Kyte
May 01, 2011 - 8:23 am UTC

No, but only because the tnsnames.ora is a very "personal" thing.

it could be in the default location FOR SOME SESSIONS
and it could be in another non-default location FOR OTHER SESSIONS


the tnsnames.ora file is found either in one of the default locations ($ORACLE_HOME/network/admin, /var/opt/oracle, etc) OR we might use the TNS_ADMIN environment variable to find it.


Suppose you connected to oracle on the server using "sqlplus user/password". Suppose you have the environment variable TNS_ADMIN set to /x/y. YOUR SESSION will look in /x/y for the tnsnames.ora file.

Suppose someone else connects to oracle with "sqlplus user/password" on the same server. And suppose they did not set the tns admin. They might find it in $ORACLE_HOME/network/admin.

Suppose someone else connects to oracle via dedicated server with "sqlplus user/password@tnsname" using the listener. The listener's environment had tns admin set to /a/b. Their session will use the tnsnames found in /a/b since they get their environment from the listener.

suppose someoneone else connects to oracle via shared server with "sqlplus user/password@tnsname" using the same listener. The shared server gets its environment from the environment of the database at startup time. Suppose the database has a tns admin of /foo/bar. They will use /foo/bar/tnsnames.ora in their session.

Now, that just covers the DATABASE SESSIONS (database link resolution). Each client itself might have its own tns admin set as well - and that would dictate where they get their configuration from when trying to resolve THEIR tnsname connect string.

copy command...

A reader, June 07, 2011 - 3:25 pm UTC

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 Kyte
June 07, 2011 - 4:41 pm UTC

impdp works with longs - not sure what issue you ran into, but it'll be fine.

the sqlplus copy command doesn't use database links - not sure what that is about, the discussion on dblinks and unique names is a red herring, copy doesn't use them.


I don't know why you have a from and a to, you just need one or the other. see this:

http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551268808763917945

for a quick example.

impdp

A reader, June 08, 2011 - 12:56 pm UTC

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

Thanks...
Tom Kyte
June 08, 2011 - 1:09 pm UTC

ah, that makes sense. If true. It is not clear.

good

A reader, July 04, 2011 - 2:53 am UTC


SQL Plus

Jayadevan, September 26, 2011 - 4:37 am UTC

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.
Tom Kyte
September 26, 2011 - 7:33 am UTC

without @, you are connecting locally - without using the network, you have to be on the machine where the database is.

when you use @connect_string - we are connecting over the network - via a listener. The database can be anywhere in the network.


A sid and a connect string bear little relationship to each other. A connect string might resolve to a longer connect string via your tnsnames.ora (typically used for name resolution) that references the sid - but in general the sid and connect string have no relationship to one another.

krunal, October 11, 2011 - 12:21 am UTC

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

sufian, October 12, 2011 - 4:39 am UTC

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

Tom Kyte
October 12, 2011 - 7:08 am UTC

Not really, I don't have any idea what a "salaries exam" is or does.

connect to DB when no entry in tnsname.ora

Rajesh, November 18, 2011 - 2:56 pm UTC

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

Please help

Shini Sharma, March 01, 2012 - 1:15 am UTC

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.

oracle

santi, April 15, 2012 - 9:11 pm UTC

Where to find username & password
Tom Kyte
April 16, 2012 - 1:07 am UTC

where ever you wrote it down?

no idea what else you might mean, you sort of make up the username and password yourself.

there are some builtin accounts, but they are locked - you'd have to unlock them and then you could assign a password.

and when you installed, you gave us the sys/system passwords


If you have access to the oracle software account, the OS account that owns the software, you should be able to connect as "/ as sysdba"

Connectivity Methods

A reader, June 06, 2012 - 3:02 am UTC

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#

15 seconds of my life goes everyday :)

Ahmad Al-Sallal, June 08, 2012 - 1:17 am UTC

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

Tom Kyte
June 08, 2012 - 7:16 am UTC

probably network host name resolution timing out

what is in your sqlnet.ora

Could not resolve connect identifiers

Ahmad Al-Sallal, June 08, 2012 - 3:36 pm UTC

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) !!!
Tom Kyte
June 08, 2012 - 3:59 pm UTC

comment both out and report back, just put a # in front of them.

Conti.

Ahmad Al-Sallal, June 09, 2012 - 7:00 am UTC

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??
Tom Kyte
June 09, 2012 - 7:18 am UTC

edit your sqlnet.ora

comment out both lines



#SQLNET.AUTHENTICATION_SERVICES= (NTS)

#NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

sorry

Ahmad Al-Sallal, June 09, 2012 - 2:02 pm UTC

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

I didn't get my answer yet :(

Ahmad Al-Sallal, June 12, 2012 - 4:08 pm UTC


Tom Kyte
June 13, 2012 - 3:01 am UTC

show use the output of a tnsping from the command line

also, try enabling client tracing and see if anything useful comes out from that
http://www.oracle.com/pls/db112/search?remark=quick_search&word=trace_level_client

and please verify that you really did comment out the settings in the only copy of the sqlnet.ora you have on the machine - search and make sure that is the only one and double check it is really commented out.


Deekshita, July 10, 2012 - 12:32 am UTC

Hi Tom,

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

Regards
Deekshita
Tom Kyte
July 11, 2012 - 4:54 pm UTC

you can use

$ lsnrctl services

to discover this

Remote db link with Dynamic IP

Vikas Sharma, May 14, 2013 - 7:19 pm UTC

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

Tom Kyte
May 14, 2013 - 9:33 pm UTC

If the OS cannot resolve the hostname to an IP address, you would have to create the database link "on the fly" (which would be bad). you would use execute immediate and dynamic sql in plsql (which again, would be a bad bad idea)

why cannot the host resolve this name for you?

having a server using dhcp is not a really good idea in general...

dynamic DB link..

Vikas Sharma, May 15, 2013 - 4:33 pm UTC

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
Tom Kyte
May 16, 2013 - 8:37 am UTC

still, the ISP should be able to give you a hostname they can resolve.

your IP address is not relevant, that you are on a mobile data card doesn't matter.


if they cannot resolve that hostname, then you would be stuck recreating the dblink.


Tell me - how are YOU going to know what the ip address is if they keep changing it???

It helped thanks!

Shiva, June 04, 2013 - 3:18 pm UTC


Tricky spaces!

Hannah Czerny, May 28, 2014 - 6:50 am UTC

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 windows EZ-connect valid string

vladius, June 20, 2014 - 8:39 am UTC

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

lakshmanan, July 03, 2017 - 5:08 am UTC

hi,

how to connect sql developer with jdbc using cmmand prompt

Unable to connect in SQL Plus

Praneeth A, May 19, 2019 - 5:42 am UTC

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
Connor McDonald
May 21, 2019 - 4:05 am UTC

If you are a local administrator, you should be able to:

sqlplus / as sysdba

and then do:

alter user MYUSER identified by MYPASSWORD account unlock;

Still can connect to Oracle DB without TNS Listener from another computer

A reader, August 01, 2019 - 6:38 pm UTC

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!
Connor McDonald
August 05, 2019 - 9:03 am UTC

That's definitely a port I'm familiar with, ie, I don't think it part of any defaults I know of except for Siebel.

I'll ask around internally, but I think you'll need to log a call with Support on this one.

A reader, June 09, 2020 - 4:08 pm UTC

Great!
Your solution works for me just using connection string without tnsnames.ora.
Thanks a lot!
Chris Saxon
June 09, 2020 - 4:55 pm UTC

Great, glad we could help.