Logical and Physical Storage in Oracle

Following my earlier post "RDBMS Software, Database and Instance", here's a quick note on Logical and Physical Storage in Oracle :

Data (whether tables or indexes) is stored in an Oracle database at "two" levels --
the Logical and the Physical.

The Logical level is of Tablespaces, Segments and Extents.
The Physical level is of Datafiles and Blocks.

When you create a Tablespace, you specify the DataFiles that the Tablespace will consist
of and the initial sizes of the DataFiles and whether Oracle may "autoextend" the DataFiles
on the same filesystems.
{When using the OMF (Oracle Managed Files) feature, datafile names and sizes and autoextend
are “automatically” set by Oracle but, nevertheless, do exist.}  
A Tablespace consists of 1 or more DataFiles which can be on the same filesystem or on
different filesystems (i.e., mountpoints).

When you create a Table, you specify the Tablespace name and Extent allocation parameters 
{Segment space management is isn't really about allocation of storage but about freelist
(blocks availabe for INSERTs) management}.
Thus the CREATE TABLE syntax identifies the Tablespace in which the Table's Segment
will be created and the sizes of the initial and next extents that will be allocated. 
{If the Tablespace is created with Allocation Type MANUAL or AUTO, then the Table’s Extent
sizes are determined by the Allocation Type}.

Oracle then allocates the Extent(s) as groups of DatabaseBlocks in the available DataFiles
"belonging" to the Tablespace which that Segment exists in.

A Segment  *cannot* span Tablespaces, it can fit into only 1 Tablespace.  However, it can
have extents in different DataFiles of the Tablespace.  Therefore, a Table (or Index) can
have Extents in different DataFiles, thus spreading the I/O and "hot spots".  Similarly,
different Tables in a Tablespace may go into different DataFiles.  However, it is difficult
for the DBA to pre-allocate specific DataFiles and Extents (other than using MINEXTENTS
during the CREATE or using ALLOCATE EXTENT with an ALTER command).

Note : When a Table (or Index) is Partitioned, each Partition is a separate Segment. 
Therefore, Oracle allows you to place each Partition in a separate Tablespace because
each is a separate Segment.

An Extent cannot span DataFiles, the first and last block of the Extent are "contiguous"
within a DataFile. 
{Oracle may "autoextend" a DataFile to increase it's size so as to allocate an extent in
the DataFile when necessary}.

When Row Chaining (a Row being larger than the Oracle BlockSize for DatabaseBlocks is
written across multiple DatabaseBlocks)  or  Row Migration (a Row is moved from one
DatabaseBlock to another because, on expansion of the RowLength by an Update, it cannot
fit into the original DatabaseBlock any longer), the Row is allocated space in another
block --whether in the same Extent or a different Extent -- in the same Segment.

Database (or Tablespace or DataFile) Backup using RMAN (or OS utilities like "cp" or
"tar" or "cpio"  or NetBackup commands etc) is always a Physical Backup.  The Backup
actually copies each DatabaseBlock without regard to the contents of the DatabaseBlock.
Therefore, it also follows that Recovery has to be Physical -- a RollForward using the
Archived RedoLogs (aka ArchiveLogs) has to "re-apply" changes to each DatabaseBlock. 
This is possible because each DatabaseBlock header includes the "SCN"  to identify the
point in time of the last update to the DatabaseBlock and the RedoStream in the RedoLogs
consists of Change Vectors which identify the DatabaseBlocks being impacted (whether by
Insert, Update or Delete) by execution of a DML.  Therefore, during the RollFoward,
Oracle merely identifies the DatabaseBlocks from the RedoStream and "reapplies" the
changes to the Blocks.  All this is at the "Physical" level.

Export and Import based "backup" and "recovery" mechanisms are Logical because they
operate at the individual Table level (or even against specific Rows, if the Export is
done with a Query).