Finding Oracle tablespaces with mixed autoextensible datafiles

Two ways to identify tablespaces where some datafiles are autoextend true and some are false.

(Mixing these can cause issues with monitoring, so try to keep them consistent.)

SELECT DISTINCT a.tablespace_name
FROM dba_data_files a
WHERE a.autoextensible ='YES'
AND EXISTS

(SELECT DISTINCT b.tablespace_name

FROM dba_data_files b
WHERE b.autoextensible ='NO'
AND b.tablespace_name=a.tablespace_name )
ORDER BY tablespace_name;



SELECT  ddf2.tablespace_name
FROM

(SELECT DISTINCT ddf.tablespace_name,ddf.autoextensible
FROM  dba_data_files ddf
GROUP BY ddf.tablespace_name,ddf.autoextensible
) ddf2
GROUP BY ddf2.tablespace_name
HAVING  count(1) = 2
ORDER BY ddf2.tablespace_name;