Home / Installation/Configurations12c / Tablespace Level Fragmentation

Tablespace Level Fragmentation

Tablespace Level Fragmentation in Oracle Database


In this article, I will discuss tablespace level fragmentation, which causes slowness and wastage of space. Fragmentation is a common issue in Oracle databases due to excessive dml operations like insert followed by an update and delete operations.

Brief Explanation:

In Oracle, tablespace level fragmentation refers to space fragmentation within a tablespace, leading to inefficient storage utilization and potential performance issues. It occurs when free space within the tablespace is fragmented into smaller, non-contiguous extents.

Tablespace level fragmentation can occur due to various reasons, such as:

Data Insertions, Updates, and Deletions: Frequent data modifications can result in fragmented free space within the tablespace as objects grow and shrink.

Improper Storage Allocation: Inadequate or incorrect storage allocation settings can lead to fragmentation, especially if the initial extent sizes are too small or if automatic segment space management is not utilized.

Uneven Object Growth: When objects within the tablespace grow at different rates, it can lead to uneven distribution of free space and fragmentation.

Inefficient Reorganization or Maintenance: Lack of regular tablespace maintenance activities, such as the reorganization or shrinking of objects, can contribute to fragmentation over time.

The presence of tablespace level fragmentation can negatively impact database performance, leading to slower query execution, increased I/O operations, and inefficient use of storage resources.

Migrating tables and indexes to new tablespaces can indeed be an efficient and effective way to defragment and shrink a large tablespace in Oracle. This approach involves moving the objects from the fragmented tablespace to a new, properly-sized tablespace, thereby consolidating free space and optimizing storage utilization. Here’s an overview of the process:

Step 1. Log in with the dba account and create new tablespaces for the database user.

Sample SQL:
create tablespace XX_ERP2 datafile ‘/path/to/XX_ERP2.dbf’ size 256m autoextend on next 128m maxsize unlimited;

Step 2. Log in with the Database owner’s username/password

Step 3. Run the script below to generate a table migration script for migrating tables to a new tablespace

spool /home/oracle/movTbl.sql
select ‘alter table ‘ || SEGMENT_NAME || ‘ move tablespace XX_ERP2;’
FROM dba_Segments a,
dba_data_files b
WHERE b.file_id=a.relative_fno
and a.tablespace_name=’XX_ERP’ and segment_type=’TABLE’
order by FILE_NAME,segment_name;
spool off;

Step 4. Run the script below to generate an Index migration script for migrating the Index to a new tablespace

spool /home/oracle/movIndex.sql
 SELECT ‘alter index ‘ || SEGMENT_NAME || ‘ rebuild tablespace XX_ERP2;’
FROM dba_Segments a, dba_data_files b
WHERE b.file_id = a.relative_fno
AND a.tablespace_name = ‘XX_ERP’
AND segment_type = ‘INDEX’
ORDER BY FILE_NAME, segment_name;
spool off;

Step 5. Run the script below to generate the LOB Segments script for migrating the LOB Segments to a new tablespace if available.

spool /home/oracle/movLobSeg.sql
select ‘ALTER TABLE ‘ || table_name || ‘ move lob(‘ || COLUMN_NAME || ‘) STORE AS (TABLESPACE XX_ERP2);’
from dba_tab_columns
where owner=’XX_ERP’ and data_type=’CLOB’;
spool off;

Step 6. Check if anything is missing in the Original Tablespace to be Migrated

set lines 300
col owner format A26
col segment_name format A26
col segment_type format A26
col tablespace_name format A26
col relative_fno format 99999
col file_name format A50
SELECT owner, segment_name, segment_type,a.tablespace_name, a.relative_fno, b.file_name
FROM dba_Segments a,
dba_data_files b
WHERE b.file_id=a.relative_fno
and a.tablespace_name=’XX_ERP’
order by FILE_NAME,segment_name;

Step 7. Do not forget to change the default tablespace of the user to the Newly Created One

ALTER USER default tablespace XX_ERP2;

Step 8. Change the Old Tablespace Offline once all the Objects are transferred to the newly created Tablespace with the Scripts Generated.

alter tablespace XX_ERP offline;


  • 5

About Syed Saad Ali

With 13 years of experience as a certified and skilled Oracle Database Administrator, I possess the expertise to handle various levels of database maintenance tasks and proficiently perform Oracle updates. Throughout my career, I have honed my analytical abilities, enabling me to swiftly diagnose and resolve issues as they arise. I excel in planning and executing special projects within time-sensitive environments, showcasing exceptional organizational and time management skills. My extensive knowledge encompasses directing, coordinating, and exercising authoritative control over all aspects of planning, organization, and successful project completions. Additionally, I have a strong aptitude for resolving customer relations matters by prioritizing understanding and effective communication. I am adept at interacting with customers, vendors, and management, ensuring seamless communication and fostering positive relationships.

Check Also


Oracle Apex Upgrade to 22.2

Oracle Apex Upgrade to 22.2   ORACLE APEX Oracle APEX (also known as APEX or …

Leave a Reply