CHECK SCHEMA STATS SCRIPT IN ORACLE DATABASE

 scripts]$ cat check_schema_stats.sh

#!/bin/bash

#

#periodic analyze schema cron script

#

# to run

#  analyze_schema <sid> <schema owner>

#

#

#

#

ORACLE_SID=$1

ORAENV_ASK=NO

SCHEMA_OWNER=$2

echo $SCHEMA_OWNER

export ORACLE_SID

export SCHEMA_OWNER

export SCHEMA_PASSWD

ORAENV_ASK=NO

sqlplus -s '/as sysdba' <<eof ;

spool /tmp/analyze_schema_$SCHEMA_OWNER.log

SET SERVEROUTPUT ON

SET TIME ON

SET TIMING ON

SET ECHO ON

SELECT TRUNC(LAST_ANALYZED) LAST_ANALYZED, COUNT(*) TABLES FROM ALL_TABLES WHERE OWNER = '$SCHEMA_OWNER' GROUP BY TRUNC(LAST_ANALYZED) ORDER BY 1;

SELECT TRUNC(LAST_ANALYZED) LAST_ANALYZED, COUNT(*) INDICES FROM ALL_INDEXES WHERE OWNER = '$SCHEMA_OWNER' GROUP BY TRUNC(LAST_ANALYZED) ORDER BY 1;

spool off ;

disconnect

quit;

eof