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
Example output:
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.
Gary
22 October 2010 - 7:53 am
Haven’t got an 11gR2 to play with, but what is the ‘cost’ in selecting count(*) from a table with no segments ? I guess the biggest component would be the cost of the parse, but other than that is it a “no logical io” like select 1 from dual ?
Jeffrey Kemp
22 October 2010 - 8:47 am
Hi Gary, you’re right, the cost of doing a count on a segment-less table is virtually nil, even taking into account the cost of parsing the query. In my rough test, counting 119 segment-less tables seems to take approximately 1 hundredth of a second. The “segment created” check is not necessary for performance reasons.