Home » SQL & PL/SQL » Client Tools » SQL*Plus on machine Oracle server is installed - TCP/IP or some sort of socket conection? (Oracle 11g on Solaris)
SQL*Plus on machine Oracle server is installed - TCP/IP or some sort of socket conection? [message #623259] Sun, 07 September 2014 09:57 Go to next message
rc3d
Messages: 213
Registered: September 2013
Location: Baden-Württemberg
Senior Member
Hi

I do:

$ sqlplus user/pass @SID


on the local machine Oracle server is installed, but with an application user (not oracle). This application user has Oracle client installed an uses TNSnames to resolove SID.

Is the connection over TCP/IP?

Not shared memory or socket?
Re: SQL*Plus on machine Oracle server is installed - TCP/IP or some sort of socket conection? [message #623260 is a reply to message #623259] Sun, 07 September 2014 10:01 Go to previous messageGo to next message
BlackSwan
Messages: 26204
Registered: January 2009
Location: SoCal
Senior Member
>$ sqlplus user/pass @SID
>Is the connection over TCP/IP?
not over TCP/IP
turn off listener & connection will still succeed

>$ sqlplus user/pass@SID
[NOTE: there is no space character preceeding the "@" sign above]
above tries to use SQL*Net & will succeed if everything is properly configured.
Re: SQL*Plus on machine Oracle server is installed - TCP/IP or some sort of socket conection? [message #623261 is a reply to message #623260] Sun, 07 September 2014 10:13 Go to previous messageGo to next message
rc3d
Messages: 213
Registered: September 2013
Location: Baden-Württemberg
Senior Member
thanks BlackSwan. What mechanism does Oracle RDBMS use in case "$ sqlplus user/pass @SID"?
Re: SQL*Plus on machine Oracle server is installed - TCP/IP or some sort of socket conection? [message #623262 is a reply to message #623261] Sun, 07 September 2014 10:17 Go to previous messageGo to next message
BlackSwan
Messages: 26204
Registered: January 2009
Location: SoCal
Senior Member
BEQ protocol
Re: SQL*Plus on machine Oracle server is installed - TCP/IP or some sort of socket conection? [message #623263 is a reply to message #623261] Sun, 07 September 2014 10:22 Go to previous messageGo to next message
EdStevens
Messages: 985
Registered: September 2013
Senior Member
With a space before the @, the connection will be bequeath and then sqlplus will attempt to execute the SQL script named "did.SQL"
Re: SQL*Plus on machine Oracle server is installed - TCP/IP or some sort of socket conection? [message #623264 is a reply to message #623263] Sun, 07 September 2014 10:42 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3131
Registered: May 2013
Location: World Wide on the Web
Senior Member
In case someone misinterprets, Ed meant "sid.sql" Smile
Re: SQL*Plus on machine Oracle server is installed - TCP/IP or some sort of socket conection? [message #623266 is a reply to message #623259] Sun, 07 September 2014 11:27 Go to previous messageGo to next message
rc3d
Messages: 213
Registered: September 2013
Location: Baden-Württemberg
Senior Member
thank you guys.

Is that correct, that I should use only this:

$ sqlplus user/pass @SID


for scripts located on the Oracle server? Because if Listener down, scripts still running and better performance (no TCP/IP overhead)?

http://www.orafaq.com/wiki/Bequeath_Protocol

Quote:

Bequeath Protocol is a SQL*Net protocol that is similar to the IPC protocol in that it is only used for local connections (when client and server programs reside on the same system). BEQ connections does not require a listener and can only establish Dedicated Server connections.
Re: SQL*Plus on machine Oracle server is installed - TCP/IP or some sort of socket conection? [message #623268 is a reply to message #623266] Sun, 07 September 2014 11:41 Go to previous messageGo to next message
Michel Cadot
Messages: 65849
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

$ sqlplus user/pass @script

does not use sqlnet and so listener, connects and execute "script.sql" script.

Re: SQL*Plus on machine Oracle server is installed - TCP/IP or some sort of socket conection? [message #623273 is a reply to message #623266] Sun, 07 September 2014 12:38 Go to previous messageGo to next message
EdStevens
Messages: 985
Registered: September 2013
Senior Member
rc3d wrote on Sun, 07 September 2014 11:27
thank you guys.

Is that correct, that I should use only this:

$ sqlplus user/pass @SID


for scripts located on the Oracle server? Because if Listener down, scripts still running and better performance (no TCP/IP overhead)?


Let's get back to basics.
When you enter a command - any command - the command processor delimits the command line by spaces. The first string of text is taken as the name of some executable. The command processor will attempt to locate and load said executable. Everything on the command line after the first space is taken to be parameters used by the executable for whatever purpose the executable sees fit.

So when you enter 'sqlplus user/pass @sid', the command processor will attempt to locate an executable named 'sqlplus', load it, and pass process control to it, along with pointers to "user/pass" and "@sid". sqlplus assumes the first parameter (in this case "user/pass" is username and password for a connection request. Since that particular parameter (delimited by the space) did not include an "@", the connection will be to a local database identified by the environment variable ORACLE_SID. In parsing out the rest of the parameters (in this case, the only additional parameter is "@sid") it will see the "@" and take what follows ("sid") as the name of a sql script (in the case "sid.sql"). That script must be located on a file system accessible to the same machine from which sqlplus is executing. With no further qualifications, it will look for "sid.sql" in the "current" directory that was in effect when sqlplus was started.

Back to the initial command line. Remember, spaces are used to delimit the command from its parameters, and delimit the various parameters. So if you enter "sqlplus user/pass@sid" (no space before the "@"), then the "@sid" is part of the first parameter and so is part of the connection specification. In this case the @ is the delimiter denoting that what follows is the net service name to be resolved to locate the database via tns.

Suppose we enter "sqlplus user/pass@sid @sid"?

As for your assumption about performance, in theory yes a bequeath connection should involve less overhead than a network connection. But I defy you to demonstrate a meaningful difference.

I'd also strongly recommend you spend some time in the SQL Plus User Guide and Reference, esp. "SQLPLUS Program Syntax" http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_three.htm#i1169374
Re: SQL*Plus on machine Oracle server is installed - TCP/IP or some sort of socket conection? [message #623325 is a reply to message #623264] Mon, 08 September 2014 07:33 Go to previous message
EdStevens
Messages: 985
Registered: September 2013
Senior Member
Lalit Kumar B wrote on Sun, 07 September 2014 10:42
In case someone misinterprets, Ed meant "sid.sql" Smile



Yep. Fat fingers trying to reply on my "smart" phone.
Previous Topic: Connectivity tool
Next Topic: in the decode function & symbol not working and it is working as Bind Variable
Goto Forum:
  


Current Time: Sun Oct 21 03:23:45 CDT 2018