Get Rows count in Whole Schema

Today I will produce function which returns count of rows in whole schema.

I can do this task using two solutions

#1 Table statistics

Get summation of  NUM_ROWS column from Data Dictionary View USER_TABLES

SELECT SUM (NUM_ROWS) FROM USER_TABLES;

It returns 448900 rows, This is related to my schema.

But this solution has drawback that  NUM_ROWS is related to last time we do analyze tables which may be not equal to current date if  I inserted new rows or deleted old rows have been done

Note : You can update table statistics using below command

ANALYZE TABLE YOUR_SCHEMA.YOUR_TABLE ESTIMATE STATISTICS SAMPLE 33 PERCENT;

#2 Get Count From Tables

I will loop through whole tables and get count rows of them

I create below functions that do this

 CREATE OR REPLACE FUNCTION GET_ROWS_COUNT  
   RETURN NUMBER  
 IS  

   LN$TOTAL_PER_SCHEMA  NUMBER := 0;  
   LN$TOTAL_PER_TABLE  NUMBER := 0;  
   CURSOR LCUR$TABLES  
   IS  
    (SELECT TABLE_NAME FROM USER_TABLES);  

 BEGIN  

   FOR LREC$TABLES IN LCUR$TABLES  
   LOOP  

    BEGIN  
      EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM ' || LREC$TABLES.TABLE_NAME  
       INTO LN$TOTAL_PER_TABLE;  
    EXCEPTION  
      WHEN OTHERS  
      THEN  
       DBMS_OUTPUT.PUT_LINE (DBMS_UTILITY.FORMAT_ERROR_STACK);  
    END;  

    LN$TOTAL_PER_SCHEMA := LN$TOTAL_PER_SCHEMA + LN$TOTAL_PER_TABLE;  
   END LOOP;  

   RETURN LN$TOTAL_PER_SCHEMA;  

 END GET_ROWS_COUNT;  

Now I will test Function GET_ROWS_COUNT Function

SELECT GET_ROWS_COUNT FROM DUAL;

It returns 453369 rows, This is related to my schema. (More than rows in solution #1 )

Rows Count Difference between Two Solutions

                         #1 Table statistics           #2 Get Count From Tables
 Rows Count            448900                                453369

Thanks