SET TERMOUT ON
SET ECHO OFF
SET VERIFY OFF
SET LINESIZE 300
SET PAGESIZE 0

SPOOL 05_STAT_USERS.log

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

CONNECT SYS/&SYSpsw@&Server AS SYSDBA

PROMPT **************************************************************

BEGIN
   SYS.DBMS_STATS.GATHER_SCHEMA_STATS (
     OwnName           => 'MAGICASH5',
     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('MAGICASH5'))
     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('MAGICASH5'))
     AND last_analyzed < SYSDATE - 90
	 AND temporary IN ('N')
ORDER BY owner, index_name;

PROMPT **************************************************************


SPOOL OFF

SET TERMOUT ON
SET ECHO ON
EXIT