oraboy
July 28, 2005 - 1pm Central time zone
Reviewer: oraboy from MI
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
Followup July 28, 2005 - 1pm Central time zone:
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=152
1)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ora9ir2.kyte.com)))'
+ sqlplus
'scott/tiger@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=152
1)))(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.
July 28, 2005 - 1pm Central time zone
Reviewer: Dave from UK
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
July 28, 2005 - 2pm Central time zone
Reviewer: oraboy from MI usa
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
July 29, 2005 - 4am Central time zone
Reviewer: Kah Hing Ting from Singapore
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)))(C
ONNECT_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)))(C
ONNECT_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.
Followup July 29, 2005 - 8am Central time zone:
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
July 29, 2005 - 4am Central time zone
Reviewer: Colin 't Hart from The Hague, The Netherlands
You have unmatched brackets in the connect string.
Attention Kah Hing
July 29, 2005 - 6am Central time zone
Reviewer: mohammad illiyaz from Chennai,India
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
July 29, 2005 - 9am Central time zone
Reviewer: Kah Hing Ting from Singapore
Thanks again for all the help. It is working now.
HELP ME TOO
July 30, 2005 - 12pm Central time zone
Reviewer: A reader
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)(S
ERVICE_NAME=P416)'
i keep on getting syntax error.What am i doing wrong?
Followup July 30, 2005 - 2pm Central time zone:
well, if you are on windows stop using the quotes as stated above.
HELP ME TOO
July 30, 2005 - 12pm Central time zone
Reviewer: Big M from charlotte, NC USA
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)(S
ERVICE_NAME=P416)'
i keep on getting syntax error.What am i doing wrong?
In reply to...
August 1, 2005 - 4pm Central time zone
Reviewer: Stephen Horn from Out-in-the-sticks, MO USA
>> 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 ?
August 2, 2005 - 8am Central time zone
Reviewer: Parag Jayant Patankar from India
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
Followup August 2, 2005 - 10am Central time zone:
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
August 4, 2005 - 10am Central time zone
Reviewer: Parag Jayant Patnkar from India
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
Followup August 4, 2005 - 10am Central time zone:
lose the spaces
still exp command I can not use connect
August 5, 2005 - 1am Central time zone
Reviewer: Parag Jayant Patankar from India
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
Followup August 5, 2005 - 10am Central time zone:
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 ()
August 8, 2005 - 8am Central time zone
Reviewer: Rajeev from INDIA
I think I am mature in databae industry due to this site.
Unable to select from table in remote database
September 30, 2005 - 10am Central time zone
Reviewer: A reader from USA
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?
Followup September 30, 2005 - 11am Central time zone:
see note Note 186296.1 in metalink
Got error without connect identifier
October 15, 2005 - 12am Central time zone
Reviewer: Peter from Indonesia
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
Followup October 15, 2005 - 9am Central time zone:
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
January 7, 2006 - 1pm Central time zone
Reviewer: A reader from VA,USA from VA,USA
I am not able to find the document 186296.1 in metalink. Is the no. correct?
Followup January 7, 2006 - 4pm Central time zone:
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?
January 7, 2006 - 5pm Central time zone
Reviewer: Dave
seems to be restricted - can find links to it but not the doc itself
January 7, 2006 - 5pm Central time zone
Reviewer: Alberto Dell'Era from Milan, Italy
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
January 8, 2006 - 8am Central time zone
Reviewer: A reader
Alberto - In the quick find list of values you select "All Sources" and then the search works (
returns the said document)
Ciao
Followup January 8, 2006 - 11am Central time zone:
thanks! appreciate that.
works for you, not for me
January 8, 2006 - 11am Central time zone
Reviewer: Alberto Dell'Era from Milan, Italy
> 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 :)
Followup January 9, 2006 - 7am Central time zone:
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
January 10, 2006 - 6am Central time zone
Reviewer: A reader
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.
Followup January 10, 2006 - 8am Central time zone:
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?
January 10, 2006 - 4pm Central time zone
Reviewer: James Valenti from Olympia, WA
Will this technique work if the listener is down?
Followup January 10, 2006 - 7pm Central time zone:
this describes how to connect to a listener! You need the listener to be up...
A very small addition
January 12, 2006 - 3am Central time zone
Reviewer: Mauro Papandrea from Italy
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_DAT
A\=(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_DAT
A\=(SERVICE_NAME\=TEST)))"
exp scott@%DB%
Regards
Mauro Papandrea
Mypath is e:\OTest()\sqlplus scott/tiger@<ConnectString instead of TNS>
February 3, 2006 - 9am Central time zone
Reviewer: Pravin Wadekar from INDIA
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.
Followup February 3, 2006 - 5pm Central time zone:
address WHAT exactly?
e:\OTest()\
what is that???
what is a "connect string" instead of "TNS"???
url like syntax
March 9, 2006 - 9am Central time zone
Reviewer: beat from Switzerland
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
Followup March 9, 2006 - 2pm Central time zone:
that was new with 10gr1
Can I replace only part of tnsnames.ora
September 28, 2006 - 4am Central time zone
Reviewer: Parag Jayant Patankar from India
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
Followup September 28, 2006 - 4am Central time zone:
what 'trick' are you referring to?
not really following what you are asking here.
partly replace tnsnames.ora
September 28, 2006 - 5am Central time zone
Reviewer: Parag Jayant Patankar from India
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
Followup September 28, 2006 - 6am Central time zone:
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
December 13, 2006 - 11am Central time zone
Reviewer: Lance E Sloan from Michigan, USA
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.
December 14, 2006 - 11pm Central time zone
Reviewer: A reader
As tom said, from 10g release 2 sqlplus client, "easy connect" works dandy.
Easy connect
February 22, 2007 - 4pm Central time zone
Reviewer: A reader
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
February 23, 2007 - 8am Central time zone
Reviewer: A reader
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
June 27, 2007 - 6pm Central time zone
Reviewer: John sisson from Sacramento, CA
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
May 7, 2008 - 3pm Central time zone
Reviewer: Mohit from USA
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?
Followup May 8, 2008 - 3am Central time zone:
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
May 8, 2008 - 7am Central time zone
Reviewer: Bhabani from INDIA
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.
Followup May 12, 2008 - 8am Central time zone:
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
May 9, 2008 - 10am Central time zone
Reviewer: Steve Mangan from Leeds, UK
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
Followup May 12, 2008 - 10am Central time zone:
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
September 21, 2008 - 11pm Central time zone
Reviewer: sam
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?
Followup September 22, 2008 - 5pm Central time zone:
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
September 22, 2008 - 6pm Central time zone
Reviewer: A reader
Tom:
Do you mean ldap is a lot of work? or the simple file thing where there is a tns file on every
machine.
Followup September 24, 2008 - 3pm Central time zone:
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
September 23, 2008 - 3pm Central time zone
Reviewer: To m Bouwman from Veghel, NL
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
October 7, 2008 - 9am Central time zone
Reviewer: A reader
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
Followup October 8, 2008 - 9pm Central time zone:
... 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
October 8, 2008 - 11pm Central time zone
Reviewer: A reader
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.
Followup October 9, 2008 - 10am Central time zone:
... 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
October 9, 2008 - 6pm Central time zone
Reviewer: sam
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.
Followup October 13, 2008 - 2am Central time zone:
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
October 13, 2008 - 6pm Central time zone
Reviewer: A reader
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.
Followup October 14, 2008 - 6pm Central time zone:
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
October 14, 2008 - 1pm Central time zone
Reviewer: A reader
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
October 14, 2008 - 1pm Central time zone
Reviewer: A reader
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
October 14, 2008 - 5pm Central time zone
Reviewer: A reader
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
Followup October 14, 2008 - 6pm Central time zone:
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
October 14, 2008 - 6pm Central time zone
Reviewer: A reader
Tom:
Would oracle odbc drivers work instead of datadirect?
Cna we also use Net*8 (instead of ODBC) if the client is installed?
Followup October 15, 2008 - 5pm Central time zone:
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
October 15, 2008 - 10am Central time zone
Reviewer: Venkat from INDIA
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
Followup October 15, 2008 - 5pm Central time zone:
...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
October 22, 2009 - 12pm Central time zone
Reviewer: Alex Yelluas from Santa Clara, CA
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.
Followup October 23, 2009 - 1pm Central time zone:
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.
|