Get count of all table records individually from each schema in a database

In my oracle database, say there are 3 schemas A,B,C.

Say in each schema, i have exact three tables P,Q,R.

how to get the count of P,Q,R table records from all 3 schemas like below output into a seperate table?
Select * from this seperate table should give counts of all tables from all schemas in a database.

in my requirement, there are more than 1000 schemas each having more than 100 tables , but the count of tables and table names , table column types exactly are same in each schema

In order to do that in a single run, you have to have access to all users being involved. One option is to use a privileged user (such as sys) and do something like this:

SQL> show user
USER is "SYS"
SQL> set serveroutput on

Tables shared by users scott and mike in my database (you'd use P, Q and R, if you're absolutely sure that table set is equal, you can simplify the query):

SQL> select table_name
  2  from dba_tables
  3  where owner = 'SCOTT'
  4  intersect
  5  select table_name
  6  from dba_tables
  7  where owner = 'MIKE';

TABLE_NAME
--------------------------------------------------------------------------------
SALGRADE
EMP
DEPT
TEST
BONUS
TABLESAMPLE

6 rows selected.

PL/SQL procedure which uses dynamic SQL to count rows in each table in each schema. I'm simply displaying the result; you'd probably rather want to store the result into some table for future analysis.

SQL> declare
  2    l_str varchar2(200);
  3    l_cnt_1 number;
  4    l_cnt_2 number;
  5  begin
  6    dbms_output.put_Line(rpad('TABLE NAME', 30, ' ') || '   SCOTT  ' || '  MIKE   ');
  7    for cur_r in (select table_name
  8                  from dba_tables
  9                  where owner = 'SCOTT'
 10                  intersect
 11                  select table_name
 12                  from dba_tables
 13                  where owner = 'MIKE'
 14                 )
 15    loop
 16      l_str := 'select count(*) from scott.' || cur_r.table_name;
 17      execute immediate l_str into l_cnt_1;
 18      l_str := 'select count(*) from mike.' || cur_r.table_name;
 19      execute immediate l_str into l_cnt_2;
 20
 21      dbms_output.put_line(rpad(cur_r.table_name, 30, ' ') ||
 22                           to_char(l_cnt_1, '999G990')     ||
 23                           to_char(l_cnt_2, '999G990'));
 24    end loop;
 25  end;
 26  /
TABLE NAME                       SCOTT    MIKE
SALGRADE                             5       5
EMP                                 14      14
DEPT                                 4       4
TEST                                 2       0
BONUS                                0       0
TABLESAMPLE                          0       2

PL/SQL procedure successfully completed.

SQL>

Alternatively, if you regularly gather statistics and tables aren't updated frequently (rows inserted/deleted), you could check num_rows column value in dba_tables view:

SQL> select owner, table_name, num_rows
  2  from dba_tables
  3  where table_name in (select table_name from dba_tables where owner = 'SCOTT'
  4                       intersect
  5                       select table_name from dba_tables where owner = 'MIKE')
  6  order by table_name, owner;

OWNER           TABLE_NAME                       NUM_ROWS
--------------- ------------------------------ ----------
MIKE            BONUS                                   0
SCOTT           BONUS                                   0
MIKE            DEPT                                    4
SCOTT           DEPT                                    4
MIKE            EMP                                    14
SCOTT           EMP                                    14
MIKE            SALGRADE                                5
SCOTT           SALGRADE                                5
MIKE            TABLESAMPLE                             1
SCOTT           TABLESAMPLE                             0
MIKE            TEST                                    0
SCOTT           TEST                                    2

12 rows selected.

SQL>

Most straightforward:

SELECT 'A' as schemaname,
       (SELECT count(*) FROM A.P) as P,
       (SELECT count(*) FROM A.Q) as Q,
       (SELECT count(*) FROM A.R) as R
 UNION ALL
SELECT 'B' as schemaname,
       (SELECT count(*) FROM B.P) as P,
       (SELECT count(*) FROM B.Q) as Q,
       (SELECT count(*) FROM B.R) as R
 UNION ALL
SELECT 'C' as schemaname,
       (SELECT count(*) FROM C.P) as P,
       (SELECT count(*) FROM C.Q) as Q,
       (SELECT count(*) FROM C.R) as R;

With more schemas and >100 tables you probably want to programmatically generate the query. It's probably easier with something like Python than SQL or PL/SQL.

schemas = ["A", "B", "C"]
tables = ["P", "Q", "R"]
" UNION ALL ".join([f"SELECT '{s}' as schemaname, " + ", ".join([f"(SELECT count(*) FROM {s}.{t}) as {t}" for t in tables]) for s in schemas]