Question: How does one find tablespace fragmentation (what is the corresponding SQL statement) ?
If there is no SQL statement, then which tool should I use?
Answer: It depends on how you define "fragmentation". In my opinion, in version 8.1.5 (Oracle8i and up), fragmentation is an impossible situation to be in.
My defininition of fragmentation is that you have many (regions of contigous free space) that are too small to be the NEXT extent of any object.
These holes of free space resulted from dropping some objects (or truncating them) and the resulting free extents cannot be used by any other object in that tablespace. This is a direct result of using a pctincrease that is not zero and having many wierd sized extents (every extent is a unique size and shape).
In Oracle8i, we would all use locally managed tablespaces. These would use either UNIFORM sizing (my favorite) or our automatic allocation scheme. In either case, it is pretty much impossible to get into a situation where you have unusable free space. To see if you suffer from "fragmentation", you can query DBA_FREE_SPACE (best to do an alter tablespace coalesce first to ensure all contigous free regions are made into 1 big free region).
DBA_FREE_SPACE will report the size of all free extents. You would look for ANY free extent that is smaller then the smallest NEXT extent size for any object in that tablespace.
Below I artifically introduce this issue by using a dictionary managed tablespace and objects with pctincrease=50.
I create two tables and then allocate extents to them one after the other so that they are "interleaved".
Then I drop one of the tables and find all of the free extents that are too small to hold the next extent for the smallest next extent in that tablespace.
thauck@THAUCK816> drop tablespace t including contents;
Tablespace dropped.
thauck@THAUCK816> create tablespace t
2 datafile 'c:\temp\t.dbf' size 10m
3 reuse
4 /
Tablespace created.
thauck@THAUCK816> create table t_t1 ( x int )
2 storage ( initial 1k next 1k pctincrease 50 )
3 tablespace t
4 /
Table created.
thauck@THAUCK816> create table t_t2 ( x int )
2 storage ( initial 1k next 1k pctincrease 50 )
3 tablespace t
4 /
Table created.
thauck@THAUCK816> alter table t_t1 allocate extent;
Table altered.
thauck@THAUCK816> alter table t_t2 allocate extent;
Table altered.
... (above 2 commands executed in order over and over) ....
thauck@THAUCK816> drop table t_t1;
Table dropped.
thauck@THAUCK816> select *
2 from dba_free_space
3 where tablespace_name = 'T'
4 and bytes <= ( select min(next_extent)
5 from dba_segments
6 where tablespace_name = 'T')
7 order by block_id
8 /
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------------------------- ---------- ---------- ---------- ---------- ------------
T 9 2 16384 2 9
T 9 6 8192 1 9
T 9 8 16384 2 9
T 9 12 24576 3 9
T 9 18 40960 5 9
T 9 28 81920 10 9
T 9 48 122880 15 9
T 9 78 163840 20 9
T 9 118 245760 30 9
T 9 178 368640 45 9
10 rows selected.
1) This tablespace contains two database objects, one with an extent size of 500 KB and another with an extent size of 1MB.
2) As extents are added to the tablespace, the different size extents are intermixed.
3) If one of the smaller extents is dropped, it leaves a hole of 500KB. The larger extent cannot fit into the smaller extent space, because extent space must be contiguous. Consequently, this 500-KB hole remains in the tablespace.