When you are investigating performance or space issues, it is useful to display each table’s row count. To calculate row counts manually, you would write a query such as this for each table that you own:

SQL> select count(*) from <table_name>;

Manually crafting the SQL is time-consuming and error prone. In this situation, it is more efficient to use SQL to generate the SQL required to solve the problem.

To that end, this next example dynamically selects the required text, based on information in the DBA_TABLES view. Create a SQL script file named tabcount_generator.sql with the following code. In SQL*Plus, run that script as a DBA-privileged user with a command like @tabcount_generator.sql.

The script prompts you for a username each time, generates a new filename named tabcount_<user>.sql that contains all the necessary SELECT COUNT(*) commands for that schema, and then runs that new script and displays the table names and row counts.

UNDEFINE user

SPOOL tabcount_&&user..sql

SET LINESIZE 132 PAGESIZE 0 TRIMSPOOL OFF VERIFY OFF FEED OFF TERM OFF SELECT

‘SELECT RPAD(‘ || ””  || table_name ||””||’,30)’

|| ‘,’ || ‘ COUNT(1) FROM &&user..’ || table_name || ‘;’ FROM dba_tables

WHERE owner = UPPER(‘&&user’) ORDER BY 1;

SPOOL OFF; SET TERM ON

@@tabcount_&&user..sql SET VERIFY ON FEED ON

If the username you provide to the script is INVUSER, then you can manually run the generated script as follows:

SQL> @tabcount_invuser.sql

Keep in mind that if the table row counts are high, this script can take a long time to run (several minutes).

Developers and DBAs often use SQL to generate SQL statements. This is a useful technique when you need to apply the same SQL process (repetitively) to many differentobjects, such as all tables in a schema. If you do not have access to DBA-level views, you can query the USER_TABLES view; for example,

SPOOL tabcount.sql

SET LINESIZE 132 PAGESIZE 0 TRIMSPO OFF VERIFY OFF FEED OFF TERM OFF SELECT

‘SELECT RPAD(‘ || ”” || table_name || ”” ||’,30)’ || ‘,’ || ‘ COUNT(*) FROM ‘ || table_name || ‘;’

FROM user_tables ORDER BY 1; SPOOL OFF;

SET TERM ON @@tabcount.sql

SET VERIFY ON FEED ON

If you have accurate statistics, you can query the NUM_ROWS column of the CDB/ DBA/ALL/USER_TABLES views. This column normally has a close row count if statistics are generated on a regular basis. The following query selects NUM_ROWS from the USER_ TABLES view:

SQL> select table_name, num_rows from user_tables;

One final note: if you have partitioned tables and want to show row counts by partition, use the next few lines of SQL and PL/SQL code to generate the SQL required:

UNDEFINE user

SET SERVEROUT ON SIZE 1000000 VERIFY OFF SPOOL part_count_&&user..txt

DECLARE

counter NUMBER;

sql_stmt VARCHAR2(1000); CURSOR c1 IS

SELECT table_name, partition_name FROM dba_tab_partitions

WHERE table_owner = UPPER(‘&&user’); BEGIN

FOR r1 IN c1 LOOP

sql_stmt := ‘SELECT COUNT(1) FROM &&user..’ || r1.table_name ||’ PARTITION ( ‘||r1.partition_name ||’ )’;

EXECUTE IMMEDIATE sql_stmt INTO counter; DBMS_OUTPUT.PUT_LINE(RPAD(r1.table_name ||'(‘||r1.partition_name||’)’,30) ||’ ‘||TO_CHAR(counter)); END LOOP; END; / SPOOL OFF