Row count of all tables in a particular schema

DBA’s would have definitely come across a situation, where in, we would need the row count of all tables in schema, It could be for validation purpose after an oracle to SQL Server migration as was my case. Here is some code snippet which helped me to accomplish the requirement.

1)

DECLARE
CURSOR C1 IS SELECT owner,TABLE_NAME FROM DBA_TABLES WHERE owner=’NIKU’;
l_query varchar2(300);
l_num pls_integer;
BEGIN
FOR REC IN C1 LOOP
l_query := ‘SELECT COUNT(*) FROM ‘ || REC.owner||’.’||REC.TABLE_NAME;
EXECUTE IMMEDIATE l_query INTO l_num;
dbms_output.put_line(REC.owner||’.’||REC.TABLE_NAME||’:’||l_num);
END loop;
exception
WHEN others THEN
dbms_output.put_line(sqlerrm);
END;
/

2)

Per MOS Note: 335498.1

SET SERVEROUTPUT ON;
DECLARE
CURSOR c_table_names IS
SELECT table_name FROM user_tables order by 1;
no_of_rows NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Table Name’ || CHR(9) || ‘Number of Rows’);
DBMS_OUTPUT.PUT_LINE(‘———-‘ || CHR(9) || ‘————–‘);
FOR tname IN c_table_names LOOP
EXECUTE IMMEDIATE ‘SELECT COUNT(*) FROM ‘ || tname.table_name INTO no_of_rows;
DBMS_OUTPUT.PUT_LINE(tname.table_name || CHR(9) || CHR(9) || no_of_rows);
END LOOP;
END;
/

3)

Per MOS Note: 335498.1 { This method is not recommended by oracle as it uses tab, which is being retained for backward compatibility.

SET SERVEROUTPUT ON;
DECLARE
no_of_rows NUMBER;
no_of_tables NUMBER;
table_name VARCHAR2(100);
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Table Name’ || CHR(9) || ‘Number of Rows’);
DBMS_OUTPUT.PUT_LINE(‘———-‘ || CHR(9) || ‘————–‘);
EXECUTE IMMEDIATE ‘SELECT COUNT(*) FROM tab’ INTO no_of_tables;
FOR table_number IN 1..no_of_tables
LOOP
EXECUTE IMMEDIATE ‘SELECT tname FROM tab WHERE ROWNUM <= ‘ || table_number || ‘ MINUS SELECT tname FROM tab WHERE ROWNUM < ‘ || table_number INTO table_name;
EXECUTE IMMEDIATE ‘SELECT COUNT(*) FROM ‘ || table_name INTO no_of_rows;
DBMS_OUTPUT.PUT_LINE(table_name || CHR(9) || CHR(9) || no_of_rows);
END LOOP;
END;
/

4)

You can also use DBA_TABLES view to check the number if rows visible to optimizer post gathering stats, because optimizer uses the data visible in dictionary rather actual data. If there is huge change in number of actual rows and the data available to optimizer then the the queries may run longer than usual.

select OWNER,TABLE_NAME,NUM_ROWS from dba_tables where owner='&sschema_name' order by 2;

——————————————————————————–

Note:
You might need to tweak it according to your needs.

Hope this helps,