Tablespace Maintenance Concepts

Tablespaces are used to contain all Oracle objects (tables, indexes, clusters, packages, procedures, functions, and so on). The Oracle tablespace is a logical storage area. Physical disk files (RAID, or even UNIX raw partitions) are added to the tablespace level. Tablespace size is limited only by the amount of available disk space. There are a couple of important tablespaces, namely SYSTEM where the Oracle data dictionary resides, and TEMPORARY which is typically used for sorts, merge/joins, and so on. DBAs and developers have a great deal of control over how the space within the tablespace is allocated and shared. Performance can be greatly enhanced using various tablespaces to control object locations on physical disk devices.

A new feature of Oracle8 is that of partitioning. Partitioning enhances Oracle’s support for very large databases as partitioning breaks tables and indexes into smaller, easier-to-manage pieces, storing these pieces at the tablespace level. Partitioned tables/indexes are nothing more than a logical collection of tablespaces (see Figure 19.4).

Figure 19.4.  Oracle tablespace layout example.

Partitioning greatly enhances database performance. This performance can be monitored and adjusted in a variety of manors. Read/write activity can be load-balanced among disk drives/disk arrays. The Oracle optimizer recognizes partitions and can select only from partitions that contain the data being selected. The Oracle Parallel Server can divide work based on the number of partitions.

Availability is higher, backups are easier, and recovery is shorter. High availability is the single goal of most computer systems. Recovery and time to recover are always an issue. Downtime for maintenance or backups is not possible with some applications. Machine failures are inevitable. Partitions can be backed up online, individually, and only the ones that have changes. This makes the backup/recovery process much quicker. If recovery is needed, the other partitions not affected are still available and online.

Administration and tuning is much easier. Oracle8 partitioning has many features that make the partitions easy to create, move, split, and so on. One can even swap in an existing non-partitioned table structure and data to an existing table partition. Tuning is enhanced with the additional indexing features available.

Oracle8 supports the read-only tablespace. Read-only tablespaces are more efficient than the traditional full access tablespace: No updates can occur so the read consistency and recovery mechanisms need not apply to this tablespace. This feature is also useful for tablespaces targeted for CD-ROM devices.

---

Tip:  
Prior to running the READ ONLY command, run the SQL statement select count(*) from ... against each table in the read-only tablespace. This will reset the check block level transaction status remaining from any recent updates and eliminate this bit of overhead from any future access to the data.

---

Importance of Organization

The Oracle tablespace implementation lends itself well to a variety of application needs. The tablespace can be used as a point of user/object organization; its flexibility lends itself well for SQL tuning through object distribution, it can be backed up and restored while still available to applications, and if damaged, it can remain unavailable while other tablespaces remain available.

The Oracle data dictionary resides in the SYSTEM tablespace. Because Oracle relies heavily on the data dictionary for every operation, it cannot be taken offline, or made unavailable for any reason. The ROLLBACK tablespace is typically used by the rollback segments (these segments ensure read consistency and recovery from uncommitted transactions) that dynamically grow and shrink based on usage and the demands of various applications. The TEMPORARY tablespace is where Oracle8 will do its sorting and merge/join operations (more than one table in the SQL statement from clause). No permanent objects can be created here. Additional TEMPORARY tablespaces can be added to the instance with the CREATE TABLESPACE <tablespace name> TEMPORARY command. This default activity can be altered with the DEFAULT TEMPORARY TABLESPACE assignment in the CREATE USER or ALTER USER SQL commands. It is important that any user-defined object (tables, indexes, clusters, and so on) not be created in any of these three tablespaces for obvious reasons. Similarly, the DEFAULT TABLESPACE is set by the CREATE USER or ALTER USER SQL commands to point at a tablespace for the user to store objects. Both the TABLESPACE and the TEMPORARY TABLESPACE defaults can be overridden by syntax in the CREATE SQL statement.

Tablespaces can be used to organize and separate various applications sharing an Oracle instance. Their names (and underlying data file names) can make the environment self-documenting. Additional tablespaces are easy to create with the CREATE TABLESPACE SQL statement. The user must have DBA authority or the CREATE TABLESPACE privilege. This statement creates the tablespace and adds the initial file or files. Default storage parameters can be entered at this time as well. These defaults are applied only when the storage parameters are not defined at the object creation time.