Datafiles

Datafiles are the files stored on disk that hold the data in the database. They are the physical structures in which Oracle stores information. Datafiles are created and manipulated indirectly when the CREATE TABLESPACE command or the ALTER TABLESPACE command is issued. A datafile takes up exactly the amount of space specified in the creation statement, regardless of whether a single object is created in the associated tablespace. Any datafile can belong to exactly one tablespace.

Tablespaces

Databases are made up of tablespaces, purely logical constructs that allow you to group related logical structures together. Tablespaces, the primary logical structure of the database, are commonly used as a means to group together all objects common to a single application to simplify some administrative operations; however, you do not have to only store related objects in a given tablespace.

Any particular tablespace is made up of one or more datafiles, and the combined size of all the datafiles associated with a tablespace is the total storage capacity of that tablespace.

The following sections discuss some special case tablespaces. Although they share many of the same characteristics as "normal" tablespaces, these have either special features or special purposes.

System Tablespace

A database must have at least one tablespace, the system tablespace, for the database and the associated instance to be created. The system tablespace holds the data dictionary information and is required for the database to build. You can never drop the system tablespace.

Temporary Tablespace

Temporary tablespaces are created so that large sort jobs that won't fit into available memory have somewhere to perform their work. If a temporary tablespace is created at database creation time, it is automatically assigned to users as their default temporary tablespace when the users are created.

Undo Tablespace

For the instance to take advantage of automatic undo (the Oracle supported means of undo as opposed to the previously preferred manual rollback segment managed undo), the database needs to have an undo tablespace. An undo tablespace must be created as an UNDO TABLESPACE for Oracle to use it as such. This tablespace houses the undo information for transactions and provides the facility for a read-consistent image of the data in the database. The following is the command that should be used to create an undo tablespace.

The only tablespace that Oracle creates automatically is the system tablespace. Although it is created automatically, you do, however, have to inform Oracle where you want the datafiles associated with the system tablespace to be located.

Figure 1.2 shows the relationship between data blocks, extents, segments, datafiles, and tablespaces.

Figure 1.2. Relationship between structures in the database.

Synonym

Synonyms are, simply, aliases that point to database tables, views, other synonyms, and PL/SQL program units. When a synonym name is used, Oracle automatically replaces its reference with the name of the object on which the synonym is defined. Oracle first checks to see whether any Private synonyms are defined (those created in a specific schema and accessible only by the schema that owns it). If Oracle can't find a Private synonym, it then looks for a Public synonym (those owned by the PUBLIC schema, open and available for reference by all database schemas).

Schema

A schema is a collection of database objects all owned by a database user. A schema has the same name as the user who owns the objects. A user can exist if no schema objects exist, but a schema cannot exist without an associated user account.

Schema Objects

Schema objects are logical structures that belong to a schema. These objects either directly refer to the database's data or are logical constructs that allow you to interact with the data (as in the case of views or synonyms).

Table

Tables are the basic unit of storage for data in the database. Tables are made up of rows and columns.

Index

Indexes are considered optional structures in the database, built on one or more columns in a table. Oracle automatically maintains the indexes when the data in the table is altered, inserted, or deleted. Their purpose is to improve performance of data retrieval. Similar to the index in this book, an index in the database allows the instance to assist the user in more quickly finding the information requested in a select statement. Indexes should be used judiciously and based on the requirements for the database in question. They can have adverse effects on performance in updates, inserts, and deletes. If you have an OLTP database where there are many of these transactions, indexes could prove to be bottlenecks in your processing.

View

Views are customized representations, often considered to be a stored query, of data that exist in one or more tables or other views. Views don't actually contain data, but rather derive data from the underlying tables on which they are based each time they are accessed. These underlying tables are referred to as the base tables of the view. Views can be queried and, depending on the type of view, inserted into, deleted from, and updated, with some restrictions. Because views are only conceptual representations of underlying data, operations that you perform are performed on the base tables of the views. Views enable you to hide the complexity of the data and of the queries on which they are based, freeing end users from the need to have as deep of an understanding as they would if they had to construct the query on their own.

Redo Log Files

Every Oracle database has a set of two or more redo log files. The set of files is collectively known as the redo log for the database and is made up of redo entries (also known as redo records).

The primary function of the redo logs is to record all changes made to any data in the database. If the instance experiences a failure preventing the modified data from being permanently written out to the datafiles, those changes can be obtained from the redo log when the instance is restarted so that work is not lost.

To protect against any failure that might occur involving the redo logs themselves, Oracle allows you to, and in fact suggests that you do, use multiplexed redo logs so that two or more copies of the redo logs can be maintained on different disks. This is to ensure that, in the event of disk failure, at least one copy of the redo logs is retained.

The information located in a redo log file is used to recover the database from either a system or media failure that would prevent the database data from being written to the datafiles before the instance stops. An example of this would be an unexpected power outage that terminates the instance's operation causing the data in memory to not be written out to datafiles. Ordinarily, you would think that the data that hadn't been written would be lost. However, this lost data can be recovered and written out to the correct data files when the instance is reopened after power is restored. By allowing and facilitating the information in the most recent redo log files to be written out to the database's datafiles, Oracle restores the database to the time when the power failure occurred. For this example, we assumed that the redo log buffers have had all their changes written out to the online redo log files on disk. If this is not the case, for example in a sudden loss of power to the server, there is no way to recapture the data that never made it from the buffers to the redo log files.

The process of reapplying the data located in the redo log during a recovery operation is called rolling forward.

Control Files

Every Oracle database instance combination has a control file (or a multiplexed control file to protect the control file against corruption or disk failure). A control file contains those entries that specify the physical structure of the database, including the following information:

• Database name

• Names and locations of all datafiles, the undo tablespace files, and redo log files

• Time stamp of database creation