The following is a script that will determine how many extents of contiguous free space you have in Oracle as well as the total amount of free space you have in each tablespace. From these results, you can detect how fragmented your tablespace is.
The ideal situation is to have one large free extent in your tablespace. The more extents of free space there are in the tablespace, the more likely you will run into fragmentation problems. The size of the free extents is also very important. If you have a lot of small extents (too small for any next extent size) but the total bytes of free space is large, then you may want to consider defragmentation options.
create table SPACE_TEMP (
TABLESPACE_NAME CHAR(30),
CONTIGUOUS_BYTES NUMBER)
/
declare
cursor query is select *
from dba_free_space
order by tablespace_name, block_id;
this_row query%rowtype;
previous_row query%rowtype;
total number;
begin
open query;
fetch query into this_row;
previous_row := this_row;
total := previous_row.bytes;
loop
fetch query into this_row;
exit when query%notfound;
if this_row.block_id = previous_row.block_id + previous_row.blocks then
total := total + this_row.bytes;
insert into SPACE_TEMP (tablespace_name)
values (previous_row.tablespace_name);
else
insert into SPACE_TEMP values (previous_row.tablespace_name,
total);
total := this_row.bytes;
end if;
previous_row := this_row;
end loop;
insert into SPACE_TEMP values (previous_row.tablespace_name,
total);
end;
.
/
set pagesize 60
set newpage 0
set echo off
ttitle center 'Contiguous Extents Report' skip 3
break on "TABLESPACE NAME" skip page duplicate
spool contig_free_space.lis
rem
column "CONTIGUOUS BYTES" format 999,999,999
column "COUNT" format 999
column "TOTAL BYTES" format 999,999,999
column "TODAY" noprint new_value new_today format a1
rem
select TABLESPACE_NAME "TABLESPACE NAME",
CONTIGUOUS_BYTES "CONTIGUOUS BYTES"
from SPACE_TEMP
where CONTIGUOUS_BYTES is not null
order by TABLESPACE_NAME, CONTIGUOUS_BYTES desc;
select tablespace_name, count(*) "# OF EXTENTS",
sum(contiguous_bytes) "TOTAL BYTES"
from space_temp
group by tablespace_name;
spool off
drop table SPACE_TEMP
/
Sample output from the above script:
Contiguous Extents Report TABLESPACE NAME CONTIGUOUS BYTES ------------------------------ ---------------- RBS 52,426,752 RBS 2,662,400 RBS 798,720 RBS 266,240 ... TABLESPACE NAME CONTIGUOUS BYTES ------------------------------ ---------------- USERS 20,480 USERS 16,384 USERS 10,240 USERS 10,240 USERS 10,240 USERS 4,096 TABLESPACE_NAME # OF EXTENTS TOTAL BYTES ------------------------------ ------------ ------------ RBS 15 56,154,112 SYSTEM 10 927,744 TEMP 5 665,600 TOOLS 10 89,397,248 USERS 6 71,680