REM ****************
REM dbms_stats 1.3
REM ****************

SET TERMOUT ON
SET ECHO OFF

SPOOL dbms_stats.log

ACCEPT Server PROMPT 'Enter DATABASE name (default - MAGICASH): ' DEFAULT MAGICASH
ACCEPT SYSpsw PROMPT 'Enter SYS password: ' DEFAULT NCTSYS HIDE
ACCEPT MGUSER PROMPT 'Enter USER name (default - MAGICASH5): ' DEFAULT MAGICASH5

CONNECT SYS/&SYSpsw@&Server AS SYSDBA

PROMPT *** STATS &MGUSER   *** 

BEGIN
   SYS.DBMS_STATS.GATHER_SCHEMA_STATS (
     OwnName           => '&MGUSER',
     Cascade           => TRUE);
END;
/

 

PROMPT *** Проверка статистики ***
REM Смотрим важные только, на временные таблицы не обращаем внимания

COLUMN table_name       FORMAT A36            HEADING "TABLE NAME"
COLUMN last_analyzed    FORMAT DATE           HEADING "LAST_ANALYZED"
SELECT   table_name, last_analyzed
    FROM dba_tables
   WHERE owner IN (UPPER('&MGUSER'))
     AND last_analyzed < SYSDATE - 90
	 AND temporary IN ('N')
ORDER BY owner, table_name;

COLUMN index_name       FORMAT A36            HEADING "INDEX NAME"
COLUMN last_analyzed    FORMAT DATE           HEADING "LAST_ANALYZED"
SELECT   index_name, last_analyzed
    FROM dba_indexes
   WHERE owner IN (UPPER('&MGUSER'))
     AND last_analyzed < SYSDATE - 90
	 AND temporary IN ('N')
ORDER BY owner, index_name;

SPOOL OFF
SET TERMOUT ON
SET ECHO ON
EXIT