| Problem creating a job [message #364646] |
Tue, 09 December 2008 07:27  |
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 #364669 is a reply to message #364650] |
Tue, 09 December 2008 08:30  |
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 |
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 #364772 is a reply to message #364646] |
Wed, 10 December 2008 00:35 |
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 #364863 is a reply to message #364646] |
Wed, 10 December 2008 05:25 |
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 #364891 is a reply to message #364646] |
Wed, 10 December 2008 06:27 |
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
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
