RE: Automate analyze - can't log in

From: J. Dex <cemail_219_at_hotmail.com>
Date: Mon, 29 Aug 2005 11:52:53 -0400
Message-ID: <BAY106-F2F3134EE70CC8F654EB27A5AF0@phx.gbl>

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
exit

>From: "Mercadante, Thomas F (LABOR)" <Thomas.Mercadante_at_labor.state.ny.us>
>To: <cemail_219_at_hotmail.com>,<oracle-l_at_freelists.org>
>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_at_freelists.org
>[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of J. Dex
>Sent: Monday, August 29, 2005 11:39 AM
>To: oracle-l_at_freelists.org
>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/
>
>--
>http://www.freelists.org/webpage/oracle-l

---

Don’t just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Aug 29 2005 - 10:54:59 CDT