Is there a way in Oracle to see which tables have data and which don't?
I want to know if there is a way to search the database and find out which tables are empty and which data. I will be migrating some data to another system and it would be nice to know which tables I should export. I am using Oracle SQL Developer.
One way to do this, besides running the silly pl / sql block before count(*)
for each table, is to do this:
SELECT num_rows FROM ALL_TAB_STATISTICS WHERE OWNER = 'user name';
(Alternative table: DBA_TAB_STATISTICS
, USER_TAB_STATISTICS
)
But then it is only valid if you have recently collected statistics with the package DBMS_STATS
.
Yes, you can select a counter for all tables in the database with a query like
select table_name,
to_number(
extractvalue(
xmltype(
dbms_xmlgen.getxml('select count(*) c from '||table_name))
,'/ROWSET/ROW/C')) count
from user_tables;
Heres a demo