How to get the number of columns and rows for each of my tables in oracle?
So far I know how to display the number of columns for each table:
select table_name, count(*) from all_tab_columns where owner = 'user' group by table_name
Advertisement
Answer
SELECT T.owner, t.table_name, t.num_rows, --it depends analyze (gather statics etc.) we can't depend it. To_number(Extractvalue(Xmltype( dbms_xmlgen.Getxml('select count(*) c from ' ||table_name)), '/ROWSET/ROW/C')) real_count, (SELECT Count(*) FROM all_tab_columns C WHERE C.table_name = T.table_name) COLUMN_COUNT FROM all_tables t WHERE OWNER='user' --and T.table_name = 'MYTABLE' GROUP BY t.owner, t.table_name, t.num_rows