Analyse de toutes les tables et/ou des indexes

Pour analyser toutes les tables ou indexes d'un schéma, utiliser le script ci-dessous dans sqlplus. Pour les tables :

set pages 9999;
set heading off;
set feedback off;
set echo off;
set verify off;

column bname new_value dbname noprint
column hname new_value hstname noprint
select name as bname from v$database;
select host_name as hname from v$instance;

spool analyze_tables_&dbname._&hstname..sql;

prompt set echo on;
prompt set feedback on;
prompt spool analyze_tables_&dbname._&hstname..log;
select 'analyze table "'||owner||'"."'||table_name||'" validate structure;' from dba_tables
where
owner not in ('SYS','SYSTEM');

prompt spool off;

spool off;

@analyze_tables_&dbname._&hstname

set heading on;
set feedback on;
set echo on;
set verify on;

Pour les indexes utiliser ce même script en remplaçant :


Revision #3
Created 14 April 2022 16:32:53 by Frank
Updated 2 May 2024 12:05:24 by Frank