Obtenga recuentos de todas las tablas en un esquema

Estoy tratando de obtener el recuento de registros de todas las tablas en un esquema. Tengo problemas para escribir PL / SQL. Esto es lo que hice hasta ahora, pero estoy recibiendo errores. Por favor sugiera cualquier cambio:

DECLARE v_owner varchar2(40); v_table_name varchar2(40); cursor get_tables is select distinct table_name,user from user_tables where lower(user) = 'SCHEMA_NAME'; begin open get_tables; fetch get_tables into v_table_name,v_owner; INSERT INTO STATS_TABLE(TABLE_NAME,SCHEMA_NAME,RECORD_COUNT,CREATED) SELECT v_table_name,v_owner,COUNT(*),TO_DATE(SYSDATE,'DD-MON-YY') FROM v_table_name; CLOSE get_tables; END; 

Esto debería hacerlo:

 declare v_count integer; begin for r in (select table_name, owner from all_tables where owner = 'SCHEMA_NAME') 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; 

Eliminé varios errores de tu código.

Esto se puede hacer con una statement única y algo de magia XML:

 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 = 'FOOBAR' 
 select owner, table_name, num_rows, sample_size, last_analyzed from all_tables; 

Esta es la forma más rápida de recuperar los conteos de filas, pero hay algunas advertencias importantes:

  1. NUM_ROWS solo es 100% exacto si las estadísticas se recostackron en 11g y superiores con ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE (valor predeterminado) o en versiones anteriores con ESTIMATE_PERCENT => 100 . Consulte esta publicación para obtener una explicación de cómo funciona el algoritmo AUTO_SAMPLE_SIZE en 11g.
  2. Los resultados se generaron a partir de LAST_ANALYZED , los resultados actuales pueden ser diferentes.

Si desea SQL simple para Oracle (p. Ej., Tenga XE sin XmlGen) vaya por un simple paso de 2 pasos:

 select ('(SELECT ''' || table_name || ''' as Tablename,COUNT(*) FROM "' || table_name || '") UNION') from USER_TABLES; 

Copia el resultado completo y reemplaza la última UNIÓN con un punto y coma (‘;’). Luego, como segundo paso, ejecuta el SQL resultante.

Tienes que usar execute immediate (dynamic sql).

 DECLARE v_owner varchar2(40); v_table_name varchar2(40); cursor get_tables is select distinct table_name,user from user_tables where lower(user) = 'schema_name'; begin open get_tables; loop fetch get_tables into v_table_name,v_owner; EXIT WHEN get_tables%NOTFOUND; execute immediate 'INSERT INTO STATS_TABLE(TABLE_NAME,SCHEMA_NAME,RECORD_COUNT,CREATED) SELECT ''' || v_table_name || ''' , ''' || v_owner ||''',COUNT(*),TO_DATE(SYSDATE,''DD-MON-YY'') FROM ' || v_table_name; end loop; CLOSE get_tables; END;