Database Reference
In-Depth Information
The
UNDO
tablespace stores the information required to undo the effects of a transaction (
insert
,
update
,
delete
, or
merge
). This information is required in the event a transaction is purposely rolled back (via a
ROLLBACK
statement). The undo information is also used by Oracle to recover from unexpected instance crashes and to provide
read consistency for SQL statements. Additionally, some database features, such as Flashback Query, use the undo
information.
Some Oracle SQL statements require a sort area, either in memory or on disk. For example, the results of a query
may need to be sorted before being returned to the user. Oracle first uses memory to sort the query results, and when
there is no longer sufficient memory, the
TEMP
tablespace is used as a sorting area on disk. Extra temporary storage
may also be required when creating or rebuilding indexes. When you create a database, typically you create the
TEMP
tablespace and specify it to be the default temporary tablespace for any users you create.
The
USERS
tablespace is not absolutely required but is often used as a default permanent tablespace for table
and index data for users. This means that when a user attempts to create a table or index, if no tablespace is specified
during object creation, by default the object is created in the default permanent tablespace.
Understanding the Need for More
Although you could put every database user's data in the
USERS
tablespace, this usually isn't scalable or maintainable
for any type of serious database application. Instead, it's more efficient to create additional tablespaces for application
users. You typically create at least two tablespaces specific to each application using the database: one for the
application table data and one for the application index data. For example, for the
APP
user, you can create tablespaces
named
APP_DATA
and
APP_INDEX
for table and index data, respectively.
DBAs used to separate table and index data for performance reasons. The thinking was that separating table
data from index data would reduce input/output (I/O) contention. This is because the data files (for each tablespace)
could be placed on different disks, with separate controllers.
With modern storage configurations, which have multiple layers of abstraction between the application and the
underlying physical storage devices, it's debatable whether you can realize any performance gains by creating multiple
separate tablespaces. But, there still are valid reasons for creating multiple tablespaces for table and index data:
•
Backup and recovery requirements may be different for the tables and indexes.
•
The indexes may have storage requirements different from those of the table data.
•
You may be using BLOB and CLOB data types, which typically have considerably different
sizing requirements than non-LOB data. Therefore DBAs tend to separate LOB data in its own
tablespace(s).
Depending on your requirements, you should consider creating separate tablespaces for each application using
the database. For example, for an inventory application, create
INV_DATA
and
INV_INDEX
; for a human resources
application, create
HR_DATA
and
HR_INDEX
. Here are some reasons to consider creating separate tablespaces for each
application using the database:
•
Applications may have different availability requirements. Separate tablespaces lets you take
tablespaces offline for one application without affecting another application.
•
Applications may have different backup and recovery requirements. Separate tablespaces lets
tablespaces be backed up and recovered independently.
•
Applications may have different storage requirements. Separate tablespaces allows for
different settings for space quotas, extent sizes, and segment management.
•
You may have some data that is purely read-only. Separate tablespaces lets you put a
tablespace that contains only read-only data into read-only mode.
The next section discusses creating tablespaces.
