next.sql

/* next.sql */

set heading off
select 'Tables with next extent greater than max free space in tablespace'
" " from dual;

column next_extent format 999,999,999
set heading on

select distinct a.table_name, b.tablespace_name, a.next_extent
from sys.dba_tables a,
	sys.dba_free_space b
where a.tablespace_name = b.tablespace_name and
	a.next_extent> 	(select max(z.bytes)
			from sys.dba_free_space z	
			where z.tablespace_name = b.tablespace_name
			group by z.tablespace_name)
/

set heading off
select 'Maximum amount of free space in tablespace in problem TABLE tablespaces'
" " from dual;

column max(b.bytes) format 999,999,999
set heading on


select b.tablespace_name, max(b.bytes)
from 	sys.dba_tables a,
	sys.dba_free_space b
where a.tablespace_name = b.tablespace_name and
	a.next_extent> 	(select max(z.bytes)
			from sys.dba_free_space z	
			where z.tablespace_name = b.tablespace_name
			group by z.tablespace_name)
group by b.tablespace_name
/

set heading off
select 'Indexes with next extent greater than max free space in tablespace'
" " from dual;

column next_extent format 999,999,999
set heading on

select distinct a.index_name, b.tablespace_name, a.next_extent
from sys.dba_indexes a,
	sys.dba_free_space b
where a.tablespace_name = b.tablespace_name and
	a.next_extent> 	(select max(z.bytes)
			from sys.dba_free_space z	
			where z.tablespace_name = b.tablespace_name
			group by z.tablespace_name)
/

set heading off
select 'Maximum amount of free space in tablespace in problem INDEX tablespaces'
" " from du˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙