RE: Disbale Default 10G stats job - GATHER_STATS_JOB
- From: D'Hooge Freek <Freek.DHooge@xxxxxxxxx>
- To: Sreejith S Nair <Sreejith.Sreekantan@xxxxxxxxxx>
- Date: Thu, 8 Jul 2010 09:58:07 +0200
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@xxxxxxxxx
tel +32(0)3 451 23 82
http://www.uptime.be
disclaimer: www.uptime.be/disclaimer
________________________________________
From: Sreejith S Nair [mailto:Sreejith.Sreekantan@xxxxxxxxxx]
Sent: donderdag 8 juli 2010 8:52
To: D'Hooge Freek
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxx>
To: "Sreejith.Sreekantan@xxxxxxxxxx" <Sreejith.Sreekantan@xxxxxxxxxx>,
"oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxx
tel +32(0)3 451 23 82
http://www.uptime.be
disclaimer: www.uptime.be/disclaimer
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Sreejith S Nair
Sent: donderdag 8 juli 2010 7:15
To: oracle-l@xxxxxxxxxxxxx
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@MYDB
SQL*Plus: Release 9.2.0.1.0 - Production on Thu Jul 8 10:32:53 2010
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
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."
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."
--
//www.freelists.org/webpage/oracle-l
Other related posts:
