Search This Blog

Thursday, December 26, 2013

Trying to get row counts for all tables at a time

You can use any of this
select
      t.owner,
      t.table_name,
      to_number(
        extractvalue(
          xmltype(dbms_xmlgen.getxml('select count(*) c from '||t.owner || '.' || t.table_name))
          ,'/ROWSET/ROW/C')
          )
          count
    from all_tables t,user_tab_privs p
    where t.owner = p.owner
      and t.table_name = p.table_name
      AND privilege = 'SELECT'
      
union all
select
      user,
      t.table_name,
      to_number(
        extractvalue(
          xmltype(dbms_xmlgen.getxml('select count(*) c from '||t.table_name))
          ,'/ROWSET/ROW/C')
          )
          row_count
    FROM user_tables t;
/

SELECT owner,table_name, num_rows 
from ALL_TABLES 
WHERE num_rows>0 
AND owner = 'OWNER_NAME'
ORDER BY num_rows DESC;

No comments: