Count Rows in each table

Sometimes we what to know which is the table that has more rows.
That could be archived easily by the following query

select table_name, num_rows FROM user_tables ORDER BY num_rows desc nulls last



for all the tables that we have access we could issue the query to all_tables
select owner, table_name, num_rows FROM all_tables ORDER BY num_rows desc nulls last

If you have a comma separated table list like 'EMP,DEP,XTPO' we could use the following procedure that also has a parameter to order by descending row count (R) or by table name (T)

PROCEDURE table_count_rows (p_table_list   IN VARCHAR2
                           ,p_order_by     IN VARCHAR2 DEFAULT 'R')
IS
BEGIN
    FOR c_table
        IN (SELECT ut.table_name, ut.num_rows
              FROM user_tables ut
                  ,(SELECT DECODE (
                               n.num
                              ,1, DECODE (
                                      INSTR (p_table_list, ',')
                                     ,0, p_table_list
                                     ,SUBSTR (p_table_list
                                             ,1
                                             ,INSTR (p_table_list, ',') - 1))
                              ,SUBSTR (p_table_list
                                      ,INSTR (p_table_list
                                             ,','
                                             ,1
                                             ,n.num - 1)
                                       + 1
                                      ,DECODE (INSTR (p_table_list
                                                     ,','
                                                     ,1
                                                     ,n.num)
                                              ,0, LENGTH (p_table_list)
                                              ,  INSTR (p_table_list
                                                       ,','
                                                       ,1
                                                       ,n.num)
                                               - INSTR (p_table_list
                                                       ,','
                                                       ,1
                                                       ,n.num - 1)
                                               - 1)))
                               tname
                      FROM (SELECT LEVEL num
                              FROM DUAL
                            CONNECT BY LEVEL <=
                                           LENGTH (p_table_list)
                                           - LENGTH (
                                                 TRANSLATE (p_table_list
                                                           ,'0,'
                                                           ,'0'))
                                           + 1) n
                     WHERE n.num = 1
                           OR INSTR (p_table_list
                                    ,','
                                    ,1
                                    ,n.num - 1) > 0) b
             WHERE ut.table_name = UPPER (b.tname)
            ORDER BY DECODE (p_order_by, 'T', ut.table_name, NULL)
                    ,DECODE (p_order_by, 'T', 0, ut.num_rows) DESC NULLS LAST)
    LOOP
        DBMS_OUTPUT.put_line (
            c_table.table_name || ' : ' || c_table.num_rows);
    END LOOP;
END table_count_rows;