Oracle Storage Primer

Basic Elements of Storage

Tablespaces

A tablespace is a logical volume of data that can contain objects.   Database Objects are things like tables, indexes and stored procedures.

Data Files

A tablespace is constructed of 1 or more data files.   The data file (.DBF file) automatically grows in size as more objects are added to the tablespace.  

Data files will grow as large as the operating system allows.   If they reach the maximum size that the operating system allows; another .DBF file will need to be created and added to the tablespace.

Oracle Storage mechanisms

The following descriptions of Oracle Storage mechanisms are very high level.   Oracle's entire documentation library is available online in HTML and PDF format.   The Oracle Library can be found here:

Oracle 11g R2 Online Documentation Library

Oracle 11g R1 Online Documentation Library

Manual

In this mode; when tablespaces are created; data files will need to be manually added and sized by the system administrator.  

You can tell if a server is setup in manual tablespace management; if you go into the Doc Mgt.Tablespaces screen within Genesis; and see that the data files have explicit filenames such as "d:\genesis\database\district_data.dbf" or "/u02/oradata/district_data.dbf".

Oracle Managed Files (OMF)

Oracle OMF has a configuration option that points to a single directory (d:\oradata for example).  

When a tablespace is created; or when additional space is needed in a data file within a tablespace; Oracle will automatically name and create the files as needed; and store them in this directory.   This setting is typical on most Windows servers.

You can tell if a server is setup in Oracle Managed Files mode in 2 ways.  

Method 1:  If you go into the Doc Mgt.Tablespaces screen within Genesis; and see that the data files have seemingly random filenames such as "/u02/oradata/GENESIS/datafile/o1_mf_system_4vnmhfpk_.db"

Method 2:  Run this SQL statement as a sys or system user: select value from v$parameter where name = 'db_create_file_dest'

If it returns a path name (/u02/oradata); then OMF is in use. If it returns an ASM Disk Set (+DATA), then ASM is in use.

Automatic Storage Management

This feature builds on Oracle Managed Files controlling sets of entire disk drives instead of a single directory.   Automatic Storage Management is the most difficult of the three options to install, but delivers the best performance and manageability.  

You can tell if a server is setup in Automatic Storage Management mode in 2 ways.  

Method 1:  If you go into the Doc Mgt.Tablespaces screen within Genesis; and see that the data files start with +DATA and have seemingly random filenames such as "+DATA/genesis/datafile/district_docs.520.709659619".

Method 2:  Run this SQL statement as a sys or system user: select value from v$parameter where name = 'db_create_file_dest'

If it returns a path name starting with +DATA (+DATA/genesis/datafile); then ASM is in use.