The database has logical structures and physical structures.
Databases,
Tablespaces, and Data Files
A database is divided into logical storage units called tablespaces, which can be used to group related logical structures together. Each database is logically divided into one or more tablespaces. One or more data files are explicitly created for each tablespace to physically store the data of all logical structures in a tablespace.
Note: You can also create bigfile tablespaces. These tablespaces can have only a single file, which is often very large. The file may be any size up to maximum that the row ID architecture will permit. The maximum size is the block size for the tablespace times 2 to the 36th power, or 128 TB for a 32 KB block size. The traditional smallfile tablespaces (which are the default) usually contain multiple data files, but the files cannot be as large.
Read more about tablespace and datafile
Schemas
A schema is
a collection of database objects that are owned by a database user. Schema
objects are the logical structures that directly refer to the database’s data.
Schema objects include such structures as tables, views, sequences, stored
procedures, synonyms, indexes, clusters, and database links. In general, schema
objects include everything that your application creates in the database.
Data
Blocks
At the
finest level of granularity, an Oracle database’s data is stored in data
blocks. One data block corresponds to a specific number of bytes of physical
database space on the disk. A database uses and allocates free database space
in Oracle data blocks.
Extents
The next
level of logical database space is called an extent. An extent is a specific
number of contiguous data blocks (obtained in a single allocation) that are
used to store a specific type of information.
Segments
The level
of logical database storage above an extent is called a segment. A segment is a
set of extents allocated for a certain logical structure. For example, the
different types of segments include:
•
Data
segments: Each nonclustered,
non-index-organized table has a data segment with the exception of external
tables, global temporary tables, and partitioned tables where each table has
one or more segments. All of the table’s data is stored in the extents of its data
segment. For a partitioned table, each partition has a data segment. Each
cluster has a data segment. The data of every table in the cluster is stored in
the cluster’s data segment.
•
Index
segments: Each index has an index segment that
stores all of its data. For a partitioned index, each partition has an index
segment.
•
Undo
segments: One UNDO tablespace is created per
database instance that contains numerous undo segments to temporarily store undo
information. The information in an undo segment is used to generate
read-consistent database information and, during database recovery, to roll
back uncommitted transactions for users.
•
Temporary
segments: Temporary
segments are created by the Oracle database when a SQL statement needs a
temporary work area to complete execution. When the statement finishes
execution, the temporary segment’s extents are returned to the instance for
future use. Specify a default temporary tablespace for every user or a default
temporary tablespace, which is used databasewide.
The Oracle
database dynamically allocates space. When the existing extents of a segment
are full, additional extents are added. Because extents are allocated as
needed, the extents of a segment may or may not be contiguous on the disk.
SYSTEM
and SYSAUX Tablespaces
Each Oracle
database must contain a SYSTEM tablespace and a SYSAUX tablespace. They are
automatically created when the database is created. The system default is to
create a smallfile tablespace. You can also create bigfile tablespaces, which
enable the Oracle database to manage ultralarge files (up to 8 exabytes in
size).
A
tablespace can be online (accessible) or offline (not accessible). The SYSTEM
tablespace is always online when the database is open. It stores tables that
support the core functionality of the database, such as the data
dictionary
tables.
The SYSAUX
tablespace is an auxiliary tablespace to the SYSTEM tablespace. The SYSAUX
tablespace stores many database components, and it must be online for the
correct functioning of all database components.
Note: The SYSAUX tablespace may be taken
offline to do tablespace recovery, whereas this is not possible for SYSTEM
tablespace. Neither of them may be made read-only.
Segments,
Extents, and Blocks
Database
objects, such as tables and indexes, are stored as segments in tablespaces.
Each segment contains one or more extents. An extent consists of contiguous
data blocks, which means that each extent can exist only in one data file. Data
blocks are the smallest unit of I/O in the database.
When the
database requests a set of data blocks from the operating system (OS), the OS
maps this to an actual file system or disk block on the storage device. Because
of this, you need not know the physical address of any of the data in your
database. This also means that a data file can be striped or mirrored on
several disks.
The size of
the data block is defined by the DB_BLOCK_SIZE parameter. The default size of 8
KB is adequate for most databases. If your database supports a data warehouse
application that has large tables and indexes, then a larger block size may be
beneficial.
If your
database supports a transactional application where reads and writes are
random, then specifying a smaller block size may be beneficial. The maximum
block size depends on your OS.
