Here's a SQL statement that shows all the tables in a given schema and whether they are candidates for the SHRINK clause:

Here's a SQL statement that shows all the tables in a given schema and whether they are candidates for the SHRINK clause:

SELECT dt.owner, dt.table_name,
(CASE
WHEN NVL(ind.cnt, 0) < 1 THEN 'Y'
ELSE 'N'
END) AS can_shrink
FROM dba_tables dt,
(SELECT table_name, COUNT(*) cnt
FROM dba_indexes di
WHERE index_type LIKE 'FUNCTION-BASED%'
GROUP BY table_name) ind
WHERE dt.table_name = ind.table_name(+)
AND dt.table_name NOT LIKE 'AQ$%'
AND dt.table_name NOT LIKE 'BIN$%'
AND dt.owner = 'REGDATA'
ORDER BY 1, 2