How a user interact with an Oracle Database

Hi DBA-Mates,

If we want to learn and understand about oracle database, we should know how the user and database interact with each others. How a user interacting with an Oracle Database?

This example will describes the Oracle database operations at the most basic level. It illustrates an Oracle database configuration in which the user and associated server process are on separate computers, connected through a network.

1. Suppose, an instance has started on a node where Oracle Database is installed, that is also called the database server or host.

2. And a user starts an application spawning or start a user process. The application attempts to establish a connection to the server. This connection can be a local, client/server, or a three-tier connection from a middle tier.

3. Now, the server runs a listener that has the appropriate Oracle Net Services handler. And as we know that the listener detects the connection request from the application and creates a dedicated server process on behalf of the user process.

4. Suppose, a user runs a DML statement command and then commits the transaction. As example, the user changes the salary of a customer in a table and commits the changes.

5. Here, the server process receives the statement and checks the shared pool (an SGA component) for any shared SQL area that contains an identical 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 existing shared

SQL area is used to process the statement.

And if a shared SQL area is not found, a new shared SQL area is allocated for the statement so that it can be parsed and processed.

6. After checking above details, the server process retrieves any necessary data values, either from the actual data file (table) or from values stored in the Database buffer cache.

7. Then the server process modifies data in the SGA. Because the transaction is committed, the LogWriter process (LGWR) immediately records the transaction in the redo log file.

As we all know that Database Writer process (DBWn) writes modified blocks permanently to disk when it is efficient to do so.

8. Now, if the transaction is successful, the server process sends a message across the network to the application. And if the transaction is not successful, an error message is transmitted.

For kind information, we have mentioned only top level orimportant steps and because 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.

Hope these may also helpful:

ORACLE APPS

DATAGUARD SETUP

JOBS TIPS

IMP SQL SCRIPTS

Hope above points is cleared. Please let us know for any concerns or suggestions either through comments or contact @ora-data.blogspot.com.

Remember, we would love to hear from you.

Thanks,