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>
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>
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
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>'ALTER SYSTEM KILL SESSION '''
|| b.sid
|| ','
|| b.serial#
|| ''' IMMEDIATE;'
AS KILL_THEM_ALL
FROM sys.v_$session B
WHERE b.sid = 2283;
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
SELECT 'begin sys.dbms_ijob.remove(' || job || '); commit; end;' || chr(10) || '/' AS DROP_JOBS
'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>
SQL>
FROM dba_jobs;
-- ijob uses for all jobs in dbms_scheduler (new version of dbms_jobs)