Logical Structure of the Database

As a DBA, you can create new tablespaces, resize data files associated to the tablespaces, add files to tablespaces, set default segment storage settings, alter default segment storage settings, make tablespaces read-only or read-write, make tablespaces temporary or permanent, and drop tablespaces.

Recall that the database architecture includes both the logical structure (tablespaces, segments, extents, and data blocks) and the physical structure (control files, online redo log files, and data files).

The Oracle Server enables the DBA to maintain fine-grained control of disk space through the use of the logical structures.

The logical structures tablespaces, extents, and segments are the means by which the DBA manipulates the data file physical structure.

Figure 10.1 shows the relationship between the different logical and physical storage structures in the Oracle database and instance. Note that there is a one-to-many relationship between the database and tablespaces, between tablespaces and extents, between extents and Oracle blocks, between tablespaces and data files, between Oracle blocks and OS blocks, and between data files and OS blocks. These relationships can extend even further because the data files can be spread across different file systems, disks, and disk groups.

Figure 10.1. Logical and physical configuration of the database.

Shown in Figure 10.1, although not covered in this chapter, are the control files and the online redo log files in the physical part of the database.

Redo log files are covered in Chapter 9, "Maintaining Redo Log Files," and control files are discussed in Chapter 8, "Maintaining Control Files."

The following list provides some of the outstanding characteristics of tablespaces:

• The data in an Oracle database is stored in a tablespace. The database has to exist before any tablespaces other than the SYSTEM tablespace (which is created concurrently with the creation of the database) can be created.

• Tablespaces are logically grouped areas of space that allow you to store and regulate the data within the database at its most coarse, granular level.

• Tablespaces can belong to exactly one database at a time, and each tablespace can consist of one or more operating system files, which are called from the Oracle perspective data files.

• Any given tablespace may contain zero, one, or more than one segment.

• Tablespaces can be brought online only when the database is running.

• With the notable exceptions of the SYSTEM tablespace, temporary tablespaces, and an undo tablespace with active undo segments, tablespaces can be taken offline while the database is running without causing harm to the database.

• Tablespaces can be switched between read-only and read-write status whenever necessary without deleterious effect to the database to which they are associated.

The following list provides some of the outstanding characteristics of data files:

• Tablespaces consist of one or more data files.

• Data files are physical constructs that conform to the operating system requirements on which the Oracle Server is running.

• A data file can belong to exactly one tablespace, although more than one data file can belong to the same tablespace.

• Oracle creates the data file on the operating system by allocating the specified amount of disk space to the data file plus a small amount of overhead that only Oracle can use.

• The database administrator is the only one who can change the size of a data file after its creation unless it has been specified (through the AUTOEXTEND clause) that the data file can grow dynamically until it fills the entire file system.

The following list provides some of the outstanding characteristics of segments:

• A segment is the space allocated for a specific logical storage structure within a tablespace. All the storage allocated to a table is in that table's segment. All the storage allocated to an index is in that index's segment.

• A tablespace may contain zero, one, or more than one segment.

• A segment cannot span multiple tablespaces.

• A segment can span multiple data files as long as those data files belong to the same tablespace.

• Each segment is made up of one or more than one extent.

The following list provides some of the outstanding characteristics of extents:

• Space is allocated to segments by extents.

• One or more extents make up a segment.

• When a segment is created it must have at least one extent.

• As a segment grows, extents are added to the segment.

• You, as a DBA, can add extents to segments.

• Extents are a set of contiguous Oracle blocks.

• An extent cannot span segments, tablespaces, or data files.

The following list provides some of the outstanding characteristics of data blocks:

• Oracle manages storage space, at the lowest granularity, in units called Oracle blocks or data blocks.

• At the finest level of granularity, all data is stored in an Oracle database in data blocks.

• Data blocks are the smallest unit of storage that Oracle can allocate, read from, or write to.

• One data block corresponds to one or more than one operating system blocks that are allocated from exactly one existing data file.

• The standard block size for an Oracle database is specified by the DB_BLOCK_SIZE initialization parameter and is set when the database is created. Because in Oracle 9i this is only the default block size. Tablespaces of nonstandard block sizes can be created using the CREATE TABLESPACE statement and at the same time specifying the BLOCKSIZE clause. These nonstandard block sizes can be given any of the following vales: 2KB, 4KB, 8KB, 16KB, or 32KB. However, to be able to use nonstandard block sizes in tablespaces, you must first configure a subcache within the buffer cache area of the SGA for each of the nonstandard block sizes that you intend to use. These subcaches are configured in the PFILE or the SPFILE and are as follows:

  • DB_2K_CACHE_SIZE

  • DB_4K_CACHE_SIZE

  • DB_8K_CACHE_SIZE

  • DB_16K_CACHE_SIZE

  • DB_32K_CACHE_SIZE

• Data block size should be a multiple of the operating system's block size to avoid unnecessary I/O operations.

• Maximum data block sizes are operating system dependent.

Now that we have looked at the logical structure of the database, including tablespaces, in the next section we will look at how to manage the status of tablespaces.

Managing Tablespace Status

Tablespaces can have several different statuses. In the following sections, we will discuss online status (also considered to be read/write), offline status, and read-only status. These statuses are used for different reasons and can be used on the same tablespace at different times.

Offline or Online

Tablespaces typically need to be online so that the data in the tablespaces is available. There are often reasons that you want to take a tablespace offline. When a tablespace has an offline status, it is not available for data access. No SQL statements can reference objects contained in the tablespace, and anyone trying to access any objects in the offline tablespace receives an error. Several tablespaces can never have an offline status: SYSTEM tablespace, default temporary tablespace, and an undo tablespace with active undo segments.

You might want to take a tablespace offline to make a portion of the database unavailable while allowing normal access to the remainder of the database. You might do this to take an offline backup, to recover a tablespace or one or more of its data files while the database remains open and operable, or to move a data file while allowing the rest of the database to be open.

The following command takes the tablespace mydata offline:

 ALTER TABLESPACE mydata OFFLINE; 

You can specify several different parameters for the offline statement. Those parameters, and their meanings, can be found in the following list:

• NORMAL This is the default and need not be specified. Flushes all the blocks in all the data files in the tablespace out of the SGA. If you offline a tablespace NORMAL, you do not need to perform media recovery when you bring it back online. This is the preferred means of offlining a tablespace if at all possible.

• TEMPORARY Performs a checkpoint for all the online data files in this given tablespace only. Offline files may require media recovery.

• IMMEDIATE Does not ensure that any of the data files associated with the tablespace are available, and Oracle does not perform a checkpoint. You have to perform media recovery on the tablespace before you can bring it back online.

• FOR RECOVER Allows the tablespace to be taken offline to enable tablespace point-in-time recovery.

When the tablespace is taken offline, all of that tablespace's associated data files are also taken offline. To bring the tablespace and its data files back online, you would issue the following command:

 ALTER TABLESPACE mydata ONLINE; 

Whenever a tablespace goes offline or comes back online, an event is recorded in the data dictionary so that Oracle can remember that the tablespace is offline. An event is also recorded in the control file.

If you shut down the database with a tablespace in offline state, it will remain offline, and when the database restarts (in the mount and open portions of the open), the offline tablespace is not checked.

Read-Only

Not only can a tablespace be online or offline, it can also be made to be read-only. Read-only tablespaces are made available to users with only read operations possible, although objects can be dropped from read-only tablespaces. Dropped objects are possible because the data dictionary is the only thing updated in the drop. For locally managed tablespaces, the segments connected to the dropped objects are made into temporary segments to prevent the bitmap from being updated.

When the tablespace is made read-only, it is first placed into a transitionally read-only state. During this period, no more write operations are permitted, but any writes that had taken place can either be committed or rolled back. After the active transactions are committed or are rolled back, the tablespace is fully placed in read-only mode.

Even more interesting, you can create a read-only tablespace on a drive with removable media.

After you have altered the tablespace to read-only with the following command, you can move the data file to a Write Once Read Many (WORM) device (such as a CD or DVD):

 ALTER TABLESPACE mydata READ ONLY; Cp d:\mydatabase\mydb1\mydata01.dbf e:\dvddrive\mydata01.dbf ALTER TABLESPACE mydata RENAME DATAFILE 'e:\dvddrive\mydata01.dbf\; 

You can bring tablespaces that are read-only back into read-write status by means of another ALTER statement:

 ALTER TABLESPACE mydata READ WRITE; 

All data files connected to the read-only tablespace that you want to make read-write have to be online and available.

If you have moved the file from its original location to the WORM device, you will have to copy it off the WORM device to make the tablespace read-write.

You will have to first copy the data file back, then alter the database to look at the writable media's data file, and then make the tablespace read-write.

You now know the basic logical structure of a tablespace. The next section looks at the different kinds of tablespaces that follow those logical constructs.