Link to home
Start Free TrialLog in
Avatar of marrowyung
marrowyung

asked on

rerun Oracle job and drop the Oracle job.

hi,

I used SQL developer 18.c's DBA toolbar to create the data pump job to import data and as according to the command below I want to rerun the job and/OR drop the job to recreate one,

Drop a job:

BEGIN
  DBMS_SCHEDULER.DROP_JOB ('IMPORT_CISUAT');
END;
/

error is :
Error starting at line : 49 in command -
BEGIN
  DBMS_SCHEDULER.DROP_JOB ('IMPORT_CISUAT');
END;
Error report -
ORA-27475: unknown job "SYSTEM"."IMPORT_CISUAT"
ORA-06512: at "SYS.DBMS_ISCHED", line 274
ORA-06512: at "SYS.DBMS_SCHEDULER", line 753
ORA-06512: at line 2
27475. 00000 -  "unknown %s \"%s\".\"%s\""
*Cause:    The specified object did not exist, privileges were not granted,
           or the object was of the wrong type.
*Action:   Specify an object of the correct type on which you have privileges.

Open in new window


to run a job I run:

/* Running Jobs */

BEGIN
   dbms_scheduler.run_job('IMPORT_CISUAT');
END;

error is:

Error starting at line : 44 in command -
BEGIN
   dbms_scheduler.run_job('IMPORT_CISUAT');
END;
Error report -
ORA-27475: unknown job "SYSTEM"."IMPORT_CISUAT"
ORA-06512: at "SYS.DBMS_ISCHED", line 238
ORA-06512: at "SYS.DBMS_SCHEDULER", line 568
ORA-06512: at line 2
27475. 00000 -  "unknown %s \"%s\".\"%s\""
*Cause:    The specified object did not exist, privileges were not granted,
           or the object was of the wrong type.
*Action:   Specify an object of the correct type on which you have privileges.

Open in new window


any idea on what's wrong ? I use SYSTEM account to login and run that job.

privileges  is not enough ? or can't find the object?
Avatar of Sean Stuber
Sean Stuber

Does SYSTEM own the job?
The first error in both of these, the ORA-27475 error, indicates this job doesn't exist.  So, it apparently was not successfully created.  Or if it was created, it was not created in the SYSTEM schema.
select * from dba_scheduler_jobs where job_name = 'IMPORT_CISUAT';

Open in new window

Then check for OWNER. As Mark and sdstuber already suggested: the job my not be owned by SYSTEM...
Avatar of marrowyung

ASKER

Mark Geerlings,

I create it under SYSTEM account in SQL developer

"indicates this job doesn't exist.  "

I search from SQL developer UI, it existing and it shows the status of the job! but just can't drop using command.

SQL developer seems don't allow me to rerun the job from UI , it doesn't make any sense, right?  

Alexander Eßer,

"select * from dba_scheduler_jobs where job_name = 'IMPORT_CISUAT';"

this shows empty, but the UI shows:

User generated image
 I can see job status from SQL developer :

User generated image
you can see that job owner : SYSTEM

the import of DMP file from using SQL  developer is much easy than using impdp !

But seems SQL developer don't allow me to delete/modify/rerun a job from UI, funny !
ASKER CERTIFIED SOLUTION
Avatar of Alex [***Alex140181***]
Alex [***Alex140181***]
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
"My bad, the following view shows those jobs:
select * from dba_datapump_jobs
"
yeah, it shows it is there.

so why this one:

BEGIN
   dbms_scheduler.run_job('IMPORT_CISUAT');
END;

Open in new window


doesn't work ?

"Sure, this is always a personal opinion, but I totally disagree ;-)"

disagree? or agree ?

I hate command as this is why UI invented ! command can make a lot of mistake but can say it is flexible for experienced DBA. can keep adding option.
I hate command as this is why UI invented ! command can make a lot of mistake but can say it is flexible for experienced DBA. can keep adding option.
This discussion would lead us nowhere, so let's just stay on-topic ;-)

The dbms_scheduler.run_job... won't work because (as I already mentioned) the impdp/expdp stuff is done in a different way under the hood...
"This discussion would lead us nowhere, so let's just stay on-topic ;-)

no worry !  not that far away ! it is related.

so you mean this command is not going to work anyway but oracle keep posting it ?

BEGIN
   dbms_scheduler.run_job('IMPORT_CISUAT');
END;

Open in new window

so you mean this command is not going to work anyway but oracle keep posting it ?
What's your source? Please provide a link or something!

no worry !  not that far away ! it is related.
I hate command as this is why UI invented ! command can make a lot of mistake but can say it is flexible for experienced DBA. can keep adding option.
UI has been invented for (all the) end users and for those who have no clue how to deal properly with servers WITHOUT a gui ;-) But as I said: that will lead us NOWHERE...
"
What's your source? Please provide a link or something!"

https://docs.oracle.com/html/E25494_01/scheduse002.htm#i1021522
That's the DBMS_SCHEDULER!! And this has NOTHING to do with IMPDP and/or EXPDP!
hi,

 but it has a section called
'Running Jobs
'

right ?

that's what I am following !

or I should read  a section called RERUN a job ?

but the point is , that scheduler can't even see the job, this is what the error is about.
>>> scheduler can't even see the job

There are multiple job types in oracle.

jobs created by DBMS_JOB
jobs created by DBMS_SCHEDULER
jobs created by data pump

Each type is called a "job" but they are not the same things at all.
You can't use functionality from one type to view or modify jobs of the other types.

You are trying to use DBMS_SCHEDULER to view, run, and drop data pump jobs, but it can't do that.
There is no reason to expect that it should be able to because they aren't the same types of jobs.
sdstuber,


tks , may I know how can I rerun the job created by pump data wizard ?

how can I drop it too ?
there isn't a re-run option, you simply execute the impdp/expdp command again, or call the dbms_datapump apis again (or use a gui to call them for you)

the dbms_datapump api is simply the START procedure.

If you want to drop it...

If the job is already running, use dbms_datapump to attach to the job and stop it.

A datapump job is stored as a table, so to clean out the record of the job, you simply drop the table.

the table name will be the same as the job name.
"call the dbms_datapump apis again"

how ?

"If the job is already running, use dbms_datapump to attach to the job and stop it."

by this : http://blog.oracle48.nl/killing-and-resuming-datapump-expdp-and-impdp-jobs/?
Now I follow this link:http://blog.oracle48.nl/killing-and-resuming-datapump-expdp-and-impdp-jobs/

to try rerun the job again

User generated image
User generated image
i tried both, the job still not restarted at all if check from SQL developer., any reason
?
You cannot rerun a job by attaching to one that's already been run!! If you paused a job, you may resume it, but if you want to rerun, you have to start a "new" job.
"you have to start a "new" job."

This is what I am wondering . is it the problem of oracle or SQL Developer ! a job can't rerun but resume only, make sense ?

and this is what I am doing, keep creating new job !

so what is the way to drop that job ?

I really don't like SQL developer much now, if using default MS SQL tools, already fix this kind of stupid and simple mistake !

in oracle still use command to drop/delete a job ?
This is what I am wondering . is it the problem of oracle or SQL Developer ! a job can't rerun but resume only, make sense ?
This is neither a problem of Oracle nor SQL Developer! It is rather a problem related to yourself in NOT understanding certain processes we tried to explain to you, more than once! Sorry to get a bit harsh here, but you seem to be very stubborn :-(

so what is the way to drop that job ?
Again: you don't need to "drop" an import/export job, just use the command line tools to perform the tasks, that's it!

I really don't like SQL developer much now
See above! For heaven's sake: USE THE TOOLS!!

in oracle still use command to drop/delete a job ?
See above + NO NO NO!!
you said this :

"but if you want to rerun, you have to start a "new" job."

can't rerun a job, so I have to create a new one.
"but if you want to rerun, you have to start a "new" job."

can't rerun a job, so I have to create a new one.
That's just quibbling!! Just call impdp and expdp or use the corresponding UI components from your GUI!
this is the command doesn't work before:

C:\Users\administrator>impdp system ATTACH=IMPORT_CISUAT

Import: Release 12.2.0.1.0 - Production on Thu Oct 11 18:14:20 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit
Production

Job: IMPORT_CISUAT
  Owner: SYSTEM
  Operation: IMPORT
  Creator Privs: TRUE
  GUID: 77287DC7D6E3400DB2FC71D4AAD413C1
  Start Time: Friday, 05 October, 2018 18:10:38
  Mode: FULL
  Instance: orcl12c
  Max Parallelism: 2
  Timezone: +00:00
  Timezone version: 26
  Endianness: LITTLE
  NLS character set: AL32UTF8
  NLS NCHAR character set: AL16UTF16
  EXPORT Job Parameters:
  Parameter Name      Parameter Value:
     CLIENT_COMMAND        cisuat/********@cis full=y directory=export_dir dumpf
ile=cisuat_12c.dmp logfile=cisuat.log
     TRACE                 0
  IMPORT Job Parameters:
  Parameter Name      Parameter Value:
     DATA_ACCESS_METHOD    AUTOMATIC
     INCLUDE_METADATA      1
     KEEP_MASTER           1
     REUSE_DATAFILES       0
     SKIP_UNUSABLE_INDEXES 0
  State: COMPLETING
  Bytes Processed: 61,733,528
  Percent Done: 100
  Current Parallelism: 2
  Job Error Count: 0
  Job heartbeat: 1
  Dump File: D:\app\oracle12c\admin\orcl12c\dpdump\CISUAT_12C.DMP

Worker 1 Status:
  Instance ID: 1
  Instance name: orcl12c
  Host name: SWDNTINTDB92
  Object start time: Friday, 05 October, 2018 18:18:24
  Object status at: Friday, 05 October, 2018 18:18:27
  Process Name: DW00
  State: FAIL
  Object Schema: CISUAT
  Object Name: PCK_SSRS01_UTILS
  Object Type: DATABASE_EXPORT/SCHEMA/PACKAGE/PACKAGE_SPEC
  Completed Objects: 80
  Total Objects: 560
  Worker Parallelism: 1

Worker 2 Status:
  Instance ID: 1
  Instance name: orcl12c
  Host name: SWDNTINTDB92
  Object start time: Friday, 05 October, 2018 18:22:45
  Object status at: Friday, 05 October, 2018 18:24:08
  Process Name: DW01
  State: SUCCESS

Worker 3 Status:
  Instance ID: 1
  Instance name: orcl12c
  Host name: SWDNTINTDB92
  Object start time: Friday, 05 October, 2018 18:20:17
  Object status at: Friday, 05 October, 2018 18:20:18
  Process Name: DW02
  State: FAIL
  Object Schema: CISUAT
  Object Name: PCK_CASB39_UTILS
  Object Type: DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY
  Completed Objects: 23
  Total Objects: 558
  Worker Parallelism: 1

Worker 4 Status:
  Instance ID: 1
  Instance name: orcl12c
  Host name: SWDNTINTDB92
  Object start time: Friday, 05 October, 2018 18:24:05
  Object status at: Friday, 05 October, 2018 18:24:08
  Process Name: DW00
  State: SUCCESS

Open in new window


but nothing started.
Apart from the fact that virtually all workers here have the status "FAIL", why oh why do you want to use "ATTACH=IMPORT_CISUAT"?? any arguable reasons??
I am not sure, worker 2 and 4 was success, right?
I am not sure, worker 2 and 4 was success, right?
If they did anything at all...

I wouldn't mind either way. Again: why do you want to use "ATTACH=IMPORT_CISUAT"?? any arguable reasons??
I follow this :

http://blog.oracle48.nl/killing-and-resuming-datapump-expdp-and-impdp-jobs/

and it said:

> expdp system ATTACH=EXP_FULL

Open in new window


so from trial and error point of view (as I can't see why it doesn't work,) I have to try what make it works.

so by that link I try change expdp to impdp and see if it works.

"If they did anything at all..."

I read it is success and I think it is making my job run again.
I read it is success and I think it is making my job run again.
If I call a PL/SQL block, let's say:
begin null; end;

Open in new window

then its execution will have the status = SUCCESS, but it hasn't done anything ;-)

I follow this :

http://blog.oracle48.nl/killing-and-resuming-datapump-expdp-and-impdp-jobs/

You have to read these guides very carefully!!

Killing or stopping a running datapump job

The difference between Kill and Stop is simple to explain. When killing a job, you won’t be able to resume or start it again. Also logs and dumpfiles will be removed!
but I didn't kill it ! it is stopped as it ran last time.
State: COMPLETING
  Bytes Processed: 61,733,528
  Percent Done: 100
That datapump job seems to be completed....
but when I check the job from SQL developer, that job didn't start at all.
I really suppose it's some kind of problem in understanding the core mechanics here, but for heaven's sake, I have no further clue how I could clarify that to you....
Maybe those docs will help:
https://support.oracle.com/knowledge/Oracle%20Database%20Products/1400974_1.html
https://docs.oracle.com/database/121/SUTIL/GUID-6BDC1CC8-8596-402D-B016-602985B97AB6.htm#SUTIL808
ok. as at this stage, it seems import is doing good.
many tks all.
You're welcome ;-)