Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_JOB (Oracle 10g,XP)
DBMS_JOB [message #392112] Mon, 16 March 2009 08:00 Go to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

Whether dbms_job.submit switch between two Schemas ,when i execute my job from USER1 and inserting data into a table TEST which is in USER2 nothing is happening .

create table USER2.Test ( T  NUMBER (10))

declare
x number:=0;
begin
  DBMS_JOB.SUBMIT  ( JOB  => X 
  ,WHAT      => 'insert into USER2.Test  values(31);' );
  commit;  
end ;


But when i execute this same job from USER2 data is get inserted into USER2.test table ; here both user USER1 and USER2 have given dba role .How can it be resolved .Please Reply


Thanks

[Updated on: Mon, 16 March 2009 08:02]

Report message to a moderator

Re: DBMS_JOB [message #392113 is a reply to message #392112] Mon, 16 March 2009 08:04 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Options
1) Create a procedure under the correct user that does the insert and call that
2) Prefix the table to insert into with the required schema-name

There are probably plenty of other options to do this.
Re: DBMS_JOB [message #392114 is a reply to message #392113] Mon, 16 March 2009 08:14 Go to previous messageGo to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

I tried this code also but not working previous issue is happening here also

CREATE OR REPLACE PACKAGE USER1.PK_test AUTHID CURRENT_USER IS

PROCEDURE PROCESS ;
END PK_test;

CREATE OR REPLACE PACKAGE BODY USER1.PK_test  IS

PROCEDURE PROCESS is
x number:=0;
begin
  DBMS_JOB.SUBMIT  ( JOB  => X
  ,WHAT      => 'insert into USER2.Test  values(31);' );
  commit;
 
END PROCESS;

END PK_test;



Thanks

[Updated on: Mon, 16 March 2009 08:15]

Report message to a moderator

Re: DBMS_JOB [message #392120 is a reply to message #392114] Mon, 16 March 2009 08:44 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What "previous issue is happening here also"?
Records being inserted into user1.test? I find that hard to believe.
Re: DBMS_JOB [message #392121 is a reply to message #392112] Mon, 16 March 2009 08:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your user must have privilege on the object DIRECTLY not via a role.

Regards
Michel

Re: DBMS_JOB [message #392123 is a reply to message #392112] Mon, 16 March 2009 09:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
When you say 'Nothing is happening', I think we need some more details.

Is the NEXT_DATE value in USER/ALL/DBA_JOBS increasing every time the job should run?

If so, then it is runing, and something is happening - we just need to work out what.

What is the value of FAILURES in USER/ALL/DBA_JOBS?

Are there any messages in the alert log about job failures?
Re: DBMS_JOB [message #392124 is a reply to message #392123] Mon, 16 March 2009 09:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is the NEXT_DATE value in USER/ALL/DBA_JOBS increasing every time the job should run?

Or in FAILURES column.

Quote:
Are there any messages in the alert log about job failures?

In this case, there is nothing unless you force the execution (dbms_job.run) and in this case I bet there will be "PL/SQL: ORA-00942: table or view does not exist" Wink

Regards
Michel
Re: DBMS_JOB [message #392125 is a reply to message #392124] Mon, 16 March 2009 09:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Or in FAILURES column.

Doh! I didn't see you said it in the last part of your post.

Regards
Michel
Re: DBMS_JOB [message #392127 is a reply to message #392123] Mon, 16 March 2009 09:36 Go to previous messageGo to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

Yes In the NEXT_DATE value for sys.DBA_JOBS increasing every time the job run . For failures the value is 1 , when i execute the same job once the value of failure gets 2 and so on..

But when job is executed in USER1 and inserted data into USER1.TEST table after execution of job the data in the sys.DBA_JOBS gets cleared after successful execution of job.


But when job is executed in USER1 and inserted data into USER2.TEST table after execution of job the data in the sys.DBA_JOBS doesn't get cleared due to job execution failure.

I think that the records in sys.DBA_JOBS is all about failed jobs or jobs which has error during execution

[Updated on: Mon, 16 March 2009 09:39]

Report message to a moderator

Re: DBMS_JOB [message #392134 is a reply to message #392127] Mon, 16 March 2009 09:51 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think that the records in sys.DBA_JOBS is all about failed jobs or jobs which has error during execution


You think wrong, I'm afraid.

DBA_JOBS just shows all the jobs on the database.]

The row disappears from DBA_JOBS after a successful execution because your job is set up to only run once - after its run, it is deleted.
When it fails, Oracle will try to run it again (up to 16 times) before marking the job as permenantly broken.

If you have a look in the Alert.log, you will see the reason for the failure - almost certainly ORA-00942: table or view does not exist like @Michel said.

Your problem is that the user USER1 needs the privilege 'INSERT ON USER2.TEST' to be explicitly granted to it - simply having a role like DBA that provides this ability is no use.
Re: DBMS_JOB [message #392265 is a reply to message #392134] Tue, 17 March 2009 04:01 Go to previous messageGo to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

grant all on USER1.TEST to USER2


and then i executed my job from schema USER2 data got inserted into USER1.TEST

Is there any other method to other than this
grant all on USER1.TEST to USER2


Thanks ALL
Re: DBMS_JOB [message #392270 is a reply to message #392265] Tue, 17 March 2009 04:38 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
rakeshramm wrote on Tue, 17 March 2009 10:01
grant all on USER1.TEST to USER2


and then i executed my job from schema USER2 data got inserted into USER1.TEST

Is there any other method to other than this
grant all on USER1.TEST to USER2


Thanks ALL

JRowbottom wrote on Mon, 16 March 2009 15:51
Your problem is that the user USER1 needs the privilege 'INSERT ON USER2.TEST' to be explicitly granted to it


Re: DBMS_JOB [message #392271 is a reply to message #392265] Tue, 17 March 2009 04:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just grant insert as you just want to insert.

Regards
Michel
Re: DBMS_JOB [message #392276 is a reply to message #392271] Tue, 17 March 2009 04:52 Go to previous message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

OK , Thanks
Previous Topic: searching
Next Topic: Migration Script using a query
Goto Forum:
  


Current Time: Sun Dec 11 05:53:39 CST 2016

Total time taken to generate the page: 0.05599 seconds