Oracle Database Admin (DBA) Tutorial: Tablespaces and Datafiles

1.   Table spaces

•       A tablespace is a logical storage unit within an Oracle database. It is logical because a table space is not visible in the file system of the machine on which the database resides. A table space, in turn, consists of at least one data file which, in turn, are physically located in the file system of the server. Btw, a datafile belongs to exactly one tablespace.

•       Each table, index and so on that is stored in an Oracle database belongs to a table space. The table space builds the bridge between the Oracle database and the file system in which the table's or index' data is stored.

There are three types of table spaces in Oracle:

•       Permanent table spaces

•       Undo table spaces

•       temporary table spaces

2.   Database Hierarchy

1.       Database Architecture

Physical: control, data, redo log, etc. files

Logical: tablespaces, segments, etc.

2.       Tablespaces

Place to store tables

Belongs to one db at a time

One or more OS files

One or more segments

Can be brought online “live”

Can be taken offline “live”

SYSTEM tablespace is important

3.       Datafile

Belong to one tablespace

DBA can change size

4.       Segments

One or more in a tablespace

Made up of one or more extents

Cannot span tablespaces

Can span multiple datafiles

Allocated for an object (e.g., table)

5.       Extents

Set of contiguous Oracle blocks; as segment grows extents are added; May not span datafiles

6.       Data Blocks

Finest level of granularity

Data stored in blocks

Smallest unit of storage that Oracle can allocate, read/write

One data block = multiple of OS block

DB_BLOCK_SIZE sets the size

3.   SYSTEM and Non-SYSTEM Tablespaces

SYSTEM Tablespace	Non-SYSTEM
Created with database contains: ·       Data dictionary     information ·       SYSTEM rollback     segment ·       SYS, SYSTEM objects ·       Should not contain user data	Tablespace contains: ·       Rollback segments ·       Temporary segments ·       Application data ·       Application indexes ·       Other user objects

4.   Create Table Space

1.     Permanent tablespace

·       create tablespace testtbs datafile ‘/u02/oradata/orcl/testtbs1.dbf’ size 50m autoextend on next 32m maxsize 2048m extent management local;

·       create tablespace datatbs datafile ‘/u02/oradata/orcl/data1.dbf’ size 100M autoextend on maxsize 200M extent management local;

2.     Temporary tablespace

·       create temporary tablespace tempnew tempfile ‘/u02/oradata/orcl/tempnew.dbf’ size 50m autoextend on next 32m maxsize 2048m extent management local;

·       Note: a temporary tablespace has tempfiles, not data files.

·       Making default temporary tablespace:-

alter database default temporary tablespace tempnew;

·       Dropping Temporary Tablespace:-

drop tablespace tempnew including contents and datafile;

3.     UNDO Tablespace

create undo tablespace undonew datafile ‘/u02/oradata/orcl/undonew.dbf’ size 100M;

alter system set undo_tablespace='UNDONEW' scope=both;

Drop tablespace UNDONEW including contents and datafiles;

5.      Renaming Table spaces

·       This is a feature that is available with Oracle 10g and can be useful in transportable tablespace scenarios.

alter tablespace users rename to usersnew;

·       The system and sysaux table space cannot be renamed, though.

·       However, read only table spaces can be renamed.

·       After renaming a table space, the control files should immediately be backed up.

6.      The system and sysaux tablespace

1.     SYSTEM Tablespace

·       The system tablespace is always available when a database is open (it cannot be taken offline).

·       The system tablespace stores the data dictionary (or their base tables, respectively).

2.     The sysaux tablespace

·       The sysaux tablespace is new with Oracle 10g. It is used to store database components that were stored in the system tablespace in prior releases of the database.

·       Also, the tablespaces that were needed for RMAN's recovery catalog, for Ultra Search, for Data Mining, for XDP and for OLAP are going to sysaux with 10g.

·       Additionally, it is the place where automatic workload repository stores its information.

7.   Space management

·       Oracle maintains extents for a tablespace. There are two different methods for Oracle to keep track of free and used (occupied) extents:

·       Dictionary managed tablespace

·       Locally managed tablespace

1.     Dictionary-managed tablespaces:

·       Free extents recorded in data dictionary

·       Oracle use the data dictionary (tables in the SYS schema) to track allocated and free extents for tablespaces. Free space is recorded in the SYS.FET$ table, and used space in the SYS.UET$ table.

·       Whenever space is required in one of these tablespaces, the ST (space transaction) enqueue latch must be obtained to do inserts and deletes against these tables. As only one process can acquire the ST enque at a given time, this often lead to contention.

2.     Locally managed tablespaces:

·       Free extents recorded in bitmap

·       Each bit corresponds to a data block or group of blocks

·       Bit value indicates free or used

·       Reduced recursive space management (can occur in dictionary managed ts where consuming or releasing of one extent causes consuming or releasing of another)

·       Reduced contention on data dictionary tables

·       No rollback generated (since there is no update to the tables in the data dictionary)

·       No coalescing required

Ex:-

CREATE TABLESPACE user_data

DATAFILE ‘/u02/oradata/orcl/user_data.dbf’ SIZE 500M

EXTENT MANAGEMENT LOCAL

UNIFORM SIZE 10M [or AUTOALLOCATE];

·       In a locally-managed tablespace, space management tasks are handled by bitmaps stored within the tablespace itself. 

·       A bitmap is used to keep track of the block status in each datafile, whether they are free or used.  Each bit in the bitmap maps to a block or a group of blocks in the datafile.

8.   State of Tablespaces

·       A table space is either online (ready to be accessed) or offline (not accessible).

·       A table space becomes offline either when the DBA explicitly makes the tablespace offline, or when an error occurs while Oracle accesses a datafile within the tablespace.

·       Offline table spaces cannot be transported to other databases.

1.     Read Only Table spaces

·       Read only table spaces have the benefit that they need be backed up only once.

9.   Examples on Tablespaces and datafiles

1.     Adding Data Files to a Tablespace

                  Ex:-

ALTER TABLESPACE app_data ADD DATAFILE ‘/DISK5/app03.dbf’ SIZE 200M;

2.     Adding Data Files to a Temporary Tablespace

      Ex:-

ALTER TABLESPACE temp ADD TEMPFILE ‘/DISK5/app03.dbf’ SIZE 200M;

3.     Enabling Automatic Extension of Data Files

Ex:-

ALTER TABLESPACE app_data ADD DATAFILE ‘/DISK6/app04.dbf’ SIZE 200M

AUTOEXTEND ON NEXT 10M MAXSIZE 500M;

4.     Changing the Size of Data Files Manually

Ex:-

ALTER DATABASE DATAFILE ‘/DISK5/app02.dbf’ RESIZE 200M;

5.     OFFLINE Status 

·       The tablespace that is offline is not available for data access.

·        The SYSTEM tablespace and any tablespace with active rollback  segments cannot be taken offline.

Ex:-

ALTER TABLESPACE app_data OFFLINE;

6.     The READ-ONLY Tablespace Status

Ex:-

ALTER TABLESPACE app_data READ ONLY;

7.     Dropping Tablespaces

Ex:-

DROP TABLESPACE users INCLUDING CONTENTS AND DATAFILES; 

8.     Dropping Datafiles

You can not drop the first datafile of a tablespace.

Ex:-

alter tablespace tty  drop datafile '/u01/oradata/tt3.dbf‘