Need query to find out tables whose next extent is bigger than largest free extent in the tablespace

Hello,

We have Oracle Apps 11i instance. This is a potential threat. We have table space monitoring scripts in-place. But sometimes they are useless if the tables are fragmented.

I have a script to find out these tables but in out instance it is taking 30 mins to execute. The instance is 3.1 TB in size.

Here is the SQL:

select a.owner owner,

       a.segment_name segment_name,

       b.tablespace_name tablespace_name,

       decode(ext.extents,

              1,

              b.next_extent,

              a.bytes * (1 + b.pct_increase / 100)) next_ext,

       freesp.largest largest

  from dba_extents a,

       dba_segments b,

       (select owner,

               segment_name,

               max(extent_id) extent_id,