30 Jun 2016

Oracle Tablespaces and Datafiles

MongoDB 0 Comment

An Oracle database is comprised of tablespaces , it stores data logically in tablespaces and physically in datafiles associated with the corresponding tablespace.


Tablespaces logically organize data that are physically stored in datafiles.

  • A tablespace belongs to only one database, and has at least one datafile that is used to store data for the associated tablespace.
  • The term “tablespaces” is misleading because a tablespace can store tables, but can also store many other database objects such as indexes, views, sequences, etc.
  • Datafiles are always assigned to only one tablespace and, therefore, to only one database.

There are three types of tablespaces:

(1) permanent tablespace
(2) undo tablespace
(3) temporary tablespace

Permanent Tablespace:
A permanent tablespace stores regular schema objects, such as tables and indexes that are permanent.

Undo Tablespace:
Undo tablespaces are special tablespaces used solely for storing undo information. You cannot create any other segment types (for example, tables or indexes) in undo tablespaces. Undo tablespaces are used only when the database is in automatic undo management mode (the default). A database can contain more than one undo tablespace, but only one can be in use at any time. Undo data is managed within an undo tablespace using undo segments that are automatically created and maintained by the database.

Temporary Tablespace:
You can manage space for sort operations more efficiently by designating one or more temporary tablespaces exclusively for sorts. A single SQL operation can use more than one temporary tablespace for sorting. For example, you can create indexes on very large tables, and the sort operation during index creation can be distributed across multiple tablespaces.

The smallest database is needed minimum two tablespaces.

  • SYSTEM tablespace– stores the data dictionary.
  • SYSAUX tablespace– stores data for auxiliary applications

A typical production database has numerous tablespaces.

 SYSTEM Tablespace: – A tablespace that is always used to store SYSTEM data that includes data about tables, indexes, sequences, and other objects – this metadata comprises the data dictionary.
· Every Oracle database has to have a SYSTEM tablespace—it is the first tablespace created when a database is created.
· Accessing it requires a higher level of privilege.
· You cannot rename or drop a SYSTEM tablespace.
· You cannot take a SYSTEM tablespace offline.
· The SYSTEM tablespace could store user data, but this is not normally done—a good rule to follow is to never allow allow the storage of user segments in the SYSTEM tablespace.
· This tablespace always has a SYSTEM Undo segment.

SYSAUX Tablespace: The SYSAUX tablespace stores data for auxiliary applications such as the LogMiner, Workspace Manager, Oracle Data Mining, Oracle Streams, and many other Oracle tools.
· This tablespace is automatically created if you use the Database Creation Assistant software to build an Oracle database.
· Like the SYSTEM tablespace, SYSAUX requires a higher level of security and it cannot be dropped or renamed.
· Do not allow user objects to be stored in SYSAUX. This tablespace should only store system specific objects.
· This is a permanent tablespace.

All other tablespaces are referred to as Non-SYSTEM. A different tablespace is used to store organizational data in tables accessed by application programs, and still a different one for undo information storage, and so on. There are several reasons for having more than one tablespace:
· Flexibility in database administration.
· Separate data by backup requirements.
· Separate dynamic and static data to enable database tuning.
· Control space allocation for both applications and system users.
· Reduce contention for input/output path access (to/from memory/disk).

To create a tablespace you must have the CREATE TABLESPACE privilege.
Tablespaces can be either Locally Managed to Dictionary Managed.
When you create a tablespace, if you do not specify extent management, the default is locally managed.

Locally Managed:
The extents allocated to a locally managed tablespace are managed through the use of bitmaps.
· Each bit corresponds to a block or group of blocks (an extent).
· The bitmap value (on or off) corresponds to whether or not an extent is allocated or free for reuse.

Locally managed tablespaces
· Reduced contention on data dictonary tables
· No undo generated when space allocatoin or deallocation occures
· No coalescing required

CREATE TABLESPACE wavelabs DATAFILE '/u01/oradata/wavelabs01.dbf' SIZE 500M EXTENET MANAGEMENT LOCAL; 


Local management is the default for the SYSTEM tablespace beginning with Oracle 10g.

· When the SYSTEM tablespace is locally managed, the other tablespaces in the database must also be either locally managed or read-only.
· Local management reduces contention for the SYSTEM tablespace because space allocation and deallocation operations for other tablespaces do not need to use data dictionary tables.
· The LOCAL option is the default so it is normally not specified.

· With the LOCAL option, you cannot specify any DEFAULT STORAGE, MINIMUM EXTENT , or TEMPORARY clauses.

Dictionary Managed:
· Extents are managed in the data dictionary
· Each segment stored in the tablaspace can have a different storage cluase
· Coalescing required



UNDO Tablespace:
· The Undo tablespace is used for automatic undo management.
· Used to store undo segments
· Cannot contain any other objects
· Extents are locally managed
· It can only use DATAFILE and EXTENT MANAGEMENT clauses

CREATE UNDO TABLESPACE undo1 DATAFILE '/u01/oradata/undo1.dbf' SIZE 40M;

· More than one UNDO tablespace can exist, but only one can be active at a time.
· A later set of notes will cover UNDO management in detail.

TEMPORARY Tablespace:
· A TEMPORARY tablespace is used to manage space for sort operations. Sort operations generate segments, sometimes large segments or lots of them depending on the sort required to satisfy the

specification in a SELECT statement’s WHERE clause.
· Sort operations are also generated by SELECT statements that join rows from within tables and between tables.
· Note the use of the TEMPFILE instead of a DATAFILE specification for a temporary tablespace in the figure shown below.

Temporary Tablespaces
· Used for sort operations
· Cannot contain any permanent objects
· Locally managed extents recommended



Default Temporary Tablespace:
· Each database needs to have a specified default temporary tablespace. If one is not specified, then any user account created without specifying a TEMPORARY TABLESPACE clause is assigned a temporary tablespace in the SYSTEM tablespace!
· This should raise a red flag as you don’t want system users to execute SELECT commands that cause sort operations to take place within the SYSTEM tablespace.
· If a default temporary tablespace is not specified at the time a database is created, a DBA can create one by altering the database.


After this, new system user accounts are automatically allocated temp as their temporary tablespace.  If you ALTER DATABASE to assign a new default temporary tablespace, all system users are automatically reassigned to the new default tablespace for temporary operations.

USERS, DATA and INDEXES Tablespaces:
Most Oracle databases will have a USERS permanent tablespace.
· This tablespace is used to store objects created by individual users of the database.
· At SIUE we use the USERS tablespace as a storage location for tables, indexes, views, and other objects created by students.
· All students share the same USERS tablespace.
Many Oracle databases will have one or more DATA tablespaces.
· A DATA tablespace is also permanent and is used to store application data tables such as ORDER ENTRY or INVENTORY MANAGEMENT applications.
· For large applications, it is often a practice to create a special DATA tablespace to store data for the application. In this case the tablespace may be named whatever name is appropriate to describe the objects stored in the tablespace accurately.
Oracle databases having a DATA (or more than one DATA) tablespace will also have an accompanying INDEXES tablespace.
· The purpose of separating tables from their associated indexes is to improve I/O efficiency.
· The DATA and INDEXES tablespaces will typically be placed on different disk drives thereby providing an I/O path for each so that as tables are updated, the indexes can also be updated simultaneously.

Bigfile Tablespaces:
A Bigfile tablespace is best used with a server that uses a RAID storage device with disk stripping – a single datafile is allocated and it can be up to 8EB (exabytes, a million terabytes) in size with up to 4G blocks.
Normal tablespaces are referred to as Smallfile tablespaces.
Why are Bigfile tablespaces important?
· The maximum number of datafiles in an Oracle database is limited (usually to 64K files) – think big here—think about a database for the internal revenue service.
· Bigfile tablespaces can only be locally managed with automatic segment space management except for locally managed undo tablespaces, temporary tablespaces, and the SYSTEM tablespace.
· If a Bigfile tablespace is used for automatic undo or temporary segments, the segment space management must be set to MANUAL.
· Bigfile tablespaces save space in the SGA and control file because fewer datafiles need to be tracked.

CREATE BIGFILE TABLESPACE graph01 DATAFILE '/u01/oradata/USER350graph01.dbf' SIZE 10g;


Tablespace Sizing:
Normally over time tablespaces need to have additional space allocated. This can be accomplished by setting the AUTOEXTEND option to enable a tablespace to increase automatically in size.
· This can be accomplished when the tablespace is initially created or by using the ALTER TABLESPACE command at a later time.

 CREATE TABLESPACE application_data DATAFILE '/u01/oradata/USER350data01.dbf' SIZE 200M AUTOEXTEND ON NEXT 48K MAXSIZE 500M; 

This query uses the DBA_DATA_FILES view to determine if AUTOEXTEND is enabled for selected tablespaces in the SIUE DBORCL database.

 SELECT tablespace_name, autoextensible FROM dba_data_files;
TABLESPACE_NAME                AUT
------------------------------ ---
SYSTEM                         NO
SYSAUX                         NO
UNDOTBS1                       YES
USERS                          NO

· Manually use the ALTER DATABASE command to resize a datafile.


This command looks similar to the above command, but this one resizes a datafile while the above command sets the maxsize of the datafile.

 ALTER DATABASE DATAFILE '/u01/oradata/USER350data01.dbf' RESIZE 600M; 

· Add a new datafile to a tablespace with the ALTER TABLESPACE command.

 ALTER TABLESPACE application_data ADD DATAFILE '/u01/oradata/USER350data01.dbf' SIZE 200M;