Segment Management in Oracle Database 10g

As a precursor to our discussion on segment management, this chapter starts with some preliminary information about data-storage principles in Oracle. Oracle databases use data files to hold data in the database. This data is written to the data files in a proprietary format read only by the database server. Oracle also uses temp files (a form of data files) exclusively with temporary tablespaces.

Data files are made up of segments and extents. There are different types of segments, specific to the database objects they holdfor example, data segments for tables and index segments for indexes. An extent is a contiguous set of data blocks within a segment, whose size is determined by the DBA either during or after creation of database objects. A data block (or database block) is the lowest unit for data transfer to database storage. The default database block size is determined at database creation by the DBA or chosen by default. The data block size cannot be altered after the database is created. But Oracle Database 10g supports the use of multiple data block sizes for tablespaces other than the default block size, as discussed in Chapter 1, "Exploring Oracle Database 10g Architecture," in the section "BigFile Tablespace."

Data, Index, and Temporary Segments

A single data segment in an Oracle database holds all the data for a table (clustered or not partitioned), a partition in a partitioned table, and a cluster of tables. The storage parameters determine how the extents for the data segment are allotted. Oracle also uses segments for materialized views and materialized view logs like tables and clusters. In a table or materialized view, LOB and varray column types are stored in LOB segments.

Oracle uses a single index segment for every nonpartitioned index and a single index segment per partition for partitioned indexes. The storage parameters are determined for index segments in a manner that is similar to table-creation methods.

As a best practice, you should not share the same tablespace for database application tables and indexes for any of your critical databases.

Oracle uses a temporary workspace for intermediary stages of processing with SQL statement parsing and SQL query execution. The disk space for this purpose is called a temporary segment. If Oracle can do this sorting or processing in memory, or using indexes, it will not create a temporary segment. Oracle allocates temporary segments for temporary tables and indexes as well.

Examples of statements that utilize a temporary segment are CREATE INDEX, certain SELECT operations (SELECT ..ORDER BY, SELECT ..UNION, SELECT DISTINCT.., SELECT ..GROUP BY, and so on). Oracle may require more than one temporary segment if the query contains un-indexed joins and related subqueries.

Oracle uses temporary segments for queries differently than for temporary tables. In a user session, Oracle uses temporary segments in the temporary tablespace of the user executing the statement. If the user does not have a temporary tablespace allotted, the default temporary tablespace will be the SYSTEM tablespace. Hence, it is very important that the DBA create a temporary tablespace during database creation itself to prevent using the SYSTEM tablespace. If the SYSTEM tablespace is locally managed, it cannot be used for default temporary storage. The data used for sorting purposes within temporary segments are not stored in the redo log.

Do Temporary Segments Always Reside in Temporary Tablespaces?

Temporary segments need not always be created in temporary tablespaces. For example, when you do a direct path load, index rebuild, or table move, temporary segments are initially created in the default tablespace or target tablespace. After the operation is complete, the temporary segment will be renamed as a permanent segment.

When a temporary table is used for transaction processing, the segments are dropped by Oracle at the end of the transaction. Similarly, with a session-specific temporary table, Oracle drops the segments at the end of the session. If more than one transaction or session shares the same temporary table, Oracle will retain the segments containing their data in the table until operations are completed.