Oracle : Script to Analyze schema for Unix

######################

ANALYZE SCHEMAS IN UNIX

#######################


#!/bin/ksh

ORACLE_SID=ORVIT8QA

export ORACLE_SID

ORACLE_HOME=/d001/oracle/9.2.0.8-64

export ORACLE_HOME

export EXPORT_FILE

LD_LIBRARY_PATH=/d001/oracle/9.2.0.8-64/bin:/d001/oracle/9.2.0.8-64/network/lib:/usr/openwin/lib:/usr/dt/lib

export LD_LIBRARY_PATH

PATH=/d001/oracle/9.2.0.8-64/bin:/bin:/usr/bin:/usr/sbin:/usr/ccs/bin:/usr/local/bin:/usr/ucb:/usr/openwin/bin:

export PATH

SQL_DIR=/d002/oracle/$ORACLE_SID/sql

export SQL_DIR

LOG_FILE=$SQL_DIR/gen_analyze_tables.log
export LOG_FILE

SQL_FILE=$SQL_DIR/gen_analyze_tables.sql
export SQL_FILE



rm $LOG_FILE



gen_analyze_tables.sql Script

______________________________

set termout off

set echo off

set feedback off

set heading off

set linesize 150

set pagesize 0

set space 0

spool analyze_tables.sql
select

‘analyze table ‘||owner||’.’||table_name||’ compute statistics;’

from dba_tables

where owner not like ‘SYS%’ and owner <> ‘MASTER_LOOKUP’ and owner <> ‘PRICING’

and table_name <> ‘MANAGETAX’

order by owner, table_name;

spool analyze_tables.log
@analyze_tables.sql
spool off

exit