How to kill a suspended automatic Materialized View to refresh the job?

How can I kill a suspended automatic Materialized View to refresh the job?

Reference Original:How to kill a hung automated materialized view refresh job (Document ID 341842.1)

Applicable:

Oracle Server-Enterprise Edition-version 8.1.5.0 to 10.2.0.5 [release 8.1.5 to 10.2]
Information in this document applies to any platform.


Objectives:

This article provides a method to refresh a job in the automatically materialized view suspended by kill.
This article can also be used for any suspended job initiated by the Database Job scheduler.

Solution:

1. query the dba_jobs_running view to obtain the SID and job number of the suspended job,
SQL>-set optimizer mode to rule to workaround BUG: 3402490 (not necessary for 10.2)
SQL> alter session set optimizer_mode = rule; ---> this command is executed in DB version <10.2, and is not required in dB 10.2.
SQL> select * From dba_jobs_running;

---> Note bug: The description of 3402490 is: query to dba_jobs_running takes long time.
In a case I encountered, in the 9201 environment, it takes 40 seconds or 1 minute to query dba_jobs_running. The query result can be displayed, but it is 0 rows.

2. Break the job so that another job queue slave process will not try to continue running the suspended job
SQL> exec dbms_job.broken (<job # from step 1>, true );

3. Confirm the OS PID corresponding to the suspended job
SQL> select a. spid, B. Sid, B. Username
From v $ process a, V $ session B
Where a. ADDR = B. paddr and SID = <Sid from step 1>;

4. confirm that the OS PID queried in the previous step is j <NNN> process (9.0.1 and abve) or SNP process (8.1.7 and below ):

For UNIX and Linux:

$ PS-Ef | grep <A. spid returned from step 3>


For Windows:

SQL> select VB. Name Nome, VP. Program processname,
VP. spid threadid, vs. Sid
From v $ session vs, V $ process VP, V $ bgprocess VB
Where VB. paddr <> '00' and
VB. paddr = VP. ADDR and
VP. ADDR = vs. paddr;

5. Kill the OS process queried in step 3 (please be careful to prevent database crash caused by kill of other processes)

For UNIX and Linux:

$ Kill-9 <A. spid returned from step 3>

For Windows:
Use of the orakill utility will be necessary.
See note: 316652.1 for an example of this.

6. Solve the cause of job suspension and submit the SR if necessary.

7. Run and unbreak job

SQL> exec dbms_job.run (job );

This command runs the job and unbreak the job.