This discussion is archived
3 Replies Latest reply: May 21, 2012 4:03 AM by Gerrit Haase RSS

dba_objects lists object type job, but can not remove it

Gerrit Haase Newbie
Currently Being Moderated
Edit: DB is 11.2.0.3 Standard edition.

I see an object of type JOB, and I cannot just drop it, but there is nothing in the dba_jobs, so dbms_job.remove(<1>) doesn't work, how do I get rid of this object?


select * from dba_objects where owner = 'XYZ_SYS'
and object_type not in ('TABLE', 'INDEX', 'DATABASE LINK', 'JAVA CLASS', 'JAVA RESOURCE')
/

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED LAST_DDL TIMESTAMP STATUS T G S NAMESPACE
-------- -------- ------------------- ------- - - - ----------
EDITION_NAME
------------------------------
XYZ_SYS
ABC_JOB_START
16646 JOB
11.05.12 11.05.12 2012-05-11:15:28:47 VALID N N N 1


set lines 100 pages 999
col     schema_user format a15
col     fails format 999
select     job
,     schema_user
,     to_char(last_date, 'hh24:mi dd/mm/yy') last_run
,     to_char(next_date, 'hh24:mi dd/mm/yy') next_run
,     failures fails
,     broken
,     substr(what, 1, 15) what
from     dba_jobs
order by 4
/

No lines ...

Any hint, please.

Gerrit

Edited by: Gerrit Haase on 18.05.2012 11:02
  • 1. Re: dba_objects lists object type job, but can not remove it
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Gerrit Haase wrote:
    Edit: DB is 11.2.0.3 Standard edition.

    I see an object of type JOB, and I cannot just drop it, but there is nothing in the dba_jobs, so dbms_job.remove(<1>) doesn't work, how do I get rid of this object?
    The DBMS_JOB interface is a PL/SQL package that enables you to schedule background processes. Have you bothered to read the Oracle® Database PL/SQL Packages and Types Reference that describes the DBMS_JOB interface?

    To drop an ADT/UDT (Advance/User Data Type), the DROP TYPE command needs to be used.
  • 2. Re: dba_objects lists object type job, but can not remove it
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    If the job was created using dbms_scheduler, then you need to drop it using dbms_scheduler, as shown below.
    SCOTT@orcl_11gR2> select * from dba_objects where owner = 'SCOTT' and object_type = 'JOB'
      2  /
    
    OWNER
    ------------------------------
    OBJECT_NAME
    --------------------------------------------------------------------------------
    SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
    ------------------------------ ---------- -------------- -------------------
    CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S  NAMESPACE
    --------- --------- ------------------- ------- - - - ----------
    EDITION_NAME
    ------------------------------
    SCOTT
    ABC_JOB_START
                                       295791                JOB
    19-MAY-12 19-MAY-12 2012-05-19:15:02:01 VALID   N N N          1
    
    
    
    1 row selected.
    
    SCOTT@orcl_11gR2> exec dbms_scheduler.drop_job ('ABC_JOB_START')
    
    PL/SQL procedure successfully completed.
    
    SCOTT@orcl_11gR2> select * from dba_objects where owner = 'SCOTT' and object_type = 'JOB'
      2  /
    
    no rows selected
  • 3. Re: dba_objects lists object type job, but can not remove it
    Gerrit Haase Newbie
    Currently Being Moderated
    Barbara Boehmer wrote:
    If the job was created using dbms_scheduler, then you need to drop it using dbms_scheduler, as shown below.
    Thanks!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points