Installgen
Features and Benefits
Installgen
Demo Available for download...
Bookmark This Page
#!/bin/bash
# script: prod1_schema_stats_job_1.sh (renamed from 56_macosx_920_prod1_schema_stats_job_1.sh)
# Features: This shell script generates sql scripts which are then run
# in order to gather schema statistics for all schemas except SYS and SYSTEM.
#
# Output Files: prod1_dbms_stats_schemas_get_1.sql
# prod1_dbms_stats_gather_schema_stats_1.sql
#
# Script Sequence#: 56
# Used By: CRON job via oracle UNIX account
# Usage:
# ******** Gather Oracle schema CBO stats weekly on Fridays at 9:00PM ********
# 00 21 * * 5 /Volumes/u01/server_scripts/prod1_schema_stats_job_1.sh
# Copyright 2002 by .com Solutions Inc.
#
# ---------------------- Revision History ---------------
# Date By Changes
# 10-9-2001 dsimpson Initial Release
# 05-10-2002 dsimpson Updated to use oracle UNIX account.
# This output file was created by Installgen version 1.38 on Sat Nov 23 07:13:32 2002. By .com Solutions Inc. www.dotcomsolutionsinc.net
# define globally used paths/variables
PATH=/bin:/sbin:/usr/bin:/usr/sbin:.
ORACLE_BASE=/Users/oracle/v920
ORACLE_HOME=/Users/oracle/v920
ORACLE_OWNER=oracle
ORACLE_OWNER_PATH=/Users/oracle
ORACLE_GROUP=dba
PATH=$PATH:$ORACLE_HOME/bin
SRCHOME=$ORACLE_HOME
ORACLE_SID=prod1
CLASSPATH=$ORACLE_HOME/DBCreate/oradev/classes/orapts.jar:$ORACLE_HOME/jdbc/lib/classes12.zip:$CLASSPATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
DYLD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/rdbms/lib:$DYLD_LIBRARY_PATH
ORAENV_ASK=NO
TNS_ADMIN=$ORACLE_HOME/network/admin
TERM=vt100
NLS_LANG=AMERICAN_AMERICA.US7ASCII
export PATH ORACLE_BASE ORACLE_HOME SRCHOME ORACLE_SID CLASSPATH LD_LIBRARY_PATH DYLD_LIBRARY_PATH ORACLE_OWNER TNS_ADMIN TERM NLS_LANG
# the following ulimit parameters are required by the Oracle database on Mac OS X
ulimit -c unlimited
ulimit -d unlimited
ulimit -s 65536
ulimit -u 500
ulimit -n 10000
# write the SQL file which obtains the list of schemas
exec 3>/Volumes/u01/server_scripts/prod1_dbms_stats_schemas_get_1.sql
echo "-- script: prod1_dbms_stats_schemas_get_1.sql">&3
echo "-- Features: This sql script obtains a list of schemas which">&3
echo "-- will get analyzed with dbms_stats.">&3
echo "-- Output File: prod1_dbms_stats_gather_schema_stats_1.sql ">&3
echo "--">&3
echo "-- Copyright 2002 by .com Solutions Inc.">&3
echo "--">&3
echo "-- ---------------------- Revision History ---------------">&3
echo "-- Date By Changes">&3
echo "-- 10-9-2001 dsimpson Initial Release">&3
echo " ">&3
echo "set echo off">&3
echo "set feedback off">&3
echo "set verify off">&3
echo "set pagesize 0">&3
echo "set linesize 250">&3
echo "spool /Volumes/u01/server_scripts/prod1_dbms_stats_gather_schema_stats_1.sql">&3
echo "select '-- script: prod1_dbms_stats_gather_schema_stats_1.sql ' from dual;">&3
echo "select '-- Features: This sql script analyzes all schemas with dbms_stats' from dual;">&3
echo "select '-- except the SYS and SYSTEM schemas. ' from dual;">&3
echo "select '-- ' from dual;">&3
echo "select '-- Used By: executed by 56_macosx_920_prod1_schema_stats_job_1.sh ' from dual;">&3
echo "select '-- ' from dual;">&3
echo "select '-- Copyright 2002 by .com Solutions Inc. ' from dual;">&3
echo "select '-- ' from dual;">&3
echo "select '-- ---------------------- Revision History --------------- ' from dual;">&3
echo "select '-- Date By Changes ' from dual;">&3
echo "select '-- 10-09-2001 dsimpson Initial Release ' from dual;">&3
echo "select '-- 04-30-2002 dsimpson Added exclusion of SYS_IOT_OVER_ tables. ' from dual;">&3
echo "select '-- ' from dual;">&3
echo "select 'set echo on ' from dual;">&3
echo "select '-- analyze tables only to validate structure of the table and its indexes' from dual;">&3
echo "select 'analyze table ' || owner || '.' || table_name || ' validate structure cascade;' from dba_all_tables where owner not in ('SYS','SYSTEM') and table_name not like ('%SYS_IOT_OVER_%');">&3
echo "select '-- generate stats for all schemas except SYS and SYSTEM' from dual;">&3
echo "select 'exec dbms_stats.gather_schema_stats(ownname=>' || CHR(39) || username || CHR(39) || ', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt=>' || CHR(39) || 'AUTO' || CHR(39) || ', degree=>DBMS_STATS.DEFAULT_DEGREE, granularity=>' || CHR(39) || 'DEFAULT' || CHR(39) || ', cascade=>TRUE);' || CHR(10) from dba_users where username not in ('SYS','SYSTEM');">&3
echo "spool off">&3
#su - oracle -c "$ORACLE_HOME/bin/sqlplus /nolog"<< EOF
$ORACLE_HOME/bin/sqlplus /nolog << EOF
connect / as SYSDBA
set echo on
set feedback on
set verify on
set pagesize 24
--exec dbms_stats.gather_schema_stats(ownname=>'OUTLN', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt=>'AUTO', degree=>DBMS_STATS.DEFAULT_DEGREE, granularity=>'DEFAULT', cascade=>TRUE);
-- generate the list of sql commands to generate schema stats for all users
@/Volumes/u01/server_scripts/prod1_dbms_stats_schemas_get_1.sql
-- now gather the stats for all schemas except SYS and SYSTEM using the generated sql code
@/Volumes/u01/server_scripts/prod1_dbms_stats_gather_schema_stats_1.sql
exit;
EOF
