An Example of How Oracle Works

The following example describes the most basic level of operations that Oracle
performs. This illustrates an Oracle configuration where the user and associated
server process are on separate machines (connected through a network).
1. An instance has started on the computer running Oracle (often called the host
or database server).
2. A computer running an application (a local machine or client workstation)
runs the application in a user process. The client application attempts to
establish a connection to the server using the proper Oracle Net Services driver.
3. The server is running the proper Oracle Net Services driver. The server detects
the connection request from the application and creates a dedicated server
process on behalf of the user process.
4. The user runs a SQL statement and commits the transaction. For example, the
user changes a name in a row of a table.
5. The server process receives the statement and checks the shared pool for any
shared SQL area that contains a similar SQL statement. If a shared SQL area is
found, then the server process checks the user’s access privileges to the
requested data, and the previously existing shared SQL area is used to process
the statement. If not, then a new shared SQL area is allocated for the statement,
so it can be parsed and processed.
6. The server process retrieves any necessary data values from the actual datafile
(table) or those stored in the SGA.
7. The server process modifies data in the system global area. The DBWn process
writes modified blocks permanently to disk when doing so is efficient. Because
the transaction is committed, the LGWR process immediately records the
transaction in the online redo log file.
8. If the transaction is successful, then the server process sends a message across
the network to the application. If it is not successful, then an error message is
transmitted.
9. Throughout this entire procedure, the other background processes run,
watching for conditions that require intervention. In addition, the database
server manages other users’ transactions and prevents contention between
transactions that request the same data.