Managing Tablespaces and Datafiles in Oracle Database 12c

Key Concepts

• Tablespaces
• Datafiles
• Creating Tablespaces
• Managing Datafiles
• Automatic Storage Management (ASM)

Tablespaces

A tablespace is a logical storage unit within an Oracle database that groups related logical structures together. Each tablespace consists of one or more datafiles, which are the physical files on disk where the actual data is stored. Tablespaces are crucial for organizing and managing data efficiently.

Think of a tablespace as a filing cabinet with multiple drawers. Each drawer (datafile) can hold different types of documents (data), and the entire cabinet (tablespace) is used to organize these documents.

Datafiles

Datafiles are the physical files on disk that store the actual data of the Oracle database. Each tablespace has one or more datafiles associated with it. These files are managed by the Oracle Database Management System (DBMS) and are essential for data persistence.

Imagine datafiles as individual bookshelves in a library. Each bookshelf (datafile) holds a collection of books (data), and together, they form the entire library (tablespace).

Creating Tablespaces

To create a tablespace, you need to specify its name, the size of the initial datafile, and the location where the datafile will be stored. The following SQL command is used to create a tablespace:

        CREATE TABLESPACE users
        DATAFILE '/u01/app/oracle/oradata/ORCL/users01.dbf'
        SIZE 100M
        AUTOEXTEND ON NEXT 10M MAXSIZE 200M;
    

In this example, a tablespace named "users" is created with an initial datafile of 100MB. The datafile will automatically extend by 10MB when needed, up to a maximum size of 200MB.

Managing Datafiles

Managing datafiles involves tasks such as adding new datafiles to a tablespace, resizing existing datafiles, and moving datafiles to different locations. These operations are crucial for maintaining the performance and scalability of the database.

For instance, to add a new datafile to an existing tablespace, you can use the following SQL command:

        ALTER TABLESPACE users
        ADD DATAFILE '/u01/app/oracle/oradata/ORCL/users02.dbf'
        SIZE 50M
        AUTOEXTEND ON NEXT 5M MAXSIZE 100M;
    

This command adds a new datafile of 50MB to the "users" tablespace, which will automatically extend by 5MB when needed, up to a maximum size of 100MB.

Automatic Storage Management (ASM)

Automatic Storage Management (ASM) is a feature in Oracle Database 12c that simplifies the management of storage by automatically distributing data across disks. ASM provides load balancing, mirroring, and striping of datafiles, ensuring optimal performance and reliability.

Consider ASM as an intelligent storage manager that automatically organizes your bookshelves (datafiles) in a library (database) to ensure easy access and protection of books (data).

By understanding and effectively managing tablespaces and datafiles, you can ensure the optimal performance, scalability, and reliability of your Oracle Database 12c environment.