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
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.
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?
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
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
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
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?
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
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?
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
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 :)
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.
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?
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.
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
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
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
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.
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?
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.
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
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?
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.
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
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
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.
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.
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.
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
odbc
A reader, October 14, 2008 - 5:55 pm UTC
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?
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
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.
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
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
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
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
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.
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,
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...
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.
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 ??
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
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 ??
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) !!!
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??
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
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
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
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
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
July 05, 2017 - 12:34 am UTC
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
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!
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!
June 09, 2020 - 4:55 pm UTC
Great, glad we could help.
Excellent!!
Nelson Crowe, March 12, 2023 - 9:22 pm UTC
Tom's first suggestion worked ONLY after I put the cxn string incl parenthesis in quotes like this:
sqlplus user/pw@"(description=(address_list=(address=.......ODS)))"
March 14, 2023 - 1:14 am UTC
Note that since 18c, EZCONNECT can also take additional parameters, alleviating you from the need to specify an entire "tns" style string