The sql script (below) does stats on everything in the database and then
deletes them from sys and system. My boss wanted it that way so that if any
schemas get added, we won't have to worry about forgetting to do stats on
them.
whenever sqlerror exit 1
set timing on
exec dbms_stats.gather_database_stats(estimate_percent=>20,cascade=>true);
exec dbms_stats.delete_schema_stats('SYS');
exec dbms_stats.delete_schema_stats('SYSTEM');
set timing off
exitFrom: "Mercadante, Thomas F (LABOR)" <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>
To: <cemail_219@xxxxxxxxxxx>,<oracle-l@xxxxxxxxxxxxx>
Subject: RE: Automate analyze - can't log in Date: Mon, 29 Aug 2005 11:46:56 -0400
J. Dex,
Have you tried running this on the command line? My guess is that logging in as SYS is giving you a problem.
You need to connect as sysdba or connect as the schema owner. I would use the schema owner - there is no real reason to use SYS to gather these stats.
Tom
-----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of J. Dex Sent: Monday, August 29, 2005 11:39 AM To: oracle-l@xxxxxxxxxxxxx Subject: Automate analyze - can't log in
I am trying to automate analyze through cron. The shell script is unable to login to Oracle and I am still not sure why. Is there an easier way to do this or can someone tell what I am doing wrong? I wanted to be able to pass parameters so that I can use it on various databases:
/u01/app/oracle/product/9.2.0/bin/sqlplus sys/$sysdbapass @/u01/app/tools/analyze_db.sql;
#!/bin/ksh #*********************************************************************** **** # Program: Analyze database # # Will analyze target database #*********************************************************************** ****
if [ $# -ne 1 ] then echo "Usage Error. Usage: $0 <sid name>" exit 1 export ORACLE_SID=$1 fi ORACLE_SID=$1 export ORACLE_SID
. /u01/app/oraconfig/Oracle.env.vars sysdbapass=`cat /home/oracle/.passwddba.$ORACLE_SID` export sysdbapass /u01/app/oracle/product/9.2.0/bin/sqlplus sys/$sysdbapass @/u01/tools/analyze_db.sql;
exit EOF exit 0 ~
_________________________________________________________________ Don't just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/
-- //www.freelists.org/webpage/oracle-l
-- //www.freelists.org/webpage/oracle-l
