RE: Automate analyze - can't log in
- From: "Mercadante, Thomas F (LABOR)" <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>
- To: "J. Dex" <cemail_219@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 29 Aug 2005 12:07:17 -0400
J.,
There is another way to do this:
whenever sqlerror exit 1
set timing on
connect / as sysdba
Declare
Cursor c1 is
Select username from dba_users;
Begin
For c1_rec in c1 loop
Dbms_stats.gather_schema_stats(c1_rec.username, estimate_percent=>20,
cascade=>true);
End loop;
End;
/
And run the above as follows:
$ORACLE_HOME/bin/sqlplus /nolog @sqlscript
Tom
-----Original Message-----
From: J. Dex [mailto:cemail_219@xxxxxxxxxxx]
Sent: Monday, August 29, 2005 11:53 AM
To: Mercadante, Thomas F (LABOR); oracle-l@xxxxxxxxxxxxx
Subject: RE: Automate analyze - can't log in
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@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
_________________________________________________________________
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
Other related posts:
