Oracle Architecture

A reasonably strong theoretical understanding of Oracle architecture will prove to be of great practical benefit later in the developing of applications to work with the Oracle database. While at first, it might seem that to work with a database system as well-developed as Oracle, all you really have to know is how to record and retrieve data, the practical reality is that knowing how Oracle works "under the hood" will pay off.

Once you understand the internal structure and mechanism of the Oracle database system, you will find yourself developing much more efficient applications-more efficient in terms both of processing speed and space requirements. If you have a reasonably strong technical understanding of how Oracle actually works, when creating objects such as tables, for example, you will have a much better sense of how much space to allocate to the object, where to put the object, and so forth.

To begin with, we need to understand that Oracle, like any database, stores data its user tells it to store. In addition, though, Oracle stores other information-data for its own use. Let's examine these two species of data stored and used.

The first type of data, the data the user stores for his or her own application's use, is appropriately termed USER data. This data can be stored and used in many ways, as well as customized according to the user's needs. You, the user, can decide the type of the data and the amount you want to store. You enjoy essentially complete control over the user data.

The second type of data, which Oracle spontaneously generates for itself, which the system uses, updates, or deletes on its own, is referred to as SYSTEM data. What is remarkable about Oracle's system data is that Oracle is able to generate and manage this type of data on its own in order to keep itself functioning smoothly and consistently. This data, which the user has little or no control over, Oracle generates and stores in internal tables during the initial installation. The sort of information stored as system data includes the number of users, their passwords, their privileges, the number of tables the user is creating, the kind of data being stored, how much data is stored, and so on. How Oracle actually functions as a database depends largely upon its system data. Finally, although users cannot access system data, Oracle database administrators routinely work with this data in order to fine-tune Oracle's performance.

The Oracle Database has three types of structures in which it stores and maintains User and System data.

1. A Logical structure, that maps the data to the Physical structure.
2. A Physical structure, that is part of the operating system's file structure.
3. A Memory structure, where all the processing takes place.

The Logical structures control how the data must be stored in the database. There are five Logical structures, namely, tablespaces, segments, extents, data blocks, and schema objects. and five Physical structures, namely, parameter files, password files, datafiles, redo log files, and control files that reference data.

Every program uses a process to communicate with the Oracle database. These processes run in the System's Random Access Memory (RAM). In addition to Logical and Physical structures, there are two main Memory structures, namely, the System Global Area (SGA) and the Program Global Area (PGA). The Oracle database uses these memory areas to store information before they are made permanent in the database.

The purpose of a database is to both store and manipulate information. The Oracle database controls this dual process efficiently by maintaining the Physical and Logical structures separately, a feature allowing for modification of physical data without the need for accessing the Logical structure, and vice-versa. By way of illustration, consider the following: assume that you have a database table for a retail outlet that stores information about its salespeople. Initially, you decide to store only the first name, the last name, the address, the phone number, and the social security number of each salesperson. Later on, you decide to store the city, state, and zip code information in separate columns instead of including them with the address. Since the Logical and Physical structures in an Oracle database are maintained separately, you can easily accomplish such changes by just adding the columns to the Logical structure (which is a table, in this case), and then inserting the data. This data will be updated in the file-system, a Physical structure. What about the reverse of this? If you have to change the contents of the data (something you will do often), you may end up changing the Logical structure as well each time.

Logical structures control how the actual data will take up available storage space. The two different sets of Logical structures available to support this purpose are:

• Tablespaces, segments, extents, and data blocks, each of which provide a different level of organization and storage units-tablespaces being the highest level.
• Schema objects, which reference data in the database.

The Physical structures occupy the disk space available to store data. These structures include:

• Password File, which contains the password information for all users.
• Parameter File, which contains all the important information necessary to start a database.
• Data Files, which contain all the data in the Oracle database.
• Redo Log Files, which keep track of changes made to the data.
• Control files, which contain information about the Physical structure, such as the database name, location, etc., and also the system change number for the last change made to the database, allowing for the restoration of the database to its last change before a crash.

Memory areas are used to manage the storage of data. There are two memory areas:

• The System Global Area (SGA), which contains data and control information for a database instance and is accessible to all users and server processes that are running.
• Program Global Area (PGA), which contains data for one process, and which therefore is accessible within different parts of a process, but not outside of it.