| DBMS_JOB [message #392112] |
Mon, 16 March 2009 08:00  |
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  |
Frank
Messages: 7901
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 |
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 |
Frank
Messages: 7901
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 #392123 is a reply to message #392112] |
Mon, 16 March 2009 09:02 |
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 #392127 is a reply to message #392123] |
Mon, 16 March 2009 09:36 |
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 |
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 #392270 is a reply to message #392265] |
Tue, 17 March 2009 04:38 |
Frank
Messages: 7901
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
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
