You can find all columns from dba_jobs view below:
SQL> desc dba_jobs
Name Null? Type
----------------------------------------- -------- ---------------------
JOB NOT NULL NUMBER
LOG_USER NOT NULL VARCHAR2(30)
PRIV_USER NOT NULL VARCHAR2(30)
SCHEMA_USER NOT NULL VARCHAR2(30)
LAST_DATE DATE
LAST_SEC VARCHAR2(8)
THIS_DATE DATE
THIS_SEC VARCHAR2(8)
NEXT_DATE NOT NULL DATE
NEXT_SEC VARCHAR2(8)
TOTAL_TIME NUMBER
BROKEN VARCHAR2(1)
INTERVAL NOT NULL VARCHAR2(200)
FAILURES NUMBER
WHAT VARCHAR2(4000)
NLS_ENV VARCHAR2(4000)
MISC_ENV RAW(32)
INSTANCE NUMBER
SQL>
SQL> select job from dba_jobs;
JOB
----------
40
22
65
41
If you need to stop all jobs temporary you can do the following:
SQL> Show parameter job_queue_processes
NAME TYPE VALUE
------------------------------------ ----------- -------------------------
job_queue_processes integer 10
SQL>alter system set job_queue_processes = 0 scope=both;
Reset the parameter to what it was later.
If a job fails to execute after 16 attempts Oracle will mark it as broken and stop running it.
This will be indicated by a ‘Y’ in the BROKEN column of the dba_jobs view.
If you want to tell Oracle to not run an specific job you can manually mark it as broken by executing the following:
BEGIN
DBMS_JOB.BROKEN(JOB => 22, BROKEN => TRUE);
END;
/
It will be remained broken and will not be run until you either force it to run or mark it to not broken.
When marking it as not broken you must also specify the next date for the job to run.
The following will mark job 22 as not broken and have it execute at 10:00pm on Feb 20.
BEGIN
DBMS_JOB.BROKEN(JOB => 22, BROKEN => FALSE, NEXT_DATE => TO_DATE('22:00 02/20/12', 'HH24:MI MM/DD/YY'));
END;
/
Or
you jut can start the job based on it's schedule:
EXECUTE DBMS_JOB.BROKEN(JOB => 22, BROKEN => FALSE);
commit;
Or
You asked to kill whole session:
First, find the session number and sid id from v$session then:
To remove a job from Oracle job queue you need the job number.
If you’re not sure what to do, you should be able to find it by querying from dba_jobs view.
When you have the job number, run the following command:
BEGIN
DBMS_JOB.REMOVE(JOB => 22);
END;
/
Or
To completely remove a job:
SQL> execute dbms_job.remove(22);
If you asked to kill a session in windows you can find the tread first and then kill by orakill command:
First find the sid and thread you want to kill by using the following query:
SELECT sid, spid as thread, osuser, s.program
FROM sys.v_$process p, sys.v_$session s
WHERE p.addr = s.paddr
Then run the following for Oracle on Windows :
C:\>orakill sid thread
For example:C:\>orakill ORCL 4760
Kill of thread id 4760 in instance ORCL successfully signalled.
Attention: the sid here is the name given to database.
As we see above, you might ask why Oracle provide orakill utility when you can kill a session by alter system kill.
1) The alter system will not clear any lock and the session will be remained until
it times out. But orakill kill the session instantly.
2)If a DBA can't have access to Sql Plus because all resource is in used then the session can be killed without longing to database.
Unlike the Unix, windows is thread-based which means for each instance all background processes and sessions has oracle.exe process, then you need to find the thread number to kill specific session. On the other hand orakill serves the action "kill -9 " does in Unix.
Related link:
