Tablespace Level Fragmentation in Oracle Database

Introduction

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;