How to check Tablespace Fragmentation in Oracle Database?

Oracle Tablespace Fragmentation

Honeycomb fragmentation

Which is easy to fix. All that needs to be done is to combine adjacent free segments into one by issuing a coalesce statement:

alter tablespace USERS coalesce;

Bubble fragmentation

Which is more difficult to handle. Of course, the best course of action is to prevent it in the first place. And as discussed earlier, the best weapon for this is to use locally-managed tablespaces. It may sound too simple, but in reality, implementing these storage structures in your database can just about remove the need to perform full tablespace reorganizations.

However, what do you do if you are in a situation where you have many databases that were set up with dictionary-managed tablespaces? You have two options, at least if you are running Oracle 8i (the version that gave birth to locally-managed tablespaces). First, you can create new locally-managed tablespaces in your database and then perform one last, full tablespace reorganization of all the database objects into the new tablespaces.

Needless to say, this can be a difficult task if you have large databases and no third-party reorganization tool. However, it will be worth the effort as you will likely never have to reorganize your tablespaces again, and your objects should never again encounter a maximum extent limit.

If you are using Oracle 8.1.6 or higher, you can convert any current dictionary-managed tablespaces to locally-managed tablespaces. Buried in the Oracle documentation is a procedure for converting a tablespace's extent management from dictionary to local or vice-versa.

convert DMT to LMT:

sys.dbms_space_admin .tablespace_migrate_to_local('USERS');

If you are afraid of how long this procedure might take on large tablespaces, do not be. It actually runs very fast.

convert LMT to DMT:

sys.dbms_space_admin .tablespace_migrate_from_local('USERS');

Detecting Oracle Tablespace Fragmentation

How can you tell if your tablespaces are suffering from fragmentation problems and then identify the type of fragmentation? The detection and diagnosis is not hard to make at all. To determine if your tablespaces are having a problem with fragmentation, you can use the tsfrag.sql script:

select
tablespace_name,
count(*) free_chunks,
decode(
round((max(bytes) / 1024000),2),
null,0,
round((max(bytes) / 1024000),2)) largest_chunk,
nvl(round(sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)) )),2),
0) fragmentation_index
from
sys.dba_free_space
group by
tablespace_name
order by
2 desc, 1;


When you examine the script output, you need to hone in on a couple of columns in particular. First, notice the fragmentationindex column. This will give your tablespace an overall ranking with respect to how badly it is actually fragmented. A 100% score indicates no fragmentation at all. Lesser scores verify the presence of fragmentation.

The free chunks count column will tell you how many segments of free space are scattered throughout the tablespace. One thing to keep in mind is that tablespaces with multiple datafiles will always show a free chunk count greater than one because each datafile will likely have at least one pocket of free space.

How to check the tablespace usage in Oracle Database?

Below is the script which will list tablespace wise free space and used space as well as total space.
The Size details are displayed in MB.

SELECT Total.name "Tablespace Name",
nvl(Free_space, 0) "Free Size(MB)",
nvl(total_space-Free_space, 0) "Used Size(MB)",
total_space "Total Size(MB)"
FROM
(select tablespace_name, sum(bytes/1024/1024) free_space
from sys.dba_free_space
group by tablespace_name
) Free,
(select b.name, sum(bytes/1024/1024) total_space
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name
) Total
WHERE Free.Tablespace_name(+) = Total.name
ORDER BY Total.name;

How to check which tablespace cannot extent causing tablespace full error?

Below is the script to check which tablespace cannot extent causing "tablespace full error"

select a.owner||'.'||a.segment_name "Segment Name",
a.segment_type "Segment Type",
a.bytes/1024/1024 "Size(MB)",
a.next_extent/1024/1024 "Next Extent",
a.tablespace_name "Tablespace Name"
from sys.dba_segments a
where a.tablespace_name not like 'T%MP%' -- Exclude TEMP tablespaces
and next_extent * 1 > ( -- Cannot extend 1x, can change to 2x...
select max(b.bytes)
from dba_free_space b
where a.tablespace_name = b.tablespace_name)
order by 3 desc;

Script to check tablespace usage in Oracle Database

SELECT Total.name "Tablespace Name",
nvl(Free_space, 0) "Free Size(MB)",
nvl(total_space-Free_space, 0) "Used Size(MB)",
total_space "Total Size(MB)"
FROM
(select tablespace_name, sum(bytes/1024/1024) free_space
from sys.dba_free_space
group by tablespace_name
) Free,
(select b.name, sum(bytes/1024/1024) total_space
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name
) Total
WHERE Free.Tablespace_name(+) = Total.name
ORDER BY Total.name;

script for tablespace useage

Below is the script to get data about all the tablespaces in a database.

SELECT Total.name "Tablespace Name",
total_space "Total Size(MB)",
nvl(total_space-Free_space, 0) "Used Size(MB)",
nvl(Free_space, 0) "Free Size(MB)"
FROM
(select tablespace_name, sum(bytes/1024/1024) free_space
from sys.dba_free_space
group by tablespace_name
) Free,
(select b.name, sum(bytes/1024/1024) total_space
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name
) Total
WHERE Free.Tablespace_name(+) = Total.name
ORDER BY Total.name;