Relationship of Datafiles and Tablespaces in an Oracle Database

Introduction

In this post, we will learn about the Relationship of Datafiles and Tablespaces in an Oracle Database. with suitable real-time examples.

If you have any queries related to any topic in any technology, please feel free to comment below. We are ready to provide you answers to all your questions.

Before going t read about the Relationship of Datafiles and Tablespaces. Firstly let us discuss the definitions of DataFile and TableSpaces.

DATAFILE in oracle

What is DATAFILE

• The data file is a part of the physical files of the database.
• However, the Datafile holds the data in an encrypted format.
• Datafiles hold real data like sales order, employee data.etc.
• Datafile holds the real data in the form tables, indexes, etc.
• This is part of the logical structure of the database.
• The data of the logical structure of the database will store in the database moreover in the form of data files.
• Datafiles consist of OS blocks or Operating System blocks.

Characteristics of Datafiles

• A tablespace has One or more datafiles form a logical structure of the database.
• However, the Datafiles can be auto extended when they are full.
• A data file will associate with its own tablespace.

How to read data from datafile in oracle

We can read the data from the data file as needed, during the normal database operations. And the same data is stored in the memory cache of the Oracle database.

To fulfill the same request given by the user, to reduce the I/O contention on the disk.

Example

• If a user wants to access the data in a table of a database.If the data which is requested by the user is not available in-memory cache of the database.
• Then the user will read the data from appropriate datafile and the same will be stored in the memory.

How to write data from datafile in oracle

• Whenever a user modifies or inserts new data, is not necessarily written to the datafile immediately.
• The Data first stores in-memory cache and written to the respective data files all at once.

As determined by the background process database writer process (DBWn).To reduce the amount of disk access and to increase the performance.

TABLESPACE in oracle

ABOUT TABLESPACE

• The tablespace is nothing but the logical storage memory of the database.
• A database is classified into one or more logical storage memories called tablespaces.
• Logical storage physically represents one or more data files.
• Oracle database stores schema objects like tables, indexes, etc.,
• Logically stored in the tablespace and physically stored into the datafiles.
• By default, the capacity of the tablespace is the equal size of the datafile.
• A tablespace can have up to 1022 data files.
• The number of data files depends on the OS limitation.

Normally the Tablespaces are normally classified into logical units of storage called segments. Segments again divided into extents. Extents are a collection of contiguous oracle blocks.

ABOUT SEGMENT

• A segment is a logical structure that contains data (tables, indexes, etc..) nothing but database objects.
• Data will store in its own data segment.
• Most importantly one segment will have only one object, at the same time One object will have several segments’ records.
• A segment is not specific to a data file, it’s specific to a tablespace.
• The segment spawns across data files belong to the same tablespace.
• A segment is a set of extents.

ABOUT EXTENT

• The next level of the logical structure of a database is the extent.
• An extent is a contiguous oracle database block allocated for storing a specific type of information.
• Oracle allocates space for segments in units of 1 extent.
• When the current extents of the segment are sufficient. However, Oracle allocates a new different extent for the segment.
• Because extents will assign as required, the extents of a segment may or might not be adjacent on disk.
• Each extent can receive data from one data file.

ABOUT DATABLOCK (ORACLE DATA BLOCK)

• Oracle stores the data in the data blocks (Oracle data blocks) which uses operating system blocks or OS blocks.
• One data block corresponds to a selected number of bytes of physical database space on the disk.
• Oracle manages the space for storing within the datafiles of a database in units called data blocks.

Conclusion

Now we have given you detailed information about what is DataFile and TableSpace. And the Relationship of Datafiles and Tablespaces.

We will elaborate on the content in the next posts please visit again and encourage us with your support.

THANK YOU FOR READING

Thanks for your support we will provide more tricks & tips for you. If you like our tricks, share these with your friends and join my Telegram Channel for more new tricks & tips.