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?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
Does SYSTEM own the job?
0
Mark GeerlingsDatabase AdministratorCommented:
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.
1
Alexander Eßer [Alex140181]Software DeveloperCommented:
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...
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

marrowyungSenior Technical architecture (Data)Author Commented:
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:

import job.
 I can see job status from SQL developer :

job status
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 !
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
this shows empty, but the UI shows:
My bad, the following view shows those jobs:
select * from dba_datapump_jobs

Open in new window


I doubt that SQL Developer (or some other IDE) enables you to (re)run a Data Pump import or export job via UI since this is done from the command line tools, like it is shown here, for instance:
http://blog.oracle48.nl/killing-and-resuming-datapump-expdp-and-impdp-jobs/
https://naya.academy/run-an-oracle-datapump-job-using-plsql/

the import of DMP file from using SQL  developer is much easy than using impdp !
Sure, this is always a personal opinion, but I totally disagree ;-)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
"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.
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
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...
0
marrowyungSenior Technical architecture (Data)Author Commented:
"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

0
Alexander Eßer [Alex140181]Software DeveloperCommented:
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...
0
marrowyungSenior Technical architecture (Data)Author Commented:
"
What's your source? Please provide a link or something!"

https://docs.oracle.com/html/E25494_01/scheduse002.htm#i1021522
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
That's the DBMS_SCHEDULER!! And this has NOTHING to do with IMPDP and/or EXPDP!
0
marrowyungSenior Technical architecture (Data)Author Commented:
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.
0
sdstuberCommented:
>>> 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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
sdstuber,


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

how can I drop it too ?
0
sdstuberCommented:
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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"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/?
0
marrowyungSenior Technical architecture (Data)Author Commented:
Now I follow this link:http://blog.oracle48.nl/killing-and-resuming-datapump-expdp-and-impdp-jobs/

to try rerun the job again

restart
restart
i tried both, the job still not restarted at all if check from SQL developer., any reason
?
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"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 ?
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
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!!
0
marrowyungSenior Technical architecture (Data)Author Commented:
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.
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
"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!
0
marrowyungSenior Technical architecture (Data)Author Commented:
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.
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
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??
0
marrowyungSenior Technical architecture (Data)Author Commented:
I am not sure, worker 2 and 4 was success, right?
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
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??
0
marrowyungSenior Technical architecture (Data)Author Commented:
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.
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
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!
0
marrowyungSenior Technical architecture (Data)Author Commented:
but I didn't kill it ! it is stopped as it ran last time.
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
State: COMPLETING
  Bytes Processed: 61,733,528
  Percent Done: 100
That datapump job seems to be completed....
0
marrowyungSenior Technical architecture (Data)Author Commented:
but when I check the job from SQL developer, that job didn't start at all.
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
0
marrowyungSenior Technical architecture (Data)Author Commented:
ok. as at this stage, it seems import is doing good.
0
marrowyungSenior Technical architecture (Data)Author Commented:
many tks all.
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
You're welcome ;-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.