Home » RDBMS Server » Server Administration » BreakUnbreak Oracle Jobs (10.2.0.3)
BreakUnbreak Oracle Jobs [message #330584] Mon, 30 June 2008 09:23 Go to next message
adit_me1
Messages: 49
Registered: October 2007
Location: BANGALORE
Member
Hi,
I have some Oracle Jobs that are owned by one of the users of my database. I wish to break/unbreak these jobs from another user (not sys user). I cannot login from the account of the owner of the jobs.
I found the procedure dbms_job.broken(job#,true/false) which i thought would serve the purpose but learnt that this stored procedure would work only if we have logged in by the same user as the owner of the jobs.

What can i do to counter this situation?
are their any grants that I can give to the user so that it is allowed to unbreak/break another user's jobs?

Re: BreakUnbreak Oracle Jobs [message #330595 is a reply to message #330584] Mon, 30 June 2008 09:46 Go to previous messageGo to next message
OracleDisected
Messages: 25
Registered: September 2007
Location: Mexico
Junior Member
You may issue ALTER SESSION SET CURRENT_SCHEMA={owner of job}

And grant EXECUTE on DBMS_JOB

Regards
Ignacio

[Updated on: Mon, 30 June 2008 09:59] by Moderator

Report message to a moderator

Re: BreakUnbreak Oracle Jobs [message #330600 is a reply to message #330595] Mon, 30 June 2008 09:52 Go to previous messageGo to next message
adit_me1
Messages: 49
Registered: October 2007
Location: BANGALORE
Member
suppose the job belongs to "ORDER_SCHEMA" and I have logged in by "xyz" user. Even after altering the session as mentioned below, i cannot break the jobs that are owned by ORDER_SCHEMA.
It throws error.

ALTER SESSION SET CURRENT_SCHEMA= ORDER_SCHEMA;
exec dbms_job.broken(1,false)


Re: BreakUnbreak Oracle Jobs [message #330604 is a reply to message #330600] Mon, 30 June 2008 09:57 Go to previous messageGo to next message
OracleDisected
Messages: 25
Registered: September 2007
Location: Mexico
Junior Member
What error you got?

[Updated on: Mon, 30 June 2008 09:59] by Moderator

Report message to a moderator

Re: BreakUnbreak Oracle Jobs [message #330607 is a reply to message #330584] Mon, 30 June 2008 10:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no grant for this, you have to use internal job package (dbms_ijob) from a privileged user.

Regards
Michel
Re: BreakUnbreak Oracle Jobs [message #330613 is a reply to message #330604] Mon, 30 June 2008 10:19 Go to previous messageGo to next message
OracleDisected
Messages: 25
Registered: September 2007
Location: Mexico
Junior Member
I saw the error and checked in metalink. There is no chance: you need to login as the owner.

Regards

[Updated on: Mon, 30 June 2008 10:49] by Moderator

Report message to a moderator

Re: BreakUnbreak Oracle Jobs [message #330621 is a reply to message #330613] Mon, 30 June 2008 10:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I said you can do it with dbms_ijob.

Regards
Michel
Re: BreakUnbreak Oracle Jobs [message #330642 is a reply to message #330621] Mon, 30 June 2008 12:50 Go to previous messageGo to next message
OracleDisected
Messages: 25
Registered: September 2007
Location: Mexico
Junior Member

Yes, you're right... it isn't on my PL/SQL packages reference and it must run owner qualified. I've tested it and works fine... thanks.
Re: BreakUnbreak Oracle Jobs [message #330643 is a reply to message #330642] Mon, 30 June 2008 12:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is not in PL/SQL types and packages doc.
It is not documented and is an internal package.
But it is the only way to act on others job.

Currently Metalink is not accessible from my place, maybe there is a doc on it.

Regards
Michel
Re: BreakUnbreak Oracle Jobs [message #332222 is a reply to message #330584] Mon, 07 July 2008 23:46 Go to previous messageGo to next message
adit_me1
Messages: 49
Registered: October 2007
Location: BANGALORE
Member
I found something interesting to share.

I wrote a package/ stored procedure to start/stop the jobs in the job_owner's account. I then, gave the execute privilege of this SP to the other user which was willing to start/stop this users jobs.
And I dont know why, but it worked. The other user, was able to start/stop the jobs of another user, indirectly throught a SP.

Re: BreakUnbreak Oracle Jobs [message #332230 is a reply to message #332222] Mon, 07 July 2008 23:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I found something interesting to share.
I wrote a package/ stored procedure to start/stop the jobs in the job_owner's account.

I don't see the package in your post.

Quote:
And I dont know why, but it worked.

It is expected, default package behaviour is to run with the privileges and identity of its owner.

Regards
Michel
Re: BreakUnbreak Oracle Jobs [message #332245 is a reply to message #332230] Tue, 08 July 2008 00:38 Go to previous messageGo to next message
adit_me1
Messages: 49
Registered: October 2007
Location: BANGALORE
Member
here is the package.

PROCEDURE TOGGLE_MONITOR_JOB(
                    P_REPORT_NAME     OB_MONITOR_RCPTS.REPORT_NAME%TYPE,
                    P_ACTION   NUMBER,
                    p_update_by ob_monitor_rcpts.update_by%type
                  )AS

JOB_ID NUMBER;
p_what varchar2(100);


BEGIN

		select what into p_what from ob_monitor_rcpts where trim(lower(report_name)) = trim(lower(p_report_name));
       SELECT JOB INTO JOB_ID FROM user_jobs WHERE trim(LOWER(WHAT)) = trim(lower(P_WHAT));

       IF(P_ACTION = 1) THEN
       		UPDATE OB_MONITOR_RCPTS SET
                                REPORT_STATUS = 'A',
                                UPDATE_DETAILS = 'Job Started',
                                UPDATE_DATE = SYSDATE,
                                UPDATE_BY = p_update_by
            WHERE lower(trim(REPORT_NAME)) = lower(trim(P_REPORT_NAME));
        DBMS_JOB.BROKEN(JOB_ID,false);
        dbms_output.put_line( 'Job Started');
       ELSIF(P_ACTION = 0) THEN
       	  UPDATE OB_MONITOR_RCPTS SET
                                REPORT_STATUS = 'D',
                                UPDATE_DETAILS = 'Job Suspended',
                                UPDATE_DATE = SYSDATE,
                                UPDATE_BY = p_update_by
        WHERE lower(trim(REPORT_NAME)) = lower(trim(P_REPORT_NAME));
        DBMS_JOB.BROKEN(JOB_ID,true);
               dbms_output.put_line( 'Job Suspended');

        end if;

       commit;

       exception when others then
       dbms_output.put_line('Error : '||sqlerrm);

END;
Re: BreakUnbreak Oracle Jobs [message #332266 is a reply to message #332245] Tue, 08 July 2008 01:09 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I said, your package is executed as owner and so can see owner's jobs.

Regards
Michel
Previous Topic: Space allocation for moving the LOB segment to different tablespace
Next Topic: drop oracle8 database
Goto Forum:
  


Current Time: Sat Dec 10 12:40:52 CST 2016

Total time taken to generate the page: 0.16707 seconds