Ottieni i conteggi di tutte le tabelle in uno schema

Sto cercando di ottenere il numero di record di tutte le tabelle in uno schema. Sto avendo problemi a scrivere il PL / SQL. Ecco cosa ho fatto finora, ma sto ricevendo errori. Si prega di suggerire eventuali modifiche:

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; 

Questo dovrebbe farlo:

 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; 

Ho rimosso vari bug dal tuo codice.

Questo può essere fatto con una singola istruzione e qualche 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; 

Questo è il modo più veloce per recuperare i conteggi delle righe, ma ci sono alcuni avvertimenti importanti:

  1. NUM_ROWS è accurato al 100% solo se le statistiche sono state raccolte in 11 ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE superiori con ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE (valore predefinito) o nelle versioni precedenti con ESTIMATE_PERCENT => 100 . Vedi questo post per una spiegazione di come funziona l’algoritmo AUTO_SAMPLE_SIZE in 11g.
  2. I risultati sono stati generati a partire da LAST_ANALYZED , i risultati attuali potrebbero essere diversi.

Se vuoi un semplice SQL per Oracle (ad es. XE senza XmlGen) vai per un semplice passo 2:

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

Copia l’intero risultato e sostituisci l’ultimo UNION con un punto e virgola (‘;’). Quindi, come secondo passaggio, eseguire l’SQL risultante.

Devi usare 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;