3 Replies Latest reply: May 21, 2012 6:03 AM by Gerrit Haase RSS

    dba_objects lists object type job, but can not remove it

    Gerrit Haase
      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
          Billy~Verreynne
          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
            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
              Barbara Boehmer wrote:
              If the job was created using dbms_scheduler, then you need to drop it using dbms_scheduler, as shown below.
              Thanks!