What is tablespace

In summary, if Oracle determines that it has enough information in the online tablespaces to execute a statement, it will do so. If it needs data in an offline tablespace, then it causes the statement to fail.

Read-Only Tablespaces The primary purpose of read-only tablespaces is to eliminate the need to perform backup and recovery of large, static portions of a database.

Note : Because you can only bring a tablespace online in the database in which it was created, read-only tablespaces are not meant to satisfy archiving or data publishing requirements. Whenever you create a new tablespace, it is always created as read-write.

Read-only tablespaces cannot be modified. Therefore, they do not need repeated backup. Also, should you need to recover your database, you do not need to recover any read-only tablespaces, because they could not have been modified. You can drop items, such as tables and indexes, from a read-only tablespace, just as you can drop items from an offline tablespace. However, you cannot create or alter objects in a read-only tablespace.

Read-Only vs. Online or Offline Making a tablespace read-only does not change its offline or online status. Offline datafiles cannot be accessed. Bringing a datafile in a read-only tablespace online makes the file readable. The file cannot be written to unless its associated tablespace is returned to the read-write state. Restrictions on Read-Only Tablespaces You cannot add datafiles to a tablespace that is read-only, even if you take the tablespace offline. When you add a datafile, Oracle must update the file header, and this write operation is not allowed.

To update a read-only tablespace, you must first make the tablespace writeable. After updating the tablespace, you can then reset it to be read-only.

Read-Only Tablespaces and Recovery Read-only tablespaces have several implications upon instance or media recovery.

See Chapter 24 , "Database Recovery", for more information about recovery. Temporary Tablespaces Space management for sort operations is performed more efficiently using temporary tablespaces designated exclusively for sorts.

This scheme effectively eliminates serialization of space management operations involved in the allocation and deallocation of sort space.

The performance gains are significant in parallel server environments. A temporary tablespace is a tablespace that can only be used for sort segments. No permanent objects can reside in a temporary tablespace.

Sort segments are used when a segment is shared by multiple sort operations. One sort segment exists in every instance that performs a sort operation in a given tablespace. Temporary tablespaces provide performance improvements when you have multiple sorts that are too large to fit into memory. The sort segment of a given temporary tablespace is created at the time of the first sort operation.

The sort segment grows by allocating extents until the segment size is equal to or greater than the total storage demands of all of the active sorts running on that instance. Datafiles A tablespace in an Oracle database consists of one or more physical datafiles. If a tablespace is offline, you cannot access data stored in it. On the other hand, if a tablespace is online, its data is available for reading and writing. Normally, a tablespace is online so that its data is available to users.

However, you can take a tablespace offline to make data inaccessible to users when you update and maintain the applications. In case of some errors such as hardware failures, Oracle automatically takes an online tablespace offline. The datafiles are created automatically when the tablespace is defined. In most cases, all datafile space is pre-allocated; that is, the space is set aside when the datafile is created.

Thus, when you create a tablespace, you define the initial size of the associated datafile. We will discuss the specifics of creating tablespaces later in this book. Tablespaces are given names as they are created. Tablespaces are generally named based on the objects within the tablespace. We will be discussing tablespaces in great detail in later chapters in this book. As we discussed earlier, a block is the smallest unit of storage in Oracle. The size of a database block is fixed when the database is created, and can not be changed except by rebuilding the database from scratch.

Once the base block size is defined, you can create new tablespace with alternate block sizes. Remember that the Oracle instance also includes a RAM buffer cache which is made-up of RAM blocks which map to the data block in the physical datafiles. An extent is an uninterrupted or contiguous allocation of blocks within a segment. Extents are assigned to a segment automatically by Oracle, so you will rarely deal directly with an extent, rather you will deal directly with its associated segment.

An extent must be on contiguous blocks within a single datafile, so an extent cannot span multiple Oracle datafiles. Oracle will allocate the size of the extents based on the type of tablespace. We will discuss more about extent allocation in later chapters when we discuss table and index creation. Segments are the storage objects within the Oracle database.