Useful shortcuts for vi editor

Monday, 24 October 2016

How to define/check/drop jobs?

Define a new job (will run every hour)
DECLARE
   X   NUMBER;
BEGIN
   BEGIN
      SYS.DBMS_JOB.SUBMIT (
         job         => X,
         what        => 'HR.P_COMPILE_INVALID_OBJECTS;',
         next_date   => TO_DATE ('24.10.2016 10:00:00',
                                 'dd/mm/yyyy hh24:mi:ss'),
         interval    => '(SYSDATE)+1/24',
         no_parse    => FALSE);
      SYS.DBMS_OUTPUT.PUT_LINE ('Job Number is: ' || TO_CHAR (x));
   EXCEPTION
      WHEN OTHERS
      THEN
         RAISE;
   END;

   COMMIT;
END;

Check job parameter

SQL> select * from v$parameter where name like '%job%';

if value is "0":

SQL> alter system set job_queue_processes=20;

Add this setting into init.ora file:

*.job_queue_processes=20

Info: If job_queue_processes parameter value is bigger than "1", jobs runs automatically such as refresh materialized view job. On the other hand, user can run this job manually as well while job_queue_processes="0".


Check current job status 

SQL> select * from user_jobs;
or
SQL> select * from dba_jobs;

Check running jobs 

SQL> select * from dba_jobs_running;
-- take a note that sid output (e.g. 2283)

Get detail info about running job

SQL> SELECT s.username, osuser, process machine, p.terminal, type, sid, s.serial#, s.program
FROM v$session s, v$process p
WHERE s.sid = 2283
AND s.paddr = p.addr;
-- s.program; CQJ0 (Job queue controller), J000, J001 ... J999 (Job Queue) 
-- These are called as background processes

Drop the job with sys or system user (as sysdba)

-- Before do this, make sure related job is broken, no enq. tx row lock session on SYS.JOB$ table that killed.

SQL>
SELECT DISTINCT
          'ALTER SYSTEM KILL SESSION '''
       || b.sid
       || ','
       || b.serial#
       || ''' IMMEDIATE;'
          AS KILL_THEM_ALL
  FROM sys.dba_dml_locks a, sys.v_$session B
 WHERE     B.SID = a.session_id
       AND owner NOT IN ('WMSYS',
                         'CTXSYS',
                         'DBSNMP')
       AND B.TYPE <> 'BACKGROUND'
       AND name = 'JOB$';

-- Run that 
SQL>
begin
sys.dbms_job.broken(1111,true); -- 1111 -> the job id
commit;
end;

SQL>
SELECT DISTINCT
          'ALTER SYSTEM KILL SESSION '''
       || b.sid
       || ','
       || b.serial#
       || ''' IMMEDIATE;'
          AS KILL_THEM_ALL
  FROM sys.v_$session B
 WHERE b.sid = 2283;

SQL>
begin
sys.dbms_job.remove(1111); -- 1111 -> the job id
commit;
end;

Dynamic SQL for dropping jobs with sys or system user (as sysdba)

-- Before do this, make sure related job is broken and session is killed.
-- Run that 
SQL>
SELECT 'begin sys.dbms_ijob.broken(' || job || ', true); commit; end;' || chr(10) || '/' AS DROP_JOBS 
FROM dba_jobs;

SQL>
SELECT DISTINCT
          'ALTER SYSTEM KILL SESSION '''
       || b.sid
       || ','
       || b.serial#
       || ''' IMMEDIATE;'
          AS KILL_THEM_ALL
  FROM sys.v_$session B, dba_jobs_running A
 WHERE a.sid = b.sid;

SQL>
SELECT 'begin sys.dbms_ijob.remove(' || job || '); commit; end;' || chr(10) || '/' AS DROP_JOBS 
FROM dba_jobs;
-- ijob uses for all jobs in dbms_scheduler (new version of dbms_jobs)

Info: Please click this for P_COMPILE_INVALID_OBJECTS