Skip navigation.

RDBMS Server

Oracle RDBMS Server Articles

TEMPORARY Tablespaces and TEMPFILES

What are Temporary Tablespaces:

Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables. For example, if you join two large tables, and Oracle cannot do the sort in memory (see SORT_AREA_SIZE initialisation parameter), space will be allocated in a temporary tablespace for doing the sort operation. Other SQL operations that might require disk sorting are: CREATE INDEX, ANALYZE, Select DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS, Sort-Merge joins, etc.

The DBA should assign a temporary tablespace to each user in the database to prevent them from allocating sort space in the SYSTEM tablespace. This can be done with one of the following commands:

Automatic Undo Management (AUM)

In Oracle 8i and below, Rollback Segments provide read consistency and the ability to rollback transactions. In Oracle 9i, Undo segments can be used to provide this functionality. The advantage of using Automatic Undo Management is that it relieves the DBA of manually creating, sizing and monitoring the rollback segments in the database.

Difference between DBFile Sequential and Scattered Reads

Both "db file sequential read" and "db file scattered read" events signify time waited for I/O read requests to complete. Time is reported in 100's of a second for Oracle 8i releases and below, and 1000's of a second for Oracle 9i and above. Most people confuse these events with each other as they think of how data is read from disk. Instead they should think of how data is read into the SGA buffer cache or user PGA memory.

db file sequential read:

A sequential read operation reads data into contiguous memory (usually a single-block read with p3=1, but can be multiple blocks). Single block I/Os are usually the result of using indexes. This event is also used for rebuilding the controlfile and reading datafile headers (P2=1). In general, this event is indicative of disk contention on index reads.

Oracle Listener Protection

Database listeners can be stopped remotely from any operating system account (not just oracle). To prevent this, all listeners on production machines MUST be password protected.

Oracle Default Listener

by Nidhi Jain

Prior to Oracle 8i, a listener was statically configured (listener.ora) to service a given set of SIDs. From 8i, PMON dynamically registers a database service with the listener.