|
Step 6 - Tablespaces
|
Learn Oracle - Tablespaces
A database consists of one or more tablespaces. A tablespace is a logical structure, or
container, created by and known only to the Oracle database server in which database
objects are stored. A tablespace consists of one or more datafiles or tempfiles.
There are various types of tablespaces, including undo tablespaces, temporary
tablespaces, and permanent tablespaces.
A database running in automatic undo management mode transparently creates and
manages undo segments. Oracle DBA can create an undo tablespace and determine the
maximum retention time for undo data kept in that tablespace.
Temporary tablespaces improve the concurrence of multiple sort operations, reduce
their overhead, or avoid Oracle space management operations altogether. Temporary
tablespaces are the most efficient tablespaces for disk sorts. Space management (extent
allocation and deallocation) is locally managed.
After temporary tablespaces, tablespaces of type TEMPORARY are the next best
tablespaces to use for sort operations. Space management is dictionary managed.
Permanent tablespaces (which are not of type TEMPORARY) are least efficient for
performance of disk sorts.
Temporary and permanent tablespaces can be assigned at the user level. Oracle DBA can
create a default temporary tablespace at database creation time. This tablespace is used
as the default temporary tablespace for users who are not otherwise assigned a
temporary tablespace.
Tablespaces Page
Click Tablespaces. The Tablespaces page provides high level information about
tablespace structure and space usage. Oracle DBA can see how much space is allocated for the
tablespace and what portion of it is used. Select a tablespace and click View to drill
down to see the underlying datafile attributes.
Some Tablespaces in the Database
Tablespace Description
SYSAUX
This is an auxiliary tablespace to the SYSTEM tablespace.
Some components and products that prior to Oracle Database 10g
used the SYSTEM tablespace or their own tablespaces now use the
SYSAUX tablespace. This reduces the load on the SYSTEM
tablespace and reduces maintenance because there are fewer
tablespaces to monitor and maintain. Every Oracle Database 10g
or higher level database must have a SYSAUX tablespace.
Components that use this as their default tablespace during
installation include Automatic Workload Repository, Oracle
Streams, Oracle Text, and Enterprise Manager Repository. For
more information, see Oracle Database Administrator's Guide
SYSTEM
The SYSTEM tablespace is always created at database creation.
Oracle uses it to manage the database. It contains the data
dictionary, which is the central set of tables and views used as a
read-only reference describing a particular database. It also
contains various tables and views that contain administrative
information about the database. These are all contained in the SYS
schema, and can only be accessed by user SYS, or other
administrative users with the required privilege.
TEMP
This tablespace stores temporary data generated when processing
SQL statements. It would, for example, be used for sort work
space. Every database should have a temporary tablespace that is
assigned to users as their temporary tablespace. In the
preconfigured database, the TEMP tablespace is specified as the
default temporary tablespace. This means that if no temporary
tablespace is specified when the user account is created, then
Oracle assigns this tablespace to the user.
UNDOTBS1
This is the undo tablespace used by the database server to store
undo information. See "Managing Undo for Your Database" to
understand Oracle’s use of the undo tablespace. Every database
must have an undo tablespace that can be created at database
creation.
EXAMPLE This tablespace contains the sample schemas that Oracle includes
with the database. The sample schemas provide a common
platform for examples. Oracle documentation and educational
materials contain examples based on the sample schemas.
Temporary Tablespace Groups
Temporary tablespace groups lets users consume temporary space from multiple
tablespaces. Using a tablespace group, rather than a single temporary tablespace, can
alleviate problems caused when one tablespace is inadequate to hold the results of a
sort, particularly on a table that has many partitions. A tablespace group enables
parallel execution servers in a single parallel operation to use multiple temporary
tablespaces.
A tablespace group name can appear where a tablespace name would appear when
assigning a default temporary tablespace for the database or assigning a temporary
tablespace for a user.
Click the Temporary Tablespace Groups link. Oracle DBA see the temporary tablespace
groups that are defined for your database. Initially, there are none.
|
|
|
