Clever Geek Handbook

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.

+3


source to share


2 answers


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

.

+1


source


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

+2


source






More articles:


All Articles