Oracle Basics

The Oracle server is a computer or set of computers that run the Oracle DBMS and host one or more databases. This server allows multiple users to simultaneously access the same data. Built-in security and login authentication prevent unauthorized access. Users may access Oracle databases located on a remote computer across a network; Oracle communicates with remote systems through any network protocol like TCP/IP via its built in Transport Network Substrate (TNS) on the Oracle Net Foundation Layer. In addition, Oracle offers ODBC drivers that allow MS Windows applications to connect to an Oracle server. Oracle provides SQL*Plus, an interactive query tool that allows users to send multiple commands to the server at one time (batch mode). Users may use a command-line interface, a Graphical User Interface (GUI) or a web based user interface to issue SQL command sequences and perform other database object manipulations.

A set of related tables may be stored as a single Oracle database. Oracle databases may be started-up and shut down. Startup and shutdown have nothing to do with the actual storage or representation of the data within; starting up a database simply makes the data within available to clients (any computer that tries to access the data via a program or application).

With version 8.0, Oracle started to accommodate object-relational databases capable of storing not only plain data but references to objects. Although the relational features are still most prominent, object-oriented features are beginning to be utilized in certain contexts. An 'object' in the OO context within a database does not just contain a value; it is also associated to certain properties and methods that describe its behavior. These methods are normally programs that accomplish the described behavior.

When a database is started, Oracle starts associated processes (a process application or utility that is currently executing on the computer's CPU) and assigns the process a RAM area referred to as a System Global Area (SGA). User access, services and executed commands are performed on the assigned RAM and through children of the Oracle process spawned for the database. The RAM and processes are together known as an oracle instance. Thus, every currently running database is an instance.

Fig 1c: Oracle Instance and Sessions

The SGA consists of three components. The database buffer is a memory area that contains recently used data. The database buffer cache is made up of all the database buffers that belong to an instance. The most frequently accessed data is kept in RAM memory via the Database Buffer Cache. This improves the performance as disk I/O operations take time. The Redo log buffers contain a record of changes made to the database. These entries are also written to an online redo log - two or more files that record all changes made to oracle data files and control files. These logs facilitate recovery from erroneous modifications. The shared pool caches entities like the SQL shared areas that are common to all users.

A memory buffer called the Program Global Area (PGA) is allocated with each database user's session. The PGA contains session specific information such as SQL statement data or buffers used for sorting.

Oracle uses certain files while starting up the oracle server or starting up an instance. These files are called parameter files; they contain name value pairs that correspond to important variables (i.e.. values referred to by names) used by the server. The binary server parameter file contains parameters that do not change with instance startup and shutdown. Other text files contain lists of instance initialization parameters. Changes to these files will take effect when the instance is re-started.

The Oracle server allows the database administrator to create users with preset levels of access. Users are assigned (and may change) an authentication password. Each user is also assigned certain privileges and roles. A privilege corresponds to operation permissions - some users may be allowed to just view contents of tables; others may be allowed to add information while yet others may be allowed to change the structure of the database by adding new tables. Roles are pre-set groups of privileges. An administrative role entails a certain set of privileges while a user role entails a more restricted set of privileges.

The data dictionary is the repository for the database metadata or data that defines the actual data in the database. The dictionary contains data about data; users may learn more about data objects in a database through the dictionary. The name of each column, restrictions on its contents and so on may be viewed through the data dictionary. Oracle stores table definitions (and definitions of all other objects) into the data dictionary when the table (or object) is created. The Oracle server uses this metadata to interpret and execute SQL statements and manage the database. Database metadata is stored in a relational table just like the data the metadata describes. Oracle provides many views that present the metadata in user friendly formats.

Oracle allows database administrators to continuously monitor performance and currently executing queries using a number of Dynamic Performance Views . These views are maintained by the oracle Server itself and are continuously changed to reflect current performance parameters measured in a number of different ways. They simply retrieve data on internal disk and memory structures and display relevant information.

Oracle provides a logical entity called the Recycle bin. Tables that have been deleted are held in the recycle bin and may be restored if they were deleted in error. The Recycle bin function may be turned on and off by modifying the value of a variable in an initialization parameter file.