All record counts against all schemas of oracle 11g

Question

I want to count number of rows for all tables in user-defined schemas. I have got solution related to specific schema counts. But I want to get the following output

 TABLE_NAME   SCHEMA_NAME   ROW_COUNT

The main concern is ALL USER-DEFINED SCHEMAS ONLY. I can't figure out that how to identify that whether it's user schema or system schema. Moreover, when I execute following script

declare
    v_count integer;
begin

    for r in (select table_name, owner from all_tables where owner not in ('SYS','SYSTEM','OUTLN','DBSNMP','APPQOSSYS','WMSYS','CTXSYS','XDB' ) )
    loop
        execute immediate 'select count(*) from ' || r.table_name 
            into v_count;


            DBMS_OUTPUT.PUT_LINE(r.table_name ||v_Count);
        INSERT INTO STATS_TABLE(TABLE_NAME,SCHEMA_NAME,RECORD_COUNT)
        VALUES (r.table_name,r.owner,v_count);
    end loop;

end;

But it returned following error

Error report - ORA-00942: table or view does not exist ORA-06512: at line 7 00942. 00000 - "table or view does not exist" *Cause:
*Action:

Thanks in advance for valuable responses.

When you get to 12c you can use the ORACLE_MAINTAINED column in DBA_USERS. Before that, the USER_ID might give a clue assuming system users were created prior to normal users and so should have a lower number (though upgrades might affect that so check carefully). — William Robertson, Apr 22 '19 at 08:52

Barbaros Özhan · Accepted Answer · 2019-04-22T10:34:59.263

1

Just replace all_tables with user_tables, since you can only query the tables of your current schema without a schema prefix. So, The problem raises due to such tables which your schema doesn't own.

Edit ( after the comment ) : If you want to get all the table's count through the database, you need to log on as a super user such as SYS, SYSTEM, and convert your SQL string to

'select count(*) from '|| r.owner||'.'||r.table_name

and run your script again.

edited Apr 22 '19 at 10:34

answered Apr 22 '19 at 05:53

Barbaros Özhan

59,113
10
31
55

I am having 240 schemas in my database and want to get counts for all of them with single query. – Muhammad Waheed Apr 22 '19 at 07:35
1

A single query or a single procedure? – William Robertson Apr 22 '19 at 08:44

score 1 · Answer 2 · answered Apr 22 '19 at 06:19

1

change your execute immediate statement as below it should work

  execute immediate 'select count(*) from '||r.owner||'.'|| r.table_name 
            into v_count;

answered Apr 22 '19 at 06:19

Tamil

141
2

no it didn't work – Muhammad Waheed Apr 22 '19 at 07:50
then the logged in user doesnt have access on that owner schema.... get access for the user, or have user like apps, sysadmin for entire access – Tamil Apr 22 '19 at 08:14

All record counts against all schemas of oracle 11g

2 Answers2