Oracle : compter les lignes de toutes les tables d'un schéma
Obtenir le nombre de lignes de chaque table d'un schéma, par bloc PL/SQL avec historique dans une table ou par requête XMLTYPE.
Deux approches pour obtenir le nombre de lignes de chaque table d'un schéma : un bloc PL/SQL qui enregistre les résultats dans une table, et une requête SQL qui utilise XMLTYPE sans bloc PL/SQL.
Méthode 1 : bloc PL/SQL avec table de résultats
Le bloc compte les lignes de chaque table du schéma et insère le résultat dans la table STATS_TABLE, qui doit exister avec les colonnes TABLE_NAME, SCHEMA_NAME, RECORD_COUNT et 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;
Le count(*) n'est pas qualifié par le propriétaire de la table : le bloc compte correctement lorsque le schéma connecté est le propriétaire des tables.
Méthode 2 : requête SQL avec XMLTYPE
dbms_xmlgen.getxml exécute le count(*) de chaque table et renvoie le résultat sous forme de XML, lu ensuite avec extractvalue. Le nom du propriétaire est inclus dans la requête.
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'