How to Create Tablespace in Oracle Database 19c A Comprehensive Tutorial by Manish Sharma

YouTube video

oracle tablesapce

Are you an Oracle database user seeking to create a tablespace? Look no further! In this tutorial, we will guide you step-by-step on how to create a tablespace in Oracle Database 19c. Whether you’re using Oracle 12c or any other version, you can follow these instructions. So let’s dive right in without wasting any time!

Steps to Create a Tablespace in Oracle Database 19c

  1. Connect to the Root Container: First, connect yourself to the root container of Oracle Database 19c using the sysuser with DB privileges.

sql
CONNECT sysuser/password AS SYSDBA;

  1. Clear the Screen: To ensure clarity, clear the screen with the following statement:

sql
CLEAR SCREEN;

  1. Define the Tablespace Name: Decide on the name for your tablespace. In this tutorial, we’ll use the name “rmn_backup” as an example.

sql
CREATE TABLESPACE rmn_backup;

  1. Specify the Data File: Add a data file to the tablespace. Since a data file is a physical file, choose a directory to save it. In this example, we will save it in a directory named “oracle_backup” that has already been created. Ensure that the file name ends with the extension “.dbf” and consider adding a number at the end for easier media recovery.

sql
CREATE TABLESPACE rmn_backup DATAFILE 'oracle_backup/rmn_backup.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 1G;

  1. Set the Extent Management: Decide whether you want to locally manage the tablespace’s extents or have Oracle automatically manage them. For simplicity and efficiency, we recommend setting the extent management to “LOCAL” and segment space management to “AUTO.”

sql
CREATE TABLESPACE rmn_backup DATAFILE 'oracle_backup/rmn_backup.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 1G
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

  1. Online the Tablespace: The final step is to make the tablespace available by adding the “ONLINE” clause.

sql
CREATE TABLESPACE rmn_backup DATAFILE 'oracle_backup/rmn_backup.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 1G
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ONLINE;

Congratulations! You have successfully created a tablespace in Oracle Database 19c. Now, let’s dive deeper into the key points and concepts covered in this tutorial.

Understanding the Concepts: Data Files, Blocks, Extents, and Segments

In the Oracle ecosystem, all data is stored in files called data files. Each data file has its own file extension, which is typically “.dbf.” Oracle uses three units to manage the storage space in these data files: blocks, extents, and segments.

1. Blocks:

The smallest unit at which Oracle stores data is called a block, also known as a page. A data block corresponds to a specific number of bytes of physical database space on disk. The size of a data block can vary depending on the tablespace.

To determine the block size of the tablespace you’re working with, use the following command:

sql
SELECT block_size FROM dba_tablespaces WHERE tablespace_name = 'rmn_backup';

For example, if the block size of the “rmn_backup” tablespace is 8192 bytes, it means that each data block in that tablespace contains 8192 bytes.

2. Extents:

Extents are a collection of contiguous data blocks that Oracle Database uses to store specific information. They can be managed locally or in the data dictionary.

In this tutorial, we have set the extent management to “LOCAL,” which means that extents are managed automatically. This approach is simpler and more efficient than managing them manually.

3. Segments:

Segments are collections of extents allocated for specific data structures, such as tables or indexes. All extents within a segment are stored in the same tablespace.

Benefits of Locally Managed Extents with Auto Segment Space Management:

By setting the extent management to “LOCAL” and segment space management to “AUTO,” you can enjoy several benefits, including:

  • Simplicity: Oracle automatically manages the extents, simplifying the storage management process.
  • Efficiency: Automatic management of extents allows for more efficient space utilization.
  • Performance: Auto Segment Space Management optimizes performance by reducing overhead.

Implementing these settings ensures optimal storage management while minimizing the administrative tasks required.

Conclusion

In this tutorial, we have walked you through the process of creating a tablespace in Oracle Database 19c. We started by connecting to the root container and then proceeded to define the tablespace name, specify the data file, set extent management, and make the tablespace available. Furthermore, we explained key concepts such as data files, blocks, extents, and segments, to provide a comprehensive understanding of the topic.

We hope you found this tutorial helpful. If you did, please give this video a thumbs up and consider subscribing to our channel, Manish Sharma. Stay tuned for more interesting tutorials on database backup and recovery. Don’t forget to click the bell icon to receive notifications whenever we upload a new video.

Thank you for watching!

This is Manish from RebellionRider.com.