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?
Oracle DatabaseSQL

Avatar of undefined
Last Comment
Alex [***Alex140181***]

8/22/2022 - Mon
Sean Stuber

Does SYSTEM own the job?
Mark Geerlings

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.
Alex [***Alex140181***]

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...
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
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:

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 !
ASKER CERTIFIED SOLUTION
Alex [***Alex140181***]

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
marrowyung

ASKER
"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.
Alex [***Alex140181***]

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...
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
marrowyung

ASKER
"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

Alex [***Alex140181***]

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...
marrowyung

ASKER
"
What's your source? Please provide a link or something!"

https://docs.oracle.com/html/E25494_01/scheduse002.htm#i1021522
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Alex [***Alex140181***]

That's the DBMS_SCHEDULER!! And this has NOTHING to do with IMPDP and/or EXPDP!
marrowyung

ASKER
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.
Sean Stuber

>>> 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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
marrowyung

ASKER
sdstuber,


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

how can I drop it too ?
Sean Stuber

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.
marrowyung

ASKER
"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/?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
marrowyung

ASKER
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
?
Alex [***Alex140181***]

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.
marrowyung

ASKER
"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 ?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Alex [***Alex140181***]

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!!
marrowyung

ASKER
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.
Alex [***Alex140181***]

"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!
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
marrowyung

ASKER
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.
Alex [***Alex140181***]

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??
marrowyung

ASKER
I am not sure, worker 2 and 4 was success, right?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Alex [***Alex140181***]

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??
marrowyung

ASKER
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.
Alex [***Alex140181***]

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!
Your help has saved me hundreds of hours of internet surfing.
fblack61
marrowyung

ASKER
but I didn't kill it ! it is stopped as it ran last time.
Alex [***Alex140181***]

State: COMPLETING
  Bytes Processed: 61,733,528
  Percent Done: 100
That datapump job seems to be completed....
marrowyung

ASKER
but when I check the job from SQL developer, that job didn't start at all.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Alex [***Alex140181***]

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
marrowyung

ASKER
ok. as at this stage, it seems import is doing good.
marrowyung

ASKER
many tks all.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Alex [***Alex140181***]

You're welcome ;-)