OraFAQ Forum: Server Administration » DBMS

Home » RDBMS Server » Server Administration » DBMS_JOB

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

DBMS_JOB [message #169198]

Tue, 25 April 2006 13:44

faiz_hyd
Messages: 294
Registered: February 2006

Senior Member

Hi

I submitted and scheduled a job to run daily from Toad it saved and gave JOB ID, this is the code, but it is not running

DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
    ( job       => X 
     ,what      => 'dbms_stats.gather_schema_stats (ownname => ''TEST'', estimate_percent => 99, cascade => TRUE);'
     ,next_date => to_date('24/04/2006 16:00:00','dd/mm/yyyy hh24:mi:ss')
     ,interval  => 'TRUNC(SYSDATE+1)'
     ,no_parse  => TRUE
    );
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
END;
/

Where as if i take it exclusively at SQL prompt it runs for 1 time,i want to make it daily scheduled Job.

SQL> exec dbms_stats.gather_schema_stats(ownname => 'TEST', estimate_percent => NULL, cascade =>  TRUE); 

PL/SQL procedure successfully completed.

Thanks

[Updated on: Tue, 25 April 2006 13:51]

Report message to a moderator

Re: DBMS_JOB [message #169201 is a reply to message #169198]

Tue, 25 April 2006 13:58

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Try issuing a COMMIT after the DBMS_OUTPUT & report back here what happens.

Report message to a moderator

Re: DBMS_JOB [message #169205 is a reply to message #169201]

Tue, 25 April 2006 14:23

faiz_hyd
Messages: 294
Registered: February 2006

Senior Member

hI

Thanks for replying

I just submitted again after issuing commit

it gave message

 Table doesn't exists ORA-00942 Batch 1 line 1 col 99

same thing works from SQL >

Thanks

Report message to a moderator

Re: DBMS_JOB [message #169206 is a reply to message #169198]

Tue, 25 April 2006 14:26

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

PRIVS acquired via ROLE do NOT apply within PL/SQL procedure(s).

Report message to a moderator

Re: DBMS_JOB [message #169207 is a reply to message #169206]

Tue, 25 April 2006 14:31

faiz_hyd
Messages: 294
Registered: February 2006

Senior Member

Thanks for replying

But the same thing is working for another schema i copied the code from the schema where it was working and from with in this code if i take that line of
exec dbms_stat.gather_Schema_Stat(...) in SQL > prompt it works
some thing i am not able to trace

Of course the user i am conencting to do this is not DBA but user as owner of Objects, same user can run stats on other schema some thing i am missing.
Thanks for giving time.

Regards

Report message to a moderator

Re: DBMS_JOB [message #169208 is a reply to message #169198]

Tue, 25 April 2006 14:38

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SQL> SET ROLE NONE
SQL> exec dbms_stats.gather_schema_stats(ownname => 'TEST', estimate_percent => NULL, cascade => TRUE);

What happens?

Report message to a moderator

Re: DBMS_JOB [message #169210 is a reply to message #169208]

Tue, 25 April 2006 14:43

faiz_hyd
Messages: 294
Registered: February 2006

Senior Member

here it is

These are the roles user is having 

SQL> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
TEST                       CONNECT                        NO  YES NO
TEST                        RESOURCE                       NO  YES NO
SQL> SET ROLE NONE
  2  /

Role set.

SQL> exec dbms_stats.gather_schema_stats(ownname => 'TEST', estimate_percent => NULL, cascade => TRUE); 

PL/SQL procedure successfully completed.

What to interpret from it.

Thanks

[Updated on: Tue, 25 April 2006 14:56]

Report message to a moderator

Re: DBMS_JOB [message #169223 is a reply to message #169198]

Tue, 25 April 2006 16:14

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SQL> SELECT COUNT(*) FROM DBA_JOBS;
returns what?

Report message to a moderator

Previous Topic:	Regarding DBMS_METADATA
Next Topic:	Database Migration (8i to 10g)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 13:50:13 CDT 2024