RE: Disbale Default 10G stats job - GATHER_STATS_JOB

From: D'Hooge Freek <Freek.DHooge_at_uptime.be>
Date: Thu, 8 Jul 2010 09:58:07 +0200
Message-ID: <4814386347E41145AAE79139EAA3989810265B1B01_at_ws03-exch07.iconos.be>

Hi,

According to the oracle documentation, only sys can do things in the sys schema: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sched.htm#i1013037 So you need to run the disable procedure as user sys.

For jobs within other user schema's you just need to have the "CREATE ANY JOB" privilege.

Regards,

Freek D'Hooge
Uptime
Oracle Database Administrator
email: freek.dhooge_at_uptime.be
tel +32(0)3 451 23 82
http://www.uptime.be
disclaimer: www.uptime.be/disclaimer

From: Sreejith S Nair [mailto:Sreejith.Sreekantan_at_ibsplc.com] Sent: donderdag 8 juli 2010 8:52
To: D'Hooge Freek
Cc: oracle-l_at_freelists.org
Subject: RE: Disbale Default 10G stats job - GATHER_STATS_JOB

Hi,
� � � � Can I execute this - means to DISABLE this job as APP_OWNER -not as SYS. I tried including the owner name of the job with the job name.Still same error.

SQL> select user from dual;

USER

APP_OWNER SQL> BEGIN
� 2 � � -- Disable programs and jobs.
� 3 � � DBMS_SCHEDULER.DISABLE(NAME � � �=> 'SYS.GATHER_STATS_JOB');
� 4 �END;
� 5 �/

BEGIN
*
ERROR at line 1:

ORA-27476: "SYS.GATHER_STATS_JOB" does not exist 
ORA-06512: at "SYS.DBMS_ISCHED", line 2763 
ORA-06512: at "SYS.DBMS_SCHEDULER", line 1753 
ORA-06512: at line 3

Brgds,
Sreejith �Nair
�
�

From: � � � �D'Hooge Freek <Freek.DHooge_at_uptime.be> 
To: � � � �"Sreejith.Sreekantan_at_ibsplc.com" <Sreejith.Sreekantan_at_ibsplc.com>, "oracle-l_at_freelists.org" <oracle-l_at_freelists.org> 
Date: � � � �07/08/2010 12:08 PM

Subject: � � � �RE: Disbale Default 10G stats job - GATHER_STATS_JOB

Hi,

You need to include the owner name of the job with the job name. So, if the job is owned by SYS then you need to to execute:

DBMS_SCHEDULER.DISABLE(NAME � � �=> 'SYS.GATHER_STATS_JOB'); Marten,

You are referring to the old jobs, using dbms_jobs. Although even then you could use dbms_ijob for some operations without any problem (but I don't think it is supported).

Regards,

Freek D'Hooge
Uptime
Oracle Database Administrator
email: freek.dhooge_at_uptime.be
tel +32(0)3 451 23 82
http://www.uptime.be
disclaimer: www.uptime.be/disclaimer

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sreejith S Nair Sent: donderdag 8 juli 2010 7:15
To: oracle-l_at_freelists.org
Subject: Disbale Default 10G stats job - GATHER_STATS_JOB

Hi list memebers,

I would like to disable the default stats gathering job in 10g and would like to configure our own.But I am facing some issues in disabling it.

select grantee,table_name from user_tab_privs where grantee='APP_OWNER'; GRANTEE � � � �TABLE_NAME

APP_OWNER � � � �V_$SESSION 
APP_OWNER � � � �V_$PARAMETER2 
APP_OWNER � � � �V_$DATABASE 
APP_OWNER � � � �V_$INSTANCE 
APP_OWNER � � � �V_$SGASTAT 
APP_OWNER � � � �DBA_ROLLBACK_SEGS 
APP_OWNER � � � �DBA_JOBS_RUNNING 
APP_OWNER � � � �DBA_JOBS 
APP_OWNER � � � �DBMS_LOCK 
APP_OWNER � � � �AQ$_AGENT 
APP_OWNER � � � �AQ$_DEQUEUE_HISTORY 
APP_OWNER � � � �AQ$_SUBSCRIBERS 
APP_OWNER � � � �AQ$_RECIPIENTS 
APP_OWNER � � � �AQ$_HISTORY 
APP_OWNER � � � �AQ$_DEQUEUE_HISTORY_T 
APP_OWNER � � � �AQ$_NOTIFY_MSG 
APP_OWNER � � � �DBMS_AQ 
APP_OWNER � � � �DBMS_AQADM 
APP_OWNER � � � �DBMS_SCHEDULER 
APP_OWNER � � � �JAVA$JVM$STATUS

H:\>sqlplus APP_OWNER/APP_OWNER_at_MYDB

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Jul 8 10:32:53 2010

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options

SQL> BEGIN
� 2 � � -- Disable programs and jobs.
� 3 � � DBMS_SCHEDULER.DISABLE(NAME � � �=> 'GATHER_STATS_JOB');
� 4 �END;
� 5 �/

BEGIN
*
ERROR at line 1:

ORA-27476: "APP_OWNER.GATHER_STATS_JOB" does not exist 
ORA-06512: at "SYS.DBMS_ISCHED", line 2763 
ORA-06512: at "SYS.DBMS_SCHEDULER", line 1753 
ORA-06512: at line 3

SQL> select JOB_NAME,STATE,ENABLED from dba_scheduler_jobs;

JOB_NAME � � � � � � � � � � � STATE � � � � � ENABL

------------------------------ --------------- -----

PURGE_LOG � � � � � � � � � � �SCHEDULED � � � TRUE FGR$AUTOPURGE_JOB � � � � � � �DISABLED � � � �FALSE

GATHER_STATS_JOB � � � � � � � SCHEDULED � � � TRUE 
AUTO_SPACE_ADVISOR_JOB � � � � SCHEDULED � � � TRUE 
RLM$EVTCLEANUP � � � � � � � � SCHEDULED � � � TRUE 
RLM$SCHDNEGACTION � � � � � � �SCHEDULED � � � TRUE 
GATHER_STALE_STATS_DICTIONARY �SCHEDULED � � � TRUE

GATHER_SCHEMA_STALE_APP_OWNER �SCHEDULED � � � TRUE GATHER_SCHEMA_STATS_APP_OWNER �SCHEDULED � � � TRUE 9 rows selected.

SQL> select user from dual;

USER

APP_OWNER SQL> SQL> select JOB_NAME,STATE,ENABLED from all_scheduler_jobs;

JOB_NAME � � � � � � � � � � � STATE � � � � � ENABL

------------------------------ --------------- ----- 
RLM$EVTCLEANUP � � � � � � � � SCHEDULED � � � TRUE 
RLM$SCHDNEGACTION � � � � � � �SCHEDULED � � � TRUE 
GATHER_STALE_STATS_DICTIONARY �SCHEDULED � � � TRUE

GATHER_SCHEMA_STALE_CTS_OWNER �SCHEDULED � � � TRUE GATHER_SCHEMA_STATS_CTS_OWNER �SCHEDULED � � � TRUE Can we DISABLE this job only as connecting as SYS / �should I give any permission to disable �this job connecting as APP_OWNER ?

Many Thanks,
Sreejith �Nair
�
�
�
�
�

DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect."

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

Received on Thu Jul 08 2010 - 02:58:07 CDT

This message: [ Message body ]
Next message: Holvoet, Jo: "RE: SQL Tuning Advisor - how well does it work?"
Previous message: Martin Klier: "Antwort: WG: RE: Oracle Configuration Manager - half working, half not"
Maybe in reply to: Sreejith S Nair: "Disbale Default 10G stats job - GATHER_STATS_JOB"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message