Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> DBA_JOBS disable script, Disabling DBA_JOBS using a script
KoK
post Aug 2 2010, 10:18 AM
Post #1


Newbie
*

Group: Members
Posts: 6
Joined: 21-July 10
Member No.: 43,519



Hello All,

I am trying to get a working script that could just go and mark all dba_jobs to BROKEN ==> TRUE
"execute dbms_job.broken(jobno,true)" using a pl/sql script

I have the working structure of the script except for the fact that the goal is not achieved.
I came to know that the ALTER SESSION SET CURRENT_SCHEMA does not take effect when used with the DBMS_JOB API.

Is there an alternative I can get this working. Please enlighten me.
Regards,
KoK

CODE
<--------- part of the script : START

FOR l_job_users IN job_users LOOP
    execute immediate 'alter session set current_schema='||l_job_users.LOG_USER;
    DBMS_OUTPUT.PUT_LINE(current_user);
FOR l_jobs IN jobs(l_job_users.LOG_USER) LOOP
     DBMS_OUTPUT.PUT_LINE(l_jobs.JOB);
     DBMS_JOB.BROKEN(l_jobs.JOB,TRUE);
     commit;
END LOOP;
END LOOP;
END;

END: script finished----------------------->
/


Go to the top of the page
 
+Quote Post
burleson
post Aug 2 2010, 10:36 AM
Post #2


Advanced Member
***

Group: Members
Posts: 11,318
Joined: 26-January 04
Member No.: 13




Hi Kok,

>> I am trying to get a working script that could just go and mark all dba_jobs to BROKEN ==> TRUE

That's in the Oracle script download:

http://www.dba-oracle.com/oracle_scripts.htm

>> I have the working structure of the script except for the fact that the goal is not achieved.

So, what error do you get?


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
KoK
post Aug 3 2010, 12:17 AM
Post #3


Newbie
*

Group: Members
Posts: 6
Joined: 21-July 10
Member No.: 43,519



Hi Don,

>>>So, what error do you get?

DBMS_JOB.BROKEN(l_jobs.JOB,TRUE) statement executes and says the JOBNo is not found in the job que
CODE
ERROR at line 1:
ORA-23421: job number 246 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 536
ORA-06512: at "SYS.DBMS_JOB", line 248
ORA-06512: at line 29
Go to the top of the page
 
+Quote Post
dineshb4u
post Aug 3 2010, 07:21 AM
Post #4


Advanced Member
***

Group: Members
Posts: 32
Joined: 27-June 09
From: Chennai, INDIA
Member No.: 41,081



you should own the job to break it
Go to the top of the page
 
+Quote Post
aussie_dba
post Aug 3 2010, 03:12 PM
Post #5


Advanced Member
***

Group: Members
Posts: 609
Joined: 28-August 04
Member No.: 495



"'alter session set current_schema"

This statement has never worked properly.

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

"the JOBNo is not found in the job que"

Connect as SYSDBA, or as the job owner.
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 24th July 2014 - 03:03 PM