• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1180
  • Last Modified:

DBMS_JOB , create table

I have submitted a job using the dbms_job package .
I see that the job has failed
How can i see the reasons for that failute

the procedure that the dbms_job executes has only this statement

execute immediate ' create table p1 as select * from v$segment_statistics'
0
active1065
Asked:
active1065
  • 5
  • 4
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you have to add error handling to the job code.

for example, like this:

DECLARE
 m VARCHAR2(2000);
begin
 execute immediate ' create table p1 as select * from v$segment_statistics';
exception
 when others then
  m:= TO_CHAR(SQLERRM);
  dbms_output.enable(10000);
  dbms_output.put_line(m);
end;
 
instead of dmbs_output, you could insert into a log table...
0
 
active1065Author Commented:
and how do I do that ?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the code I showed is "simple" pl/sql, which you submit as dbms job code...
where is the problem? you could put it altogether into a procedure, and just call that procedure.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
active1065Author Commented:
I have done that .
For some reason i get no existing table error
I even used schema.object naming
same thing happens
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
that means that either your oracle version does not have that view, or the user you use to run the job does not have permissions to that view...
0
 
sdstuberCommented:
also, for this or other jobs,  your alert log will capture any unhandled exceptions from dbms_jobs.

v$segment_statistics only grants SELECT to the SELECT_CATALOG_ROLE by default.

The "SELECT ANY DICTIONARY" system privilege will also give you access.

So, to fix....

grant select on sys.v$segment_statistics to <username of job>;
or
grant SELECT_CATALOG_ROLE to <username of job>;
or
grant SELECT ANY DICTIONARY to <username of job>;

instead of granting directly to the job user, you could also grant to a role that user has.
0
 
active1065Author Commented:
u'll be surprised
the view exists and i can select from it ...
0
 
active1065Author Commented:
select * from v$segmet_statistics works in my session

when executed from a procedure it ( by the same user ) it says that table or view does not exists .


it is actually a public synonym to the SYS view
0
 
sdstuberCommented:
you have access through a role then

I'm sorry, I shouldn't have suggested granting to a role since you're going to be doing this in a procedure.


You need a direct grant on the view
or the select any dictionary privilege.
0
 
active1065Author Commented:
thank you !
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I am glad you found your solution!

just a small note on the EE usage; your original question was:
>How can i see the reasons for that failure
I think, a split would have been appropriate here....
please remember for next times...
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now