Managing Storage Space in Oracle

Back to: Oracle DBA Tutorials

Managing Storage Space in Oracle

This article explains how to create tablespaces and manage storage spaces in Oracle DBA. The Oracle database server automatically manages space. It generates alerts about potential problems and recommends possible solutions. With every new version of the Oracle database, the database does a lot of things in the background, which helps to understand the storage structure in the database’s background. There are multiple storage tools to manage Oracle Database:

Oracle Managed Files (OMF):

• There is no need to worry about file names and storage requirements. Oracle provides a unique full name for data files, redo log files, control files, and other database files.
• Eliminates the need for the DBA to manage OS files directly.
• Allows operations to be specified in terms of objects and not files.
• Reduces the chance of overwriting a file. This avoids accidental corruption of data files.

Free-space management with bitmaps (“locally managed”):

This is also known as Automatic Segment space management (ASSM). When we create a tablespace and mention the option segment space management to ‘auto’, Oracle uses the bitmap space management.

CREATE TABLESPACE mytbs1
 DATAFILE '/u01/oracle/data/mytbs01.dbf' SIZE 500M
 EXTENT MANAGEMENT LOCAL
 SEGMENT SPACE MANAGEMENT AUTO;

From the above statement, we are creating tablespace mytbs1 with the extent management local. This extent management local will use bitmaps to manage the space. The Segment space management is set to auto.

Oracle stores data in data blocks. By default, the block size is 8kb. When we try to insert any data, Oracle fills the block with these 8kb.Once the delete statement is issued, the data is deleted from the block, and the block is free now, and any data can be inserted.

The term segment space management auto-looks for the free blocks and then allocates the data in those blocks.

• Automatic datafile Extension: the datafiles present in the database are auto-extensible and will extend automatically based on the amount of data.
• Proactive space management: This tool helps to manage the database proactively. Whenever any tablespace reaches 80%, the database sends an alert about the threshold value.

So, these are the essential things that we need to learn about storage space management in the Oracle database. In the next few articles, we will take a few examples and showcase storage space management in the database.

In the next article, I will discuss Segments, Extensions, and Data Blocks in Oracle. Here, in this article, I explain how to manage storage space in Oracle with examples. I hope you enjoy this article.