REM      INDICATEUR SUR L ACCROISSEMENT DU NOMBRE DE LIGNES DES TABLES 
REM ------------------------------------------------------------------------
SET FLUSH OFF
SET TERM OFF
SET LINESIZE 100
SET PAGES 100 
REM
DROP index SYSTEM.ZZ_TEMP_HISTO_TABLEIDX;
LOCK TABLE SYSTEM.ZZ_TEMP_SEQUENCE IN ROW EXCLUSIVE MODE;
UPDATE SYSTEM.ZZ_TEMP_SEQUENCE
set numseq=numseq + 1;
INSERT into SYSTEM.ZZ_TEMP_HISTO_TABLE
SELECT
      c.numseq , 
      to_char (sysdate,'IW') , 
      a.tablespace_name, 
      a.table_name, 
      a.num_rows,
      b.num_rows,
      a.num_rows - b.num_rows,
      b.semaine_id
FROM dba_tables a, sys.dual, system.ZZ_TEMP_HISTO_TABLE b, system.ZZ_TEMP_SEQUENCE c
where a.owner='BAAN' 
and b.tablespace_name=a.tablespace_name   
and b.table_name=a.table_name
and b.numseq=c.numseq - 1
;
COMMIT
;
CREATE INDEX system.zz_temp_histo_tableidx 
ON system.zz_temp_histo_table (numseq,tablespace_name,table_name)
tablespace ERP_TS_TOOLS
storage (initial 5M next 1M
         minextents 1 maxextents 4);  
REM
set pages 100
set linesize 100
column numseq          heading 'Seq' format 999
column semaine_id      heading 'Semaine' format 99
column tablespace_name heading 'Tablespace' format a20
column table_name      heading 'Table' format a20
column num_rows        heading 'Nb rows' 
column num_rows_pre    heading 'Nb rows pre' 
column ecart           heading 'Ecart'
column semaine_pre     heading 'Semaine pre'
column HOST_NAME new_value host

select HOST_NAME from v$INSTANCE;

PROMPT   " ETAT SUR L ACCROISSEMENT DU NOMBRE DE LIGNES DES TABLES"
define output = './REPORTS/indicateur7.lis'
spool &output
SELECT
      a.numseq, 
      semaine_id, 
      tablespace_name, 
      table_name, 
      num_rows,
      num_rows_pre,
      ecart,
      semaine_pre 
FROM system.ZZ_TEMP_HISTO_TABLE a,sys.dual,system.ZZ_TEMP_SEQUENCE b 
where     num_rows > 0 
and       a.numseq=b.numseq 
ORDER BY  ecart desc, num_rows desc, semaine_id, tablespace_name, table_name;
spool off
clear columns
SET FLUSH ON
SET TERM ON
exit