0

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.

Schemaname P Q R
A. 5 6 7
B. 2 5 4
C. 8 6 4

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

Srihari
  • 2,509
  • 5
  • 30
  • 34
  • 1
    An output where the same entities are stored as different columns is just a visual sugar that would be better to address to the data visualization tool (any spreadsheet app can do this easily). – astentx Jun 11 '23 at 16:59

2 Answers2

1

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>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • Thanks @littlefoot for your response, in my case schema's are also dynamic , there is another table which have schema details like db name. I think there should be 2 for loops running, outer for loop for schemas and inner for loop for all tables , please advise i modified question accordingly – Srihari Jun 11 '23 at 17:26
  • Sure, adjust it the way you find the most appropriate. Test on a small set of users and tables; once you're satisfied with the outcome, apply it to the whole set. – Littlefoot Jun 11 '23 at 19:55
1

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]
Kombajn zbożowy
  • 8,755
  • 3
  • 28
  • 60