Database Reference
In-Depth Information
A tablespace is logical in the sense that it is only visible through data dictionary views (such as
DBA_TABLESPACES
);
you manage tablespaces through SQL*Plus or graphical tools (such as Enterprise Manager), or both. Tablespaces only
exist while the database is up and running.
Data files can also be viewed through data dictionary views (such as
DBA_DATA_FILES
) but additionally have
a physical presence, as they can be viewed outside the database through OS utilities (such as
ls
). Data files persist
whether the database is open or closed.
Oracle databases typically contain several tablespaces. A tablespace can have one or more data files associated
with it, but a data file can be associated with only one tablespace. In other words, a data file can't be shared between
two (or more) tablespaces.
Objects (such as tables and indexes) are owned by users and created within tablespaces. An object is logically
instantiated as a segment. A segment consists of extents of space within the tablespace. An extent consists of a set of
database blocks. Figure
2-3
shows the relationships between these logical and physical constructs used to manage
space within an Oracle database.
Figure 2-3.
Relationships of logical storage objects and physical storage
When you create a database, typically five tablespaces are created when you execute the
CREATE DATABASE
statement:
SYSTEM
,
SYSAUX
,
UNDO
,
TEMP
, and
USERS
.
These five tablespaces are the minimal set of storage containers you need to operate a database (one could argue,
however, you don't need the
USERS
tablespace; more on that in the next section). As you open a database for use, you
should quickly create additional tablespaces for storing application data. This chapter discusses the purpose of the
standard set of tablespaces, the need for additional tablespaces, and how to manage these critical database storage
containers. The chapter focuses on the most common and critical tasks associated with creating and maintaining
tablespaces and data files, progressing to more advanced topics, such as moving and renaming data files.
Understanding the First Five
The
SYSTEM
tablespace provides storage for the Oracle data dictionary objects. This tablespace is where all objects
owned by the
SYS
user are stored. The
SYS
user should be the only user that owns objects created in the
SYSTEM
tablespace.
Starting with Oracle 10g, the
SYSAUX
(system auxiliary) tablespace is created when you create the database. This
is an auxiliary tablespace used as a data repository for Oracle database tools, such as Enterprise Manager, Statspack,
LogMiner, Logical Standby, and so on.
