Creating a Database in Oracle 9i - How Data is Stored
(Page 2 of 3 )
Data in Oracle is stored in tables and accessed data via a relational model. This means that one may use the tables of data items inside the database in a manner such that the tables relate to one another with the use of primary and foreign keys.
Tables that contain a foreign key are often translated as being a lookup table. Each table should have a primary key, so that each row can be uniquely identified. The primary key is used to connect with the foreign key in another table to form a relationship.
Oracle also supports object-orientated structures. This allows the database to include abstract data types and methods. Due to this object orientation property, objects may related to other objects and that object may exist inside other objects.
Files are used to store data in Oracle. Oracle 9i removes the risk of having orphan file. That is, like the name suggests the file doesn’t have a parent, which means that the datafile was not removed when it’s tablespace is removed. To prevent this from ever happening to your database again, Oracle has introduced “Oracle Managed Files”.
Oracle Managed files, abbreviated as OMF, uses a brand new system of storing files. OMF uses file system directories as opposed to filenames for files that are associated with the tablespace.
This means that when a tablespace is created, the files are given a unique system-generated name, using the locations that have been assigned to them along with two new initialization parameters. So when the tablespace is removed, the files will be removed with it.
Another new feature in Oracle 9i is the System Managed Undo, abbreviated as SMO. The SMO was created to make the creation of the database simpler for the DBA and to reduce the problem relating to misused rollback segments. The DBA now creates a tablespace as an UNDO type to allow the Oracle database to dynamically and automatically manage the number of undoes, along with the size of the undo segments within the tablespace.
A database is partitioned using tablespaces. The tablespace named SYSTEM is the databases default tablespace. These tablespaces allows for the grouping of elements within the system that transact with the database.
A tablespace contains files. These files are called datafiles, which are used to physically store data for the database. Notice this property suggests that datafiles are internal and external structure types, since they are parented by a tablespace (internal property) and are physical storage files (external). The datafile may be resized after it has been created.
Rules about Tablespaces and Datafiles
- A tablespace is parented by the database. So a tablespace cannot be a part of another database.
- A datafile is parented by the tablespace. So a datafile cannot be part of another database.
- A datafile may not be removed from a tablespace.
Now that we know how data is stored using Oracle, there are other special physical files that are associated with data storage and will assist in data recovery. The files that are created when the database in created and they are as follows,
- Redo log files, which will monitor transactions in a chronological manner. These files are known as online redo log files. The reason why this is an important file to have associated with the database is evident when the database malfunctions.
According to the Oracle 9i DBA Handbook, a database should consist of 3 or more online redo files. To archive old online redo files before they are inevitably over written, you may set the database to run in ARCHIVELOG mode. This allows the DBA to look further back into the history of transactions with the database.
- Control files, which holds the location of all the files the database will require. Multiple copies are made and stored on different disks to back up this operation-critical file. The CONTROL_FILES parameter specifies the names of the database control files. An instance must be shut down prior to an addition of a new control file.
- Temp files, which acts as a temporary storage for a result set. If the result exceeds the memory available in the RAM’s buffer, then the result set will be stored into the temporary data file. Make sure that the size of the file is large enough to hold large sorting operations.
The other files associated with an Oracle 9i database are called parameter files. There are two types of parameter files. These are,
- Static parameter files – This is what the init.ora file is. This are common known as a PFILE and should be based on the init.ora file and renamed as init{SID}.ora, where SID is the system identifier.
- Server parameter files – This is a new feature in Oracle 9i named the SPFILE. These parameter files are written in binary and should not be altered manually. The files should be named as spfile{SID}.ora, where SID is again the system identifier. The Oracle server always maintains these files.
To create the SPFILE we do the following,
CREATE SPFILE = ‘<directory>\spfile{SID}.ora’
FROM PFILE = ‘init{SID}.ora’
There are two types of parameter that are located in these parameter files. These are,
- Implicit parameter, which are parameters with no value. Oracle therefore assumes that the value for the parameter is the Oracle default values.
- Explicit parameter, which are parameter that have a value assigned to it.
Now we know how the parameter files work. Well, to access data inside the database, Oracle uses a set of background processes that are shared between users. The DBA Handbook states that an instance is “ a set of memory structures and background processes that access a set of database files.” Every instance has a large memory structure known as the System Global Area, abbreviated as SGA. The SGA will be explained in another article.
But where do the parameter fit in?
Well the parameters are used to set the size and composition of an instance and are the stored in one of the two specific parameter files as stated previously. The parameter file is read during the startup process. So, to access a database one must start an instance, which may call either the PFILE or the SPFILE. If there exist an SPFILE, then the STARTUP command will read that parameter file. If the SPFILE doesn’t exist, the default SPFILE is read. If that doesn’t exist than the PFILE is read. If you don’t wish for the server to read the PFILE, then simply override the default SPFILE with the PFILE.
STARTUP PFILE = ‘<directory>.init{SID}.ora’
Now you have a good understanding of architecture of an Oracle database, let us create one.
Next: Create a Database >>
More Oracle Articles
More By Ben Shepherd
/ 152 
