DBMS_JOB , create table

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...

and how do I do that ?

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.

I have done that .
For some reason i get no existing table error
I even used schema.object naming
same thing happens

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...

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.

u'll be surprised
the view exists and i can select from it ...

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

thank you !

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...