[SOLVED] How long until a job that has finished executing gets removed from dba

How long until a job that has finished executing gets removed from dba_jobs?

If I submit a job via dbms_job.submit and I commit then it runs until completion. How much longer will it be available for me to view on the dba_jobs view and what determines this time?

Solution

As @Kaushik said, the DBMS_JOB package has been superseeded by the DBMS_SCHEDULER package source. They have job names instead of numbers, a proper time scheduler language, and many views to monitor execution etc.

The DBMS_SCHEDULER.CREATE_JOB procedure has even a dedicated parameter for your question called auto_drop:

This flag if TRUE, causes the job to be automatically dropped after it has been completed. source

But you asked for DBMS_JOB, and I am rather sure that the job is not deleted. I just verified that it is removed when the user/schema is dropped.

As admin:

CREATE USER a IDENTIFIED BY a;
GRANT CREATE SESSION, CREATE JOB TO a;

As user a:

DECLARE
  my_job number;
BEGIN
  DBMS_JOB.SUBMIT(job => my_job, 
    what => 'BEGIN DBMS_STATS.GATHER_SCHEMA_STATS(NULL); END;',
    next_date=>sysdate+(1/24/60),
    interval=>'sysdate+(1/24/60)');
  COMMIT;
end;
/

As admin:

SELECT * FROM DBA_JOBS WHERE schema_user='A';
JOB LOG_USER LAST_DATE           NEXT_DATE ...
25  A        29.06.2018 13:46:25 29.06.2018 13:47:25

DROP USER A;
User A dropped.

SELECT * FROM DBA_JOBS WHERE schema_user='A';
no rows returned