Pavan DBA's Blog

The DBA Knowledge Store

Script to Analyze schema for Unix (but with analyze command)

Posted by Pavan DBA on March 8, 2012


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

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: