Solution to Oracle Stored Procedure compilation failure

 

I sorted out the DB hang issue during the shutdown process yesterday. For details, refer:

Dbhang solution during oracleshutdown

Http://blog.csdn.net/tianlesoftware/article/details/7407587

 

Today, my colleague modified the stored procedure, which made the two processes unable to be compiled. Dave didn't talk about it here to see how to solve the problem.

 

1. View invalid objects

Xezf @ xezf (qs-xezf-db1)> select object_name, object_type, status from all_objects where status = 'invalid' and owner = 'xezf ';

 

Object_name object_type status

--------------------------------------------------------

Proc_ob_to_xezf procedure invalid

Proc_job_ob_write procedure invalid

 

 

The preceding two processes cannot be compiled. We can view the sessions used in these two processes:

 

2. view the session that is accessing an invalid object:

Here we use the V $ access View:

V $ access displaysinformation about locks that are currently imposed on library cache objects. the locks are imposed to ensure that they are not aged out of the library cachewhile they are required for SQL Execution.

 

Xezf @ xezf (qs-xezf-db1)> select * from V $ access where object = 'proc _ ob_to_xezf ';

 

Sid owner object type

------------------------------------------------------------------

958 xezf proc_ob_to_xezf procedure

 

Xezf @ xezf (qs-xezf-db1)> select * from V $ access where object = 'proc _ job_ob_write ';

 

Sid owner object type

------------------------------------------------------------------

958 xezf proc_job_ob_write procedure

 

From the above query results, 958 of sessions are in use.

 

3. view the session Status:

 

Xezf @ xezf (qs-xezf-db1)> select Sid, serial #, status, process from V $ session where Sid = '2013 ';

 

Sid serial # Status Process

----------------------------------------

958 8350 killed 24007

 

From the above results, the session has been marked as killed. The processes marked as killed are killed by the pmon process, but this is also conditional:

Pmon will notdelete the session object itself until the client connected to that sessionnotices that it has been killed.

 

If the session is marked as killed and cannot be identified for a long time, the solution provided on MOS is to kill the process at the OS level. This issue will be explained in another blog.

 

 

4. Get the spid corresponding to the session

 

Xezf @ xezf (qs-xezf-db1)> select spid, osuser, S. Program

2 from V $ session S, V $ PROCESS p

3 where S. paddr = P. ADDR and S. Sid = 958;

-- Specify the session Sid.

 

Spid osuser Program

------------------------------------------------------------------------------

24007 Oracle @ qs-xezf-db1 (j004)

 

 

On the Linux platform, we can kill the process directly:

# Kill-9 24007

 

5. Kill the process

 

It may take a long time to wait for pmon to clean up the process, so here we will manually kill the process. Perform operations at the OS level:

[Oracle @ qs-xezf-db1 ~] $ PS-Ef | grep 24007

Oracle 10031 9299 0 00:00:00 pts/2 grep 24007

Oracle 24007 1 0 mar29? 00:00:01 ora_j004_xezf

 

[Oracle @ qs-xezf-db1 ~] $ Kill-9 24007

[Oracle @ qs-xezf-db1 ~] $ PS-Ef | grep 24007

Oracle 10361 9299 0 00:00:00 pts/2 grep 24007

 

The process has been killed.

 

Verify session:

Xezf @ xezf (qs-xezf-db1)> select Sid, serial #, status, process from V $ session where Sid = '2013 ';

 

Sid serial # Status Process

----------------------------------------

958 8357 inactive 1234

 

 

The compilation process is invalid once.

 

Xezf @ xezf (qs-xezf-db1)> select object_name, object_type, status from all_objects where status = 'invalid' and owner = 'xezf ';

 

No rows selected

 

 

Solve the problem.

 

 

 

 

 

 

Bytes -------------------------------------------------------------------------------------------------------

All rights reserved. reprinted articles are allowed, but source addresses must be indicated by links. Otherwise, the documents will be held legally responsible!

Skype: tianlesoftware

Email: tianlesoftware@gmail.com

Blog: http://www.tianlesoftware.com

WEAVER: http://weibo.com/tianlesoftware

Twitter: http://twitter.com/tianlesoftware

Facebook: http://www.facebook.com/tianlesoftware

LinkedIn: http://cn.linkedin.com/in/tianlesoftware

 

 

------- Add a group to describe the relationship between Oracle tablespace and data files in the remarks section. Otherwise, reject the application ----

Dba1 group: 62697716 (full); dba2 group: 62697977 (full) dba3 group: 62697850 (full)

Super DBA group: 63306533 (full); dba4 group: 83829929 dba5 group: 142216823

Dba6 group: 158654907 dba7 group: 172855474 DBA group: 104207940