12 Replies Latest reply: Sep 30, 2010 3:29 AM by MichaelS RSS

    How to find number of rows in tables

    690004
      Hi,

      Can you please help me how to know the number of rows in all the tables databsae.

      Thanks In Advance,
        • 1. Re: How to find number of rows in tables
          SankaraNarayanan.M.N
          Hi,
          select tname,count(*) from tab group by tname;
          Regards

          Sankar.M.N
          • 2. Re: How to find number of rows in tables
            MaheshKaila
            Why you need this one?

            Any specific business requirement ?

            Regards,
            Mahesh Kaila
            • 3. Re: How to find number of rows in tables
              Madhu BR
              select ut.num_rows, ut.table_name from user_tables ut
              • 4. Re: How to find number of rows in tables
                SankaraNarayanan.M.N
                You can use this too,
                  select OBJECT_NAME ,count(*) from user_objects where OBJECT_TYPE='TABLE' group by OBJECT_NAME
                
                  
                Regards
                Sankar MN
                • 5. Re: How to find number of rows in tables
                  690004
                  Hi,

                  This query returns the table count,not the row count.

                  Thanks,
                  • 6. Re: How to find number of rows in tables
                    Kanish
                    If you analyzed all table upto date then we can see like this
                    select table_name,num_rows from user_tables
                    kanish
                    • 7. Re: How to find number of rows in tables
                      Prathamesh
                      Hi Sankar,

                      have you tried wht you have posted ???

                      if Statistics are upto the mark then
                      select table_name, num_rows from user_tables; 
                      else
                      SET SERVEROUTPUT ON SIZE 10000000
                      
                      declare
                          V_COUNT NUMBER;
                          cursor c1 is
                              select table_name
                              from    user_tables;
                      begin
                          
                          for c in c1
                          loop
                              execute immediate 'select count(1) from ' || c.table_name into v_count;
                              dbms_output.put_line(c.table_name  || ' - ' || v_count);
                          end loop;  
                      END;
                      • 8. Re: How to find number of rows in tables
                        788713
                        hi Prathamesh

                        incase statistics not uptodate can this also be made with subqueris not using PL/SQL i tried something like

                        select a.table_name, (SELECT COUNT(1) from a.table_name) from dba_tables a ;

                        but struck can't find the logic

                        Edited by: Vinas on Sep 30, 2010 12:10 AM
                        • 9. Re: How to find number of rows in tables
                          Saubhik
                          Datadictionary tables like USER_TABLES in not reliable for row count. Because, even if the statistic is up to date, but after analyzing there may be some DML already altered the row number.
                          A method to see the row count may be like
                          SQL> SELECT
                            2      table_name,
                            3      to_number(
                            4        extractvalue(
                            5           xmltype(
                            6    dbms_xmlgen.getxml('select count(*) c from '||table_name))
                            7           ,'/ROWSET/ROW/C')) count
                            8     FROM user_tables
                            9     WHERE table_name LIKE 'EMP%' --I am just restricting the names, 
                           10                                  --you can remove this to get all tables
                           11     ;
                          
                          TABLE_NAME                          COUNT
                          ------------------------------ ----------
                          EMP                                    14
                          EMPLOYEES                             107
                          EMP_EXTERNAL                            2
                          EMP_LOG                                 1
                          • 10. Re: How to find number of rows in tables
                            MichaelS
                            can this also be made with subqueris not using PL/SQL
                            It can: A relatively simple 11g version can be found in Re: count(*) for all tables.
                            • 11. Re: How to find number of rows in tables
                              788713
                              ok it worked when i use user_tables;

                              but if i want to get from dba_tables or all_tables i get the following error :

                              ORA-19202: Error occurred in XML processing
                              ORA-00942: table or view does not exist
                              ORA-06512: at "SYS.DBMS_XMLGEN", line 176
                              ORA-06512: at line 1

                              Edited by: Vinas on Sep 30, 2010 1:02 AM
                              • 12. Re: How to find number of rows in tables
                                MichaelS
                                Just found out that the behaviour changed in 11.2.0.2:
                                SQL> select * from v$version where rownum = 1
                                /
                                BANNER                                                                          
                                --------------------------------------------------------------------------------
                                Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production          
                                1 row selected.
                                
                                SQL> select owner, table_name, column_value cnt
                                  from (select owner, table_name, 'count(ora:view("' || owner || '","' || table_name || '"))' xq
                                          from all_tables
                                         where table_name in ('EMP', 'DEPT')),
                                       xmltable (xq) order by owner, table_name desc
                                /
                                OWNER                          TABLE_NAME                     CNT   
                                ------------------------------ ------------------------------ ------
                                FLEET                          EMP                            14    
                                FLEET                          DEPT                           4     
                                MICHAEL                        EMP                            14    
                                MICHAEL                        DEPT                           5     
                                SCOTT                          EMP                            14    
                                SCOTT                          DEPT                           4     
                                STEFAN                         EMP                            14    
                                STEFAN                         DEPT                           4     
                                
                                8 rows selected.