Great Tips to Resolve Tablespace Fragmentation Issues!!

 Author: S. Sheidaei

Being database administrator you must have faced a number of issues while fragmenting tablespaces and we better know that it is always a good idea to have the information of tablespace allocated and free area. In this article we will help you resolve some key issues related to tablespace fragmentation by showing tablespace map of free space and used space in the tablespace.

Create Table:

First of all we will create below table in the database.

CREATE TABLE IF NOT EXISTS MY_OBJECT_PLACE_IN_TBS
(
   MY_TBS_NAME VARCHAR2(30)
   MY_ALLOCATION_PATTERN CLOB
   MY_OBJECT_PATTERN CLOB
   MY_TIME DATE
);

The column MY_ALLOCATION_PATTERN will show allocated extents with + and free extents with * whereas the column MY_OBJECT_PATTERN will have allocated extents as the name of the object and free extents as *.

Create Script:

Now we will run below script.

CREATE OR REPLACE PROCEDURE GET_TABLESPACE_MAP(my_target_tablespace VARCHAR2, minimum_extents NUMBER DEFAULT 8) IS

   current_block_ID NUMBER; previous_block_ID NUMBER;
   current_blocks NUMBER; previous_blocks NUMBER;
   my_map_str CLOB; my_alloc_str CLOB; my_temp NUMBER; my_file_ID_v NUMBER;

   CURSOR my_cursor IS select segment_name,blocks,block_id,EXTENT_ID from dba_extents where       MY_TBS_NAME=Upper(my_target_tablespace) AND file_id=my_file_ID_v order by block_id,EXTENT_ID;

   myvar my_cursor%ROWTYPE;

   BEGIN

FOR rec IN (SELECT file_id FROM dba_data_files WHERE MY_TBS_NAME=Upper(my_target_tablespace))

LOOP

  my_file_ID_v:=rec.file_id;
  current_block_ID:=1;
  previous_block_ID:=1;
  previous_blocks:=0;
  OPEN my_cursor;

LOOP

FETCH my_cursor INTO myvar;

EXIT WHEN my_cursor%NOTFOUND;

current_block_ID:=myvar.block_id;
current_blocks:=myvar.blocks;

IF(previous_block_ID+ previous_blocks= current_block_ID) THEN

FOR i IN 1..round(myvar.blocks/minimum_extents)
LOOP my_alloc_str:=my_alloc_str||'-'|| '+'; END LOOP;

my_temp:= trunc(myvar.blocks/minimum_extents);
my_map_str:=my_map_str||'-'|| myvar.segment_name;
my_map_str:=my_map_str||'('||To_Char(my_temp)||')';
previous_block_ID:=current_block_ID;
previous_blocks:=current_blocks;

ELSE

FOR i IN 1..Round (
(current_block_ID-previous_block_ID-previous_blocks) /minimum_extents )
LOOP
my_map_str:=my_map_str||'-'|| '*';
my_alloc_str:=my_alloc_str||'-'|| '*';
END LOOP;

FOR i IN 1..Round(myvar.blocks/minimum_extents)
LOOP my_alloc_str:=my_alloc_str||'-'|| '+'; END LOOP;

my_temp:= trunc(myvar.blocks/8);
my_map_str:=my_map_str||'-'|| myvar.segment_name;
my_map_str:=my_map_str||'('||To_Char(my_temp)||')';
previous_block_ID:=current_block_ID;
previous_blocks:=current_blocks;

END IF;

--Dbms_Output.put_line(my_map_str);

END LOOP;

  INSERT INTO MY_OBJECT_PLACE_IN_TBS VALUES (my_target_tablespace, my_file_ID_v, my_alloc_str, my_map_str, sysdate);
COMMIT;

  my_alloc_str:=NULL;
  my_map_str:=NULL;

END LOOP;

CLOSE my_cursor;

   END;
/

Output:

Running above script will give us result as follows

MY_TBS_NAME MY_ALLOCATION_PATTERN MY_OBJECT_PATTERN
------------------------------------------------------------------------------
users +++*+++ -obj1(3)-*-objx(1)-objy(1)-objx(1)

The column MY_ALLOCATION_PATTERN shows that the first 3 extents are allocated, 4 th one is free and the next three extents have also been allocated. Similarly the MY_OBJECT_PATTERN column shows that first 3 two extents are allocated by obj1, the 3 rd extent is free, the 5 th extent is allocated by objx, 6thn one by objy and 7 th one by objx.

Final Words:

This is a very easy to use script for database administrators and enables you to resolve the tablespace fragmentation issues by identifying the offending objects and relocating them. Read Again!!

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Great Tips for Oracle Wrap Utility!!
   Inside Automating Histogram Sampling!!
   Great Tips on Oracle Real-Time SQL Monitoring!!
   Great Tips on Tuning Database Materialized Views