oracleconnectionservice-name

Oracle database connections - what are all the fields I need to fill in?

I'm used to using SQL Server and I'm now faced with connecting to Oracle. Can I get some completely unambiguous descriptions for what all the fields I need to fill in are?

Specifically, I want to understand what these are:

Home
Hostname
Port
SID
Service name

Network Alias
Connect identifier

My basic understanding is that hostname is the pooter it's sat on, but is this different to home? Port is the TCP port and it defaults to 1521 - that seems pretty clear.

I only need to provide either SID or service name? And what's the difference - why one or the other?

If I have a TNS file, what is the network alias and connect identifier? Are these in anyway the same as the other fields I have if I don't use a TNS file?

Sorry to be such a noob, but my preliminary searching to get answers still has me very confused.

Thanks!

Solution 
  • Home:     ORACLE_HOME, an environment variable that points to the location 
of the Oracle binaries (either location Instance runs from on server or client
runs from on client)
Hostname: name of the server
Port:     Port on which the Listener is listening for Oracle connections
SID:      **S**ervice **ID**entifier.  The name of the the Database.  This is
one of the identifiers that the Listener will expose
Service name: An alternate identifier that may be exposed by the Listener

    On an existing, configured site the easiest way to find these details is from a tnsnames.ora file on a client (or server) from which connections can be made to the Database. Look under $ORACLE(underscore)HOME/network/admin. Find the ORACLE(underscore)HOME with a set (Windows) or env (Unix) command. The tnsnames.ora might also be in a location pointed to by the variable $TNS_ADMIN.

    If a tnsnames.ora cannot be found and you have access to the server try the following command, generally as user Oracle

    lsnrctl status

    lsnrctl is the Listener. Status will show SIDs and Service Names it knows of (and a couple of other details)

    Or find the files listener.ora and sqlnet.ora under $ORACLE_HOME/network/admin or in the location pointed to by the env variable $TNS_ADMIN

    Generally there will be one listener per host, therefore one Port per host (there could be more but its not common)

    To connect to Oracle you have to point the client to the Listener at a location specified by a Hostname/Port combination and tell it which SID or Service to connect too.

    DCookie's comment about SID versus Service name is essentially correct.

    The SID can be found with (depending on version)

    select db_unique_name from v$database
   or
select db_name from v$database

    Or by looking in the file $ORACLE_HOME/dbs/init(SID NAME).ora or by doing 

    ps -ef | grep pmon

    and noting the last part of the process name, e.g. ora(underscore)pmon(underscore)SID