ORACLE – ANALYZE TABLE et ANALYZE INDEX en un seul script

SPOOL c:analyze_tab.lis

SELECT 'ANALYZE TABLE "' || OWNER || '"."' || TABLE_NAME || '" ESTIMATE STATISTICS;' AS " "
FROM ALL_TABLES
WHERE OWNER IN ('##ORACLE_SCHEMA##');

SELECT 'ANALYZE TABLE "' || OWNER || '"."' || TABLE_NAME || '" ESTIMATE STATISTICS FOR ALL COLUMNS;' AS " "
FROM ALL_TABLES
WHERE OWNER IN ('##ORACLE_SCHEMA##');

SELECT 'ANALYZE TABLE "' || OWNER || '"."' || TABLE_NAME || '" ESTIMATE STATISTICS FOR ALL INDEXES;' AS " "
FROM ALL_TABLES
WHERE OWNER IN ('##ORACLE_SCHEMA##');

SPOOL OFF;

SPOOL c:analyze_ind.lis

SELECT 'ANALYZE INDEX "' || TABLE_OWNER || '"."' || INDEX_NAME || '" ESTIMATE STATISTICS;' AS " "
FROM DBA_INDEXES
WHERE OWNER IN ('##ORACLE_SCHEMA##');

SELECT 'EXIT' AS " " FROM DUAL;

SPOOL OFF;

SPOOL c:analyze_tab.log

@analyze_tab.lis

SPOOL OFF;

SPOOL c:analyze_ind.log

@analyze_ind.lis

SPOOL OFF;