User interaction with Oracle DB detailed process

Interacting with Oracle DB
The following example describes the Oracle DB operation from the most basic level. This example illustrates an Oracle DB configuration in which the user and the associated server processes run on different computers that are connected over a network.
1. An instance is started on a node that has Oracle DB installed (often referred to as a "host" or "Database Server").
2. The user launches an application that derives a user process. The application attempts to establish a connection to the server (this connection may be a local connection, a client/server connection, or a three-tier connection from the middle tier).
3. The server runs a listener with the corresponding Oracle Net Services handler. The listener detects the connection request made by the application and creates a dedicated server process that represents the user process.
4. The user runs a DML-type SQL statement and commits the transaction. For example, the user changes the customer address in the table and submits the change.
5. The server process receives the statement and checks if there is a shared SQL region in the shared pool (an SGA component) that contains the same SQL statement. If a shared SQL zone is found, the server process checks the user for access to the requested data and processes the statement using the existing shared SQL zone. If a shared SQL region is not found, the statement is assigned a new shared SQL region to parse and process the statement.

6. The server process retrieves any required data values from the values stored in the actual data file (table) or in the database buffer cache.
7. The server process modifies data in the SGA. Because the transaction is committed, the log write process (LGWR) immediately logs the transaction to the Redo log file. The database write process (DBWN) writes the modified block to disk permanently when it is executed efficiently.
8. If the transaction succeeds, the server process sends a message over the network to the application. If the transaction is unsuccessful, an error message is sent.
9. Throughout the process, other background processes are running at the same time to monitor for situations in which there is a need for intervention. In addition, the database server manages transaction processing for other users and prevents contention between transactions that request the same data.

User interaction with Oracle DB detailed process