Home » Infrastructure » Windows » how to create cmd script for ora statistics [merged 2] (oracle 11)
how to create cmd script for ora statistics [merged 2] [message #519119] Tue, 09 August 2011 08:48 Go to next message
bonovox
Messages: 6
Registered: February 2006
Junior Member
Hy everyone,

I need to do a cmd script for launcing oracle statistics every week and scheduling it from task scheduler of my server with win 2008 R2 O.S.

the script is:

"statistiche-oracle.bat"

set ORACLE_SID=GW1
sqlplus "/ as sysdba" @statistiche.sql

and "statistiche.sql" is:

exec dbms_stats.gather_schema_stats(ownname => 'PDMUSER', cascade=>true)
exit

When I lauch it from command line it stops with error message: "insufficient privileges" and ask me the user, so I put 'PDMUSER' that is my user, it asks also pwd, I put it and the works.

Please could you help me to do the correct sintax for the automatic run?

Thanks


Christian
Re: how to create cmd script for ora statistics [message #519121 is a reply to message #519119] Tue, 09 August 2011 08:52 Go to previous messageGo to next message
joy_division
Messages: 4961
Registered: February 2005
Location: East Coast USA
Senior Member
Only a user in the DBA group can run sqlplus "/ as sysdba" without being prompted for a password.
Re: how to create cmd script for ora statistics [message #519122 is a reply to message #519119] Tue, 09 August 2011 08:53 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

default behavior for V11 is that statistics are gathered once every 24 hours.
Realize that LAST_ANALYZED is NOT updated each time statistics are gathered,
but only when statistics for that object actually change.
if/when data does not change, then "new" statistics are NOT gathered for that object

SQL> select max(last_analyzed) from dba_tables;

MAX(LAST_ANALYZED)
-------------------
2011-08-08 22:03:03

[Updated on: Tue, 09 August 2011 08:58]

Report message to a moderator

Re: how to create cmd script for ora statistics [message #519124 is a reply to message #519121] Tue, 09 August 2011 09:57 Go to previous message
bonovox
Messages: 6
Registered: February 2006
Junior Member
Of course,

This script was made by a collegue that didn't say me nothing about.
Now following info I tried that the script is scheduled for running the task with an user that is in effect member of DBA group.
If I login with this user and from cmd lauch the script, it runs for about 10 minutes and terminate succesfully.
Instead, if I try to run the task scheduled, when it reaches the timeout it ends.
I don't know how to see where and why it stops for all this time.
Previous Topic: install oracle database 11g.2 on windows 7
Next Topic: sql path how to set??
Goto Forum:
  


Current Time: Wed Dec 08 16:46:51 CST 2021