Identifying nonempty tables in Oracle SQL Developer

Question

I am trying to find data in a database with hundreds of tables. I am just going through and writing simple select * queries for all of the tables checking them one by one. It is taking forever! I was wondering if there is a way to write a query to filter all of the tables with at least 1 row in the database. Is this possible?

Answer 1

with 
function f(tabname in varchar2) return int as
  res int;
begin
  execute immediate 'select count(*) cnt from "'||tabname||'" where rownum=1' into res;
  return res;
end;
select table_name, f(table_name) chk
from user_tables;

Results:

TABLE_NAME                            CHK
------------------------------ ----------
STRVALS                                 1
MY_EMP                                  1
TBL_3                                   1
TBL_1                                   1
TLOCK                                   1
DATES                                   1
B                                       1
A                                       1
C                                       1
T0                                      0

Or select count(*) from dual where exists(...):

with 
function f(tabname in varchar2) return int as
  res int;
begin
  execute immediate 'select count(*) cnt from dual where exists(select 1 from "'||tabname||'")' into res;
  return res;
end;
select table_name, f(table_name) chk
from user_tables

Answer 2

If your database is being properly maintained, you'll have optimizer statistics.

If you tables are missing stats, you can collect them thusly:

BEGIN
        dbms_stats.gather_schema_stats(
                                      ownname => 'HR'
                                    , estimate_percent => X -- note the higher you go here, the more work will be done
        );
    END;

Then, QUERY THE STATISTICS, not your tables.

SELECT num_rows
     , table_name
  FROM sys.dba_all_tables
 WHERE owner      = 'HR'

You can then QUICKLY see what tables are empty or not - based on how accurate your stats are.

If you use dynamic SQL to run a SELECT COUNT(*) on every table in your schema, and your schema and/or tables are HUGE, you are going to have a very bad experience, and perhaps cause performance issues for your database.