LLI Wiki — My Digital Knowledge Base

Oracle: count the rows of all tables in a schema

Get the row count of each table in a schema, with a PL/SQL block that stores the history in a table or with an XMLTYPE query.

Two ways to get the number of rows of each table in a schema: a PL/SQL block that stores the results in a table, and a SQL query that uses XMLTYPE without a PL/SQL block.

Method 1: PL/SQL block with a results table

The block counts the rows of each table of the schema and inserts the result into the table STATS_TABLE, which must exist with the columns TABLE_NAME, SCHEMA_NAME, RECORD_COUNT and CREATED.

declare
    v_count integer;
begin

    for r in (select table_name, owner from all_tables
              where owner = 'APP_SCHEMA')
    loop
        execute immediate 'select count(*) from ' || r.table_name
            into v_count;
        INSERT INTO STATS_TABLE(TABLE_NAME,SCHEMA_NAME,RECORD_COUNT,CREATED)
        VALUES (r.table_name,r.owner,v_count,SYSDATE);
    end loop;

end;

The count(*) is not qualified with the table owner: the block gives correct counts when the connected schema owns the tables.

Method 2: SQL query with XMLTYPE

dbms_xmlgen.getxml runs the count(*) of each table and returns the result as XML, which extractvalue then reads. The owner name is included in the query.

select table_name,
       to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from '||owner||'.'||table_name)),'/ROWSET/ROW/C')) as count
from all_tables
where owner = 'APP_SCHEMA'
Mastodon LLIMastodon SocialFosstodon