@ECHO OFF
COLOR 3E
ECHO ************************************************************
ECHO                 Variablen Deklarieren
ECHO ************************************************************
for /f "tokens=1 delims=;" %%a in ('findstr "#u#" conf.txt') do set us=%%a
set su=%us:#u#=%
for /f "tokens=1 delims=;" %%a in ('findstr "#p#" conf.txt') do set ps=%%a
set sp=%ps:#p#=%
for /f "tokens=1 delims=;" %%a in ('findstr "#d#" conf.txt') do set db=%%a
set bd=%db:#d#=%
set errs=Erfolgreich
set bzeit=%time:~0,8%
set logdatei=analysestat.log
set tmplog=lg1.log
ECHO ************************************************************
ECHO                 ANALYSE Beginn regestrieren
ECHO ************************************************************
for /F "delims=" %%a in (%logdatei%) do echo %%a >> %tmplog%
echo ANALYSE Beginn: %su% %date% %bzeit% > %logdatei%
echo ################################## ANALYSE %su% BEG ############################# >> %logdatei%
for /F "delims=" %%a in (%tmplog%) do echo %%a >> %logdatei%
del %tmplog% /q
ECHO ************************************************************
ECHO                 ANALYSE Beginn
ECHO ************************************************************
sqlplus %su%/%sp%@%bd% @cstattab.sql
sqlplus %su%/%sp%@%bd% @compstat.sql
ECHO ************************************************************
ECHO                 ANALYSE Result ermitteln
ECHO ************************************************************
for /f %%i in ('sqlplus -s %su%/%sp%@%bd% @cstattab.sql') do @set count=%%i
for /f "tokens=1 delims=-" %%a in ("%count%") do set err=%%a
IF "%err%" == "ORA" goto :ORAERR
IF "%err%" == "OSERR" goto :OSERR
goto :ERFOLG
:ORAERR
set put=# Oracle Error bei Analyse Tabellen Schema %su%
goto :RESULT
:OSERR
set put=# Betriebsystem Error bei Analyse Tabellen Schema %su%
goto :RESULT
:ERFOLG
set put=# %count% Tabellen Schema %su% %errs% Analysiert
goto :RESULT
if errorlevel 1 goto :error
goto :RESULT
:error
set put=# Batchscript Error bei Analyse Tabellen Schema %su%
ECHO ************************************************************
ECHO                 ANALYSE Result, Ende regestrieren
ECHO ************************************************************
:RESULT
REM Zeit END ermitteln:
set ezeit=%time:~0,8%
REM ANALYSE Result, Ende regestrieren:
for /F "delims=" %%a in (%logdatei%) do echo %%a >> %tmplog%
echo ################################## ANALYSE %su% END ############################# > %logdatei%
echo ANALYSE Ende: %su% %date% %ezeit% >> %logdatei%
echo %put% >> %logdatei%
for /F "delims=" %%a in (%tmplog%) do echo %%a >> %logdatei%
del %tmplog% /q
REM pause
exit

    

SET echo off;
SET heading off;
SET define off;
SET feedback off;
SET verify off;
set tab off;
set trimout off;
set trims off;
SET serveroutput on;
SET PAGESIZE 0
SET LINESIZE 120
WHENEVER SQLERROR EXIT SQL.SQLCODE
WHENEVER OSERROR EXIT OSERR
SPOOL compstat.sql
select 'ANALYZE TABLE '||t.OWNER||'.'||t.table_name||' COMPUTE STATISTICS; ' from sys.all_tables t
where t.OWNER=(SELECT sys_context('USERENV', 'CURRENT_SCHEMA') FROM DUAL);
SELECT TO_CHAR('exit;') FROM DUAL;
SPOOL OFF
SELECT TRIM (count(*)) from sys.all_tables t where t.OWNER=(SELECT sys_context('USERENV', 'CURRENT_SCHEMA') FROM DUAL);
SET define on;
SET feedback on;
SET heading on;
SET verify on;
set tab on;
set trimout on;
set trims on;
exit;

    

#u#Username
#p#Password
#d#Database