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:
x
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