Connecting to Oracle

A user makes a request using a Oracle client software to connect to the database.  For example, the client software can be SQLPlus. 

sqlplus user/password@oracle10g.localdomain

The user supplies the username and password.  The target database is represented by a TNS (Transport Network Substrate, a piece of software that make remote connection) service name.  The TNS name is translated to a hostname and port number using mapping file like tnsname.ora file, or Oracle Internet Directory (OID, a LDAP).

A TNS listener on the database server will listen on the port for incoming connection request.  For a dedicated server connection, the listener will fork() and exec() to create a new UNIX process to communicate with the user.  The dedicated server process will execute SQL request, read data from files or find data in the SGA cache.

For shared server (previously called MTS, Multi-threaded Server) connection, Oracle uses a pool of shared processes to handle user requests.  However, user does not talk directly to the shared process like a dedicated server connection.  User talks to a process called dispatcher.  The  dispatcher put user request into a request queue in SGA.  The first shared process that is not busy will pick up the request from the queue, execute the request and put the result into a respond queue in SGA.  The dispatcher picks up the result and sends back to the user over network.  When user connect to the database, the listener will choose a dispatcher from the dispatcher pools and redirect the client to the dispatcher by sending the port the dispatcher listens to.  The client software then talks to the dispatcher directly.