Big tables have to be optimized. There is no use of huge tables having no indexes, partitions and other optimization features. Feel free to use this script to locate them 🙂
SELECT ts.owner
, ts.table_name
, ts.num_rows
, ts.last_analyzed
, part.part#
, ind.index#
, t.temporary as gtt_flag
FROM dba_tab_statistics ts
--
LEFT JOIN (
SELECT table_owner
, table_name
, count(partition_name) as part#
FROM dba_tab_partitions
WHERE 1=1
GROUP BY table_owner
, table_name
) part
ON part.table_owner = ts.owner
AND part.table_name = ts.table_name
--
LEFT JOIN (
SELECT table_owner
, table_name
, count(index_name) as index#
FROM dba_indexes
WHERE 1=1
GROUP BY table_owner
, table_name
) ind
ON ind.table_owner = ts.owner
AND ind.table_name = ts.table_name
--
LEFT JOIN dba_tables t
ON t.table_name = ts.table_name
AND t.owner = ts.owner
--
WHERE 1=1
AND ts.num_rows > 1000000 -- big tables
-- no optimization !! (partitions + indexes)
AND ind.index# IS NULL
AND part.part# IS NULL
;
PS: pls keep your statistics updated and adjust the size of the tables (num rows) according to your needs