De Fragmentation In Oracle Re organization Reclaim Space and Optimize Performance

YouTube video


In this article, we will discuss the process of de-fragmentation, or re-organization, in Oracle and its importance in reclaiming space and optimizing database performance. As a Database Administrator (DBA), it is essential to have a proper plan in place before performing any activity to ensure smooth execution and minimal downtime. We will explore the step-by-step process of moving tables and indexes to a new tablespace, validating the relocation, and dropping the temporary tablespace. Throughout the article, we will focus on the key points and provide detailed explanations to help you fully understand the process.

Key Points

  1. Understanding the need for de-fragmentation in Oracle database.
  2. The importance of planning and having a backup strategy before starting any activity.
  3. Checking the objects in the tablespace and creating a new tablespace for reorganization.
  4. Moving tables and indexes to the new tablespace and verifying the relocation.
  5. Recompiling any dependent objects to ensure validity.
  6. Reclaiming unused space and resizing the data file.
  7. Validating the successful execution of the reorganization process.
  8. Dropping the temporary tablespace to complete the de-fragmentation.
  9. Monitoring and optimizing performance after de-fragmentation.

Understanding the Need for De-Fragmentation in Oracle Database

When working with Oracle database, it is common to encounter scenarios where table and index data is scattered across the tablespace, leading to fragmented data storage and decreased performance. The size of the user’s table mentioned in the video transcript is 10GB, but even after deleting rows, the space is not efficiently utilized. This unused space can cause data files to reach their maximum size, resulting in errors when trying to resize them. It is important to understand that simply deleting rows from tables does not automatically free up space at the tablespace level.

Planning and Backup Strategy

As a responsible DBA, it is crucial to plan and strategize before executing any activity. This ensures that the process is carried out smoothly and minimizes the risk of data loss or system downtime. Creating a backup of the database is an essential step before starting the de-fragmentation process.

Checking Objects and Creating a New Tablespace

To begin the de-fragmentation process, it is important to identify the objects present in the tablespace. In this example, the video transcript mentions tables related to the user schema “Scott” being present in the tablespace. To perform the re-organization, a new tablespace named “reorg_clears” is created.

Moving Tables and Indexes to the New Tablespace

Once the new tablespace is created, tables and indexes are moved from the original tablespace to the new tablespace. This step ensures that the table and index data are stored together, optimizing data retrieval and improving performance. The video transcript suggests executing specific queries to generate scripts for enabling table movement and moving the tables to the new tablespace.

Validating the Relocation

After moving the tables and indexes, it is important to verify that no objects are left in the original tablespace. This involves running queries to check whether the expected count of objects in the new tablespace matches the earlier count. Additionally, it is necessary to recompile any dependent objects to ensure their validity after the re-organization process.

Reclaiming Unused Space and Resizing the Data File

One of the main goals of de-fragmentation is to reclaim unused space and optimize data storage. After relocating the tables and indexes, the data file size can be resized to the appropriate size. This step helps in freeing up space and ensuring that the data file aligns with the database’s actual storage requirements.

Validating the Successful Execution

To ensure that the de-fragmentation process has been successfully executed, it is important to validate the changes made. Checking the current status of the tables and indexes in the database can provide insights into the space reclamation achieved and the improved performance.

Dropping the Temporary Tablespace

Once the de-fragmentation process is complete and all objects have been successfully moved and verified, the temporary tablespace created during the process can be dropped. This step ensures that the database is clean and only contains the necessary tablespaces.

Monitoring and Optimizing Performance

After de-fragmentation, it is essential to monitor and optimize the performance of the Oracle database. This involves regularly analyzing performance metrics, tuning SQL queries, and managing the database resources effectively to ensure optimal performance.


De-fragmentation, or re-organization, is a vital task for Oracle DBAs to optimize performance and reclaim space in the database. By following a structured plan and executing the step-by-step process outlined in this article, you can ensure efficient data storage and improved performance for your Oracle database.

If you found this article useful, please like, share, and subscribe to our channel for more informative content. If you have any doubts or questions, please leave a comment, and we will be happy to assist you. Stay tuned for our upcoming video on handling high watermark in Oracle. Thank you for watching!