Storage Structures in Oracle Database

Key Concepts

Storage Structures in Oracle Database are fundamental components that define how data is stored, managed, and accessed. Understanding these structures is crucial for optimizing database performance and ensuring data integrity. The key concepts include:

1. Datafiles

Datafiles are physical files on disk that store the actual data of the database. Each datafile belongs to a specific tablespace and contains the data for one or more database objects.

Example:

Creating a datafile for a tablespace:


        ALTER TABLESPACE users ADD DATAFILE '/u01/app/oracle/oradata/ORCL/users01.dbf' SIZE 100M;

2. Tablespaces

Tablespaces are logical storage units that group related datafiles together. Each tablespace can contain one or more datafiles and is used to organize database objects.

Example:

Creating a new tablespace:


        CREATE TABLESPACE sales DATAFILE '/u01/app/oracle/oradata/ORCL/sales01.dbf' SIZE 200M;

3. Segments

Segments are portions of a tablespace that store the data for a specific database object, such as a table or index. Each segment is divided into one or more extents.

Example:

Creating a table with a segment in a specific tablespace:


        CREATE TABLE employees (id NUMBER, name VARCHAR2(50)) TABLESPACE users;

4. Extents

Extents are contiguous blocks of storage allocated to a segment. When a segment requires more space, it is allocated in the form of extents.

Example:

Setting the initial and next extent sizes for a tablespace:


        CREATE TABLESPACE sales DATAFILE '/u01/app/oracle/oradata/ORCL/sales01.dbf' SIZE 200M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

5. Blocks

Blocks are the smallest units of storage in Oracle Database. Each block corresponds to a specific number of bytes on disk and is the level at which data is read or written.

Example:

Setting the block size for a tablespace:


        CREATE TABLESPACE sales DATAFILE '/u01/app/oracle/oradata/ORCL/sales01.dbf' SIZE 200M BLOCKSIZE 8K;

6. Control Files

Control files are critical for the operation of the database. They contain metadata about the database, including the names and locations of datafiles and redo log files.

Example:

Creating a control file:


        ALTER DATABASE BACKUP CONTROLFILE TO '/u01/app/oracle/oradata/ORCL/control01.ctl';

7. Redo Log Files

Redo log files record all changes made to the database. They are essential for recovery operations, as they allow the database to replay changes and restore data to a consistent state.

Example:

Adding a redo log file to a group:


        ALTER DATABASE ADD LOGFILE GROUP 3 ('/u01/app/oracle/oradata/ORCL/redo03a.log', '/u01/app/oracle/oradata/ORCL/redo03b.log') SIZE 50M;

8. Archived Redo Log Files

Archived redo log files are copies of redo log files that have been filled and archived. They are used for point-in-time recovery and maintaining data integrity.

Example:

Archiving redo log files:


        ALTER SYSTEM ARCHIVE LOG ALL;

9. Temporary Tablespaces

Temporary tablespaces are used for temporary storage, such as sorting operations and hash joins. They are automatically created and managed by Oracle.

Example:

Creating a temporary tablespace:


        CREATE TEMPORARY TABLESPACE temp TEMPFILE '/u01/app/oracle/oradata/ORCL/temp01.dbf' SIZE 100M;

10. Undo Tablespaces

Undo tablespaces store undo data, which is used to roll back transactions and provide read consistency. They are essential for maintaining data integrity.

Example:

Creating an undo tablespace:


        CREATE UNDO TABLESPACE undotbs1 DATAFILE '/u01/app/oracle/oradata/ORCL/undotbs01.dbf' SIZE 200M;

11. System Tablespace

The system tablespace is the default tablespace used by Oracle to store system-related data, such as metadata and internal tables.

Example:

Adding a datafile to the system tablespace:


        ALTER TABLESPACE SYSTEM ADD DATAFILE '/u01/app/oracle/oradata/ORCL/system02.dbf' SIZE 500M;

12. Default Tablespace

The default tablespace is the tablespace to which new database objects are automatically assigned if no tablespace is specified.

Example:

Setting the default tablespace for a user:


        ALTER USER hr DEFAULT TABLESPACE users;

13. Temporary Tablespace Group

Temporary tablespace groups allow multiple temporary tablespaces to be grouped together, providing flexibility in managing temporary storage.

Example:

Creating a temporary tablespace group:


        ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/ORCL/temp01.dbf' ADD TO GROUP temp_group;

14. Undo Tablespace Group

Undo tablespace groups allow multiple undo tablespaces to be grouped together, providing flexibility in managing undo storage.

Example:

Creating an undo tablespace group:


        ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/ORCL/undotbs01.dbf' ADD TO GROUP undo_group;

15. Automatic Storage Management (ASM)

ASM is a storage management feature that provides automatic storage management for Oracle databases. It simplifies storage management and improves performance.

Example:

Creating a disk group using ASM:


        CREATE DISKGROUP data_dg NORMAL REDUNDANCY DISK '/dev/oracleasm/disks/ORCL_DATA01', '/dev/oracleasm/disks/ORCL_DATA02';

16. Fast Recovery Area (FRA)

The Fast Recovery Area is a disk-based storage area used for storing backup files, archived redo logs, and other recovery-related files. It simplifies the management of recovery files.

Example:

Configuring the Fast Recovery Area:


        ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10G;
        ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u01/fra';

17. Temporary Files

Temporary files are used by temporary tablespaces to store temporary data, such as sort operations and hash joins. They are automatically created and managed by Oracle.

Example:

Creating a temporary file:


        ALTER TABLESPACE temp ADD TEMPFILE '/u01/app/oracle/oradata/ORCL/temp02.dbf' SIZE 100M;