Count rows in all tables or similar tables in database:
I have come across many situations as a DBA when I have to count the total number of rows in a tables in a table across the database. I have written this simple code to help us with that.
If you are a DBA you might also say why not just query the num_rows from all_tables or user_tables. But that is not possible as it might not give you the accurate count, as we all know that you cant get the right numbers with all_tables column, at least not until you have gather the stats for all these tables before you run the query.
----- COUNT ROWS FROM ALL TABLES AND PRINT THEM :
DECLARE
result sys_refcursor;
strTableOwner VARCHAR2(100);
strTableName1 VARCHAR2(100);
strQuery VARCHAR2(4000);
rec NUMBER;
BEGIN
OPEN result FOR SELECT owner,table_name FROM all_tables WHERE table_name IN (
'ABC') order by owner; -->> you can alter this query as per your requirement
LOOP
FETCH
result
INTO
strTableOwner,
strTableName1;
EXIT
WHEN result%notfound;
--- List schema and table being counted
DBMS_OUTPUT.PUT_LINE('count for '||strTableOwner||'.'||strTableName1||' ');
strQuery := 'select count(*) from '||strTableOwner||'.'||strTableName1||' ';
EXECUTE immediate strQuery INTO rec;
DBMS_OUTPUT.PUT_LINE(' >> '||rec||'');
END LOOP;
CLOSE result;
END;
Sample Output :
count for ATOORPU.ABC IS
>> 5
count for SCOTT.ABC IS
>> 1
count for SYS.ABC IS
>> 8
Note :
1) You can edit this plsql as per your requirement, it can count all tables in a schema if change all_tables to user_tables.
2) You can also get the count of different tables by changing the In clause in the cursor.
