Home » RDBMS Server » Server Administration » How to remove this job?
How to remove this job? [message #288209] Sat, 15 December 2007 21:22 Go to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Hi all!

I created a job in the test database
create or replace procedure kill_session
is
sql_stmt varchar2(300);
s1 VARCHAR (150);
 s2 VARCHAR (150);
 cursor my_curr is select  sid,serial#  from V$SESSION
        where      status='INACTIVE' AND
                    TO_CHAR(SYSDATE,'MI') - TO_CHAR(LOGON_TIME,'MI') >=60;
   begin
    OPEN my_curr;
  FETCH my_curr
   INTO s1,s2;
  WHILE my_curr%FOUND
  LOOP  
  execute immediate ('alter system kill session '''||s1||','||s2||'''');
      FETCH my_curr
      INTO s1,s2;      
  END LOOP;
end;   


and, I submitted this job

DECLARE
JobNo NUMBER;
v_date date;
BEGIN
 dbms_job.submit(JobNo, 'begin kill_session; end;', SYSDATE,
'(sysdate + 3600/(24*60*60))');
 COMMIT;
END;


And now, I want to remove this job from job queue, but I..don't know how to do it. Embarassed

Following the Oracle document which guides that finding the number of job being run, and use to these command
Begin
DBMS_JOB.REMOVE(job_number)
commit;
end;
/


But I don't know how to find it. Pls teach me how to remove this job.

Thank so much!
Re: How to remove this job? [message #288210 is a reply to message #288209] Sat, 15 December 2007 21:30 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
select job, broken, what from dba_jobs;

[Updated on: Sat, 15 December 2007 21:32] by Moderator

Report message to a moderator

Re: How to remove this job? [message #288211 is a reply to message #288209] Sat, 15 December 2007 22:05 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Thank you, anna!

I just removed it following your guide!

However, it's not seem like to the relation problem according to alert log file

Sun Dec 16 10:50:30 2007
Process P072 died, see its trace file
Sun Dec 16 10:50:30 2007
Process startup failed, error stack:
Sun Dec 16 10:50:30 2007
Errors in file c:\oracle\product\10.2.0\admin\vnp\bdump\vnp_psp0_2260.trc:
ORA-27300: OS system dependent operation:spcdr:9261:4200 failed with status: 997
ORA-27301: OS failure message: Overlapped I/O operation is in progress.
ORA-27302: failure occurred at: skgpspawn

Sun Dec 16 10:50:31 2007
Process P071 died, see its trace file
Sun Dec 16 10:50:31 2007
Process startup failed, error stack:
Sun Dec 16 10:50:31 2007
Errors in file c:\oracle\product\10.2.0\admin\vnp\bdump\vnp_psp0_2260.trc:
ORA-27300: OS system dependent operation:spcdr:9261:4200 failed with status: 997
ORA-27301: OS failure message: Overlapped I/O operation is in progress.
ORA-27302: failure occurred at: skgpspawn
Re: How to remove this job? [message #288213 is a reply to message #288209] Sat, 15 December 2007 22:14 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
After 100+ posts you choose to not post basic information such as OS name & version and Oracle version.

You are incapable or unwilling to use GOOGLE which reports bugs which are OS & version dependent.

You're On Your Own (YOYO)!
Re: How to remove this job? [message #288215 is a reply to message #288213] Sat, 15 December 2007 22:33 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Hi ana!
I am sorry for my careless reply!

My OS is 2003 Server - 32 bit
My Database is Oracle 10.2.0.2

Brief of memory
sga_max_size                         big integer 1504M
sga_target                           big integer 1504M
db_cache_size=1249902592
java_pool_size=16777216
large_pool_size=33554432
shared_pool_size=268435456
streams_pool_size=0


I am using SGA_TARGET feature, Automatic Memory Management.

This Database was configured with shared server
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------
max_shared_servers                   integer     20
shared_server_sessions               integer
shared_servers                       integer     10
SQL> show parameter dispatcher

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------
dispatchers                          string      (protocol=TCP)(disp=15)
max_dispatchers                      integer     25
SQL>


I've been being searcher in the Google, something's useful, some thing else not. All of them directly navigated to the Oracle bug

Quote:


Bug 3411021



And, the Note 371074.1 in Metalink describes this bug, I learn some thing, but I am not sure about this resolution. If it's the bug, I think, no, I suspect it may not generate something like

Quote:


Sun Dec 16 11:23:57 2007
Process P069 died, see its trace file
Sun Dec 16 11:28:47 2007
Errors in file c:\oracle\product\10.2.0\admin\vnp\bdump\vnp_j000_5336.trc:
ORA-04030: out of process memory when trying to allocate 16396 bytes (koh-kghu sessi,plsql callback row counter)



May be the OS error?
Re: How to remove this job? [message #288216 is a reply to message #288209] Sat, 15 December 2007 22:38 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>ORA-04030: out of process memory when trying to allocate 16396 bytes (koh-kghu sessi,plsql callback row counter)

Does Metalink find anything useful search on string above?

My initial guess is that PGA might be too small.
Re: How to remove this job? [message #288218 is a reply to message #288216] Sat, 15 December 2007 22:50 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Thank anna for your time!

This error is claimed by many people, especially in the OS 2003.

I am agreement with you, may be, it's the small size of PGA
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
pga_aggregate_target                 big integer 501M
SQL>


I am trying to increase pga_aggreate_target to 800M, decrease the hash_area_size which is value
Quote:


NAME TYPE VALUE
------------------------------------ ----------- -------
hash_area_size integer 131072



now.

In the Oracle 9i, I should decrease the sort_area_size, however, I am using Oracle 10g, so, with the workarea_size set by ON, what do I do with this parameter?
Re: How to remove this job? [message #288220 is a reply to message #288209] Sat, 15 December 2007 22:57 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/memory.htm#i49320
I run Linux exclusively, so can't do too much on Windoze issues.
Re: How to remove this job? [message #288221 is a reply to message #288218] Sat, 15 December 2007 23:00 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

You should contact Oracle Support.
and for pga, you enable automatic pga setting if yes then just increase "pga_aggregate_size" parameter and don't touch "sort_area_size" or "other pga related parameter. Oracle will automatic adjust value for this.

Re: How to remove this job? [message #288222 is a reply to message #288221] Sat, 15 December 2007 23:09 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Yeap!
Thank for your answers!

I know that, if I used AMM, all of the component which is controlled by AMM.
And I must find the exactly problem and resolution.

Thank again!
Re: How to remove this job? [message #288233 is a reply to message #288222] Sun, 16 December 2007 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is this related to your previous topic ORA-04030 error in Windows 2003!?
Does removing the dedicated sessions remove this problem?

Regarding your job, you can most efficiently achieve the same thing using profiles.

Regards
Michel
Re: How to remove this job? [message #288283 is a reply to message #288209] Sun, 16 December 2007 22:37 Go to previous message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

ORA-04030 is not a bug. Check the maximum limit of memory that is specified from OS.
Previous Topic: Can't create index as user
Next Topic: Tablespace Extend error
Goto Forum:
  


Current Time: Sun Dec 04 00:47:45 CST 2016

Total time taken to generate the page: 0.14089 seconds