对于存储过程编译失败问题https://www.cndba.cn/hbhe0316/article/4932
1.查看无效对象https://www.cndba.cn/hbhe0316/article/4932
SQL > select object_name,object_type,status from all_objects where status='INVALID' and owner='HBHE';
OBJECT_NAME OBJECT_TYPE STATUS
------------------------------------------------- -------
PROC_TS_TO_XFTG PROCEDURE INVALID
PROC_TS_TO_WRITE PROCEDURE INVALID
2.查看正在访问无效对象session,查询结果,是1001的session 在使用。
SQL > Select * from v$access where object='PROC_TS_TO_XFTG';
SID OWNER OBJECT TYPE
---------- -------------------------------- ------------------------
1001 HBHE PROC_TS_TO_XFTG PROCEDURE
SQL > Select * from v$access where object='PROC_TS_TO_WRITE';
SID OWNER OBJECT TYPE
---------- -------------------------------- ------------------------
1001 HBHE PROC_TS_TO_WRITE PROCEDURE
3.查看session 状态:
SQL > select sid,serial#,status,process from v$session where sid='1001';
SID SERIAL# STATUS PROCESS
---------- ---------- -------- ------------
1001 8350 KILLED 24007
从以上结果看,这个session 已经被标记为killed状态了。 被标记为killed 的进程由PMON 进程kill,但是这个也是有条件的:https://www.cndba.cn/hbhe0316/article/4932
PMON will notdelete the session object itself until the client connected to that sessionnotices that it has been killed.https://www.cndba.cn/hbhe0316/article/4932
如果session 被标记为killed,并且长时间不能被清除,只能在OS级别kill 进程
4.获取session 对应的SPID
SQL > select spid,osuser, s.program from v$session s,v$process p where s.paddr=p.addr and s.sid=1001;
--指定session的SID
SPID OSUSER PROGRAM
------------ ------------------------------------------------------------------
25555 oracle oracle@qs-HBHE-db1 (J004)
5.Kill 进程https://www.cndba.cn/hbhe0316/article/4932
如果等PMON来清理这个进程可能需要很长时间,所以这里我们手工的kill 进程。 在OS级别进行操作:https://www.cndba.cn/hbhe0316/article/4932
[oracle@node01 ~]$ ps -ef|grep 25555
oracle 10031 9299 0 17:39 pts/2 00:00:00 grep 25555
oracle 25555 1 0 Mar29 ? 00:00:01 ora_j004_HBHE
[oracle@node01 ~]$ kill -9 25555
[oracle@node01 ~]$ ps -ef|grep 25555
oracle 10361 9299 0 17:54 pts/2 00:00:00 grep 25555
进程已经被kill掉了。https://www.cndba.cn/hbhe0316/article/4932
6.验证session:
SQL > select sid,serial#,status,process from v$session where sid='1001';
SID SERIAL# STATUS PROCESS
---------- ---------- -------- ------------
1001 8357 INACTIVE 1234
7.在次就可以成功编译无效的过程
SQL >select object_name,object_type,status from all_objects where status='INVALID' and owner='HBHE';
no rows selected
版权声明:本文为博主原创文章,未经博主允许不得转载。
oracle,linux