Count All Rows in a Schema
I wrote this simple SQL*Plus script to loop through all the user’s tables and report how many rows were in them at this point in time. Normally I might estimate this information based on the table statistics, but in this case I had to know if any rows had been inserted since the last stats were gathered. Also, I preferred not to just run the stats gathering job. Obviously, it doesn’t pick up any uncommitted data.
It doesn’t list the tables which have zero (0) rows, but it report how many tables it counted, and the total number of rows across the entire schema.
If run on an 11g database, it will skip the count on tables which have had no segment created yet.
WARNING: this script is suitable for development environments only. Use at your own risk.
SET SERVEROUT ON FEEDBACK OFF DECLARE c INTEGER; tc INTEGER := 0; tr INTEGER := 0; BEGIN $IF DBMS_DB_VERSION.VER_LE_10 $THEN DBMS_OUTPUT.enable(1000000); $ELSE DBMS_OUTPUT.put_line('Only tables with segment created are counted.'); $END FOR r IN ( SELECT table_name FROM user_tables $IF DBMS_DB_VERSION.VER_LE_10 $THEN $ELSE WHERE segment_created = 'YES' $END ORDER BY table_name ) LOOP tc := tc + 1; BEGIN EXECUTE IMMEDIATE REPLACE( 'SELECT COUNT(*) FROM "#TABLE#"' ,'#TABLE#',r.table_name) INTO c; tr := tr + c; IF c > 0 THEN DBMS_OUTPUT.put_line( RPAD(r.table_name,31,' ') || TO_CHAR(c,'9G999G999G990')); END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line(r.table_name); DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack); END; END LOOP; DBMS_OUTPUT.put_line('counted ' || tc || ' table(s)'); DBMS_OUTPUT.put_line('total rows ' || TO_CHAR(tr,'9G999G999G990')); END; / SET SERVEROUT OFF FEEDBACK ON
SQL> @count_all_tables Only tables with segment created are counted. counted 1 table(s) total rows 0
This tells me that there are no rows yet, and of all the tables, only one has had a segment created for it (e.g. perhaps an INSERT was attempted but rolled back, or the row was deleted). I’m sure you’ll be able to adapt the script to suit your obsessive record-counting needs.