unable to extend table
I wrote a query and want to run it frequently to keep an eye on the tablespace free space. However, this query takes a long time. I am not sure whether my query is ok.
select a.segment_name, a.segment_type from dba_segments a, dba_free_space b where a.tablespace_name=b.tablespace_name and a.next_extent > b.bytes;
Currently, I have a query that checks the percentage of the tablespace that are free and used. However, the percentage sometimes is not enough to be proactive in monitoring the space. Usually, I increase the tablespace size then free space falls below 20%. Sometimes some table's next extents could be larger than the 20% free space left. In those case, that table will be nailed.