How users connect to the Oracle database server

To use an Oracle database, the user first has to establish a connection to the database. For database connections, Oracle offers two solutions: private and shared connections.

In most cases, we use a private connection. For a private connection, the user starts an application on the client, such as Sql*plus, and then initiates a user process locally on the client. After a successful connection with an Oracle server, a corresponding server process is generated on the database server, which acts as a proxy process for the user process, also known as a shadow process, in place of the client to execute various commands and return the results. That is, the various commands that the user enters on the client are sent to the server-side corresponding server process through the user process located at the client, and the server process replaces the user process to execute the specific commands and return results. A user process cannot access the database directly. In private connection mode, the user process corresponds to the server process one by one, and the server process terminates as soon as the user process terminates.

In addition, we also have a connection method, called a shared connection. In the case of a shared connection, the DBA can define the number of server processes. When the database is started, Oracle establishes the specified number of server processes in the instance in advance. At this point, the user process no longer has a one-to-one relationship with the server process, but a one-to-many relationship. That is, a user process can correspond to multiple server processes, and multiple server processes can handle different commands issued by the same user. Shared connections are relatively rare, so we don't discuss them much. As long as there are no special instructions, this refers to a private connection.

When a user successfully establishes a connection, the corresponding server process is generated on the server side, while a session is created. The so-called session is a memory space, which records the user what application to connect to the database, the name of the client machine, to which user name login and other information.

Here you need to explain two confusing concepts in Oracle: Connection (connection) and session.

The so-called connection, refers to the physical concept. That is, the communication channel from the client to the server side. There are three types of connection.

Native logon: Log on to the database directly from the server where the database resides. Then the internal communication mechanism is used to connect.

C/S mode: Both the client and the server are in the same LAN, and the end user logs into the database from the client and uses the Oracle network components to establish a connection with the database server through the network device.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

b/S mode: Also called three-layer mode. The client does not have an Oracle network component, establishes a connection through the browser to the application server, and then picks the connection channel on the application server's pre-established connection pool.

Let's take a local login as an example to look at the concept of user process and server process. We start Sql*plus and log in to the database instance.

[Oracle@book ~]$ Sqlplus/nolog

Sql*plus:release 10.2.0.1.0-production on Wed Oct 10

13:34:12 2007

Copyright (c) 1982, +, Oracle. All rights reserved.

Sql> Connect/as SYSDBA

Connected.

We went to the operating system to query the process (assuming only one user is currently logged on to the database using the Sqlplus command).

[Oracle@book ~]$ Ps-ef|grep Sqlplus

Oracle 3036 2841 0 13:27 pts/1 00:00:00

Sqlplus

Oracle 3070 3041 0 13:29 pts/2 00:00:00

grep sqlplus

You can see the process number of the sqlplus process is 3036, which is the process we started, the user process. Continue to query:

[Oracle@book ~]$ Ps-ef|grep 3036

Oracle 3036 2841 0 13:27 pts/1 00:00:00

Sqlplus

Oracle 3037 3036 0 13:27 00:00:00

oracleora10g

(Description= (Local=yes) (address= (PROTOCOL=BEQ))

Oracle 3074 3041 0 13:29 pts/2 00:00:00 grep 3036

You can see that the No. 3036 process spawned a process, that is, the No. 3037 process, which is the server process that Oracle created for the user process (process number 3036). As you can see from the description of the server process, Local=yes indicates that the process started with another process running on the same server as the database itself, that is, a native login. The PROTOCOL=BEQ description uses the Bequeath protocol login instead of the TCP protocol.

Once the user establishes a connection to the database server, a variety of commands can be issued to begin using the Oracle database.