Skip to content

getting number of columns and rows in my tables

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 
User contributions licensed under: CC BY-SA
10 People found this is helpful