Home » RDBMS Server » Server Administration » Problem creating a job (10g, SLES9)
Problem creating a job [message #364646] Tue, 09 December 2008 07:27 Go to next message
dirkm
Messages: 86
Registered: November 2008
Location: Centurion - South Africa
Member
I am trying to create the following job, but get the error (see below):

BEGIN
dbms_scheduler.create_job
(job_name => 'POC.CLEAN_TMP_TABS',
job_type => 'STORED_PROCEDURE',
job_action => 'POC.CLEAN_TMP_TABS',
start_date => '17-FEB-2009 17:00 PM',
repeat_interval => 'FREQ=DAILY',
enabled => TRUE,
auto_drop => FALSE,
comments => 'Clean temptabs job - user POC');
END;
/

SQL> @clean_tmp_tabs.sql
BEGIN
*
ERROR at line 1:
ORA-27477: "POC.CLEAN_TMP_TABS" already exists
ORA-06512: at "SYS.DBMS_ISCHED", line 99
ORA-06512: at "SYS.DBMS_SCHEDULER", line 262
ORA-06512: at line 2


I can only find 1 object with this owner & name, and also no jobs with this name:

select owner, object_name, object_type
from dba_objects
where object_name like 'CLEAN%';

POC
CLEAN_TMP_TABS
PROCEDURE


select OWNER, JOB_NAME
from dba_scheduler_jobs

OWNER                          JOB_NAME
------------------------------ ------------------------------
SYS                            AUTO_SPACE_ADVISOR_JOB
SYS                            GATHER_STATS_JOB
SYS                            FGR$AUTOPURGE_JOB
SYS                            PURGE_LOG
EXFSYS                         RLM$SCHDNEGACTION
EXFSYS                         RLM$EVTCLEANUP
Re: Problem creating a job [message #364650 is a reply to message #364646] Tue, 09 December 2008 07:43 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
You cannot have a job and procedure with the same name. You can, for example, change your job's name to:

(job_name => 'POC.CLEAN_TMP_TABS_JOB',

Re: Problem creating a job [message #364669 is a reply to message #364650] Tue, 09 December 2008 08:30 Go to previous messageGo to next message
ora_zhp
Messages: 10
Registered: September 2007
Junior Member
Each Scheduler object is a complete database schema
object of the form [schema.]name. Scheduler objects
exactly follow the naming rules for database objects and
share the SQL namespace with other database objects.
Re: Problem creating a job [message #364683 is a reply to message #364646] Tue, 09 December 2008 10:22 Go to previous messageGo to next message
dirkm
Messages: 86
Registered: November 2008
Location: Centurion - South Africa
Member
Maybe I can approach my problem from a different angle. I am trying to create a job that runs on an existing server, on a newly installed server.

On the existing server, I can see the job in dba_jobs, but not in dba_scheduler_jobs. I also cannot see it in Enterprise Manager.

How can I generate the ddl for this job, and then maybe work it from there ?

Dirk

Re: Problem creating a job [message #364695 is a reply to message #364683] Tue, 09 December 2008 11:52 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
job != scheduler job

In dba_jobs are the jobs that are created with dbms_job.

In dba_scheduler_jobs are the jobs that are created with dbms_scheduler.

Those are two different things.
Re: Problem creating a job [message #364772 is a reply to message #364646] Wed, 10 December 2008 00:35 Go to previous messageGo to next message
dirkm
Messages: 86
Registered: November 2008
Location: Centurion - South Africa
Member
Yes, thank you Thomas. I saw in the manuals that dbms_job was used in version 9, and that it was replaced with dbms_scheduler in version 10. But I thought that a job would still be a job (that they would show in the same system tables (eg. dba_jobs).

I will have to read more about this. I have read a lot of links on Google about this, some of it from the manuals, but I must still be missing something. I am relatively new to Oracle, and have not worked with jobs / schedules before.

Thanks again.
Re: Problem creating a job [message #364783 is a reply to message #364772] Wed, 10 December 2008 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I saw in the manuals that dbms_job was used in version 9, and that it was replaced with dbms_scheduler in version 10.

No, dbms_scheduler was added but dbms_job is still there and will always be, they are complementary and dbms_job is transactional (that is the job is submitted only if you commit the transaction and removed if you roll back).

Regards
Michel
Re: Problem creating a job [message #364863 is a reply to message #364646] Wed, 10 December 2008 05:25 Go to previous messageGo to next message
dirkm
Messages: 86
Registered: November 2008
Location: Centurion - South Africa
Member
Got it, thank you. I ran a couple of tests, using both dbms_job and dbms_scheduler and saw the results of them in the 2 views: dba_jobs, and dba_scheduler_jobs. Thank you.


A question about dba_jobs:

Where do I get the job name for the jobs in this table ? The "what" column seems to be the code that it executes, and the "job" column is the number of the job. I do not see a column for the name in here.
Re: Problem creating a job [message #364873 is a reply to message #364863] Wed, 10 December 2008 05:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no job name, only a job number.

Regards
Michel
Re: Problem creating a job [message #364891 is a reply to message #364646] Wed, 10 December 2008 06:27 Go to previous messageGo to next message
dirkm
Messages: 86
Registered: November 2008
Location: Centurion - South Africa
Member
I had a feeling it would only have the number. Thank you Michel.

The reason I asked:

if you have a name it would be easier to compare jobs on different servers - it would be something you could quickly pick up if you had a name for the job.
Comparing the "what" would be a manual task - to select the info and compare it manually between servers.


This also brings me back to Frank's answer:
"You cannot have a job and procedure with the same name."

If a job doesn't have a name, then this would not be possible.

Dirk
Re: Problem creating a job [message #364893 is a reply to message #364891] Wed, 10 December 2008 06:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Frank talked about scheduler job not old one.

Regards
Michel
Re: Problem creating a job [message #364917 is a reply to message #364646] Wed, 10 December 2008 08:14 Go to previous message
dirkm
Messages: 86
Registered: November 2008
Location: Centurion - South Africa
Member
Off course, my apologies. Time for a break ...

Thank you.
Previous Topic: TRACE FILES
Next Topic: about hang, waiting, lock
Goto Forum:
  


Current Time: Thu Dec 08 20:28:43 CST 2016

Total time taken to generate the page: 0.12910 seconds