TIP 52# : Tablespace map

Sometime for solving tablespace fragmentation issue, it is better off to have a better picture of tablespace allocated and free area.
In this post, I wrote a PL/SQL script which shows mapping of free space and used space in tablespace.This script helped me out to resolve tablespace fragmentation issue for a client.Analyzing the result of script would end up to detecting offending objects and relocating them.

Script details
------------------

CREATE OR REPLACE PROCEDURE mapts (target_ts VARCHAR2,min_extents NUMBER DEFAULT 8) IS
 cur_block_id NUMBER;
 prev_block_id NUMBER;
 cur_blocks NUMBER;
 prev_blocks NUMBER;
 map_str CLOB;
 alloc_str CLOB;
 tmp NUMBER;
 file_id_v NUMBER;
 CURSOR mycur IS select segment_name,blocks,block_id,EXTENT_ID from dba_extents where TABLESPACE_NAME=Upper(target_ts) AND file_id=file_id_v order by block_id,EXTENT_ID;
 myvar mycur%ROWTYPE;
BEGIN
 FOR rec IN (SELECT file_id FROM dba_data_files WHERE tablespace_name=Upper(target_ts)) LOOP
  file_id_v:=rec.file_id;
  cur_block_id:=1;
  prev_block_id:=1;
  prev_blocks:=0;
  OPEN mycur;
  LOOP
   FETCH mycur INTO myvar;
   EXIT WHEN mycur%NOTFOUND;
   cur_block_id:=myvar.block_id;
   cur_blocks:=myvar.blocks;
   IF(prev_block_id+ prev_blocks= cur_block_id) THEN
    FOR i IN 1..round(myvar.blocks/min_extents) LOOP
     alloc_str:=alloc_str||'-'|| '+';
    END LOOP;
    tmp:= trunc(myvar.blocks/min_extents);
    map_str:=map_str||'-'|| myvar.segment_name;
    map_str:=map_str||'('||To_Char(tmp)||')';
    prev_block_id:=cur_block_id;
    prev_blocks:=cur_blocks;
   ELSE
    FOR i IN 1..Round((cur_block_id-prev_block_id-prev_blocks)/min_extents) LOOP
     map_str:=map_str||'-'|| '*';
     alloc_str:=alloc_str||'-'|| '*';
    END LOOP;
    FOR i IN 1..Round(myvar.blocks/min_extents) LOOP
     alloc_str:=alloc_str||'-'|| '+';
    END LOOP;
    tmp:= trunc(myvar.blocks/8);
    map_str:=map_str||'-'|| myvar.segment_name;
    map_str:=map_str||'('||To_Char(tmp)||')';
    prev_block_id:=cur_block_id;
    prev_blocks:=cur_blocks;
   END IF;
   --Dbms_Output.put_line(map_str);
  END LOOP;
  INSERT INTO object_place_in_ts values(target_ts,file_id_v,alloc_str,map_str,sysdate);
  COMMIT;
  alloc_str:=NULL;
  map_str:=NULL;
 END LOOP;
 CLOSE mycur;
END;
/

Script needs the following table to be existed in database.

Table : object_place_in_ts
Table description:

Name Null? Type
----------------------------------------
TABLESPACE_NAME VARCHAR2(30)
ALLOC_PATTERN CLOB
OBJ_PATTERN CLOB
CRE_TIME DATE

Alloc_pattern : String pattern which allocated extents are shown with + and free extents are shown with *
Obj_patten : String pattern which allocated extents have name of object and free extents are shown with *.

Example :

tablespace_name allocated_pattern object_pattern
------------------------------------------------------------------------------
users ++*+++ -obj1(2)-*-objx(1)-objy(1)-objx(1)

In this example, two first extents have been allocated, third extent is free and fourth and fifth and sixth extents have been allocated . (Check allocated_pattern).
First two extents have been allocated by obj1, fourth and sixth extents have been allocated by objx.fifth extent has been allocated by objy.

I found this result and analysis very handy to resolve tablespace fragmentation.