Skip to content
Advertisement

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
Advertisement