Compile stored procedure encounters DDL lock

Sometimes we need to go live on a new version of the stored procedure, perhaps to do a performance optimization or to add or improve the functionality, if the stored procedure is being called by the program, it will take a long time to execute, which may be beyond our online window, what to do, some people may think of the client's connection disconnected, A session-enabled server process will naturally be cleaned up by Pmon, but often backfired, stored procedures are still executing, the most effective way is to find the corresponding session on the server corresponding to the sessions of this session killed, and not only physically kill the server process.
The first thing to do is to find the SID of the server process;
When executing a stored procedure, you need to obtain the DDL on the object, which is obtained in shared mode for the execution session, and the session to which the stored procedure is to be compiled, in exclusive mode.
How do I view the session ID that holds the DDL cable? Oracle provides us with a useful view: Dba_ddl_locks.

The following simulation test, first executes the stored procedure proc_test,

First execute the stored procedure proc_test,

Session S1:

begin  -- Call the procedure   proc_test; End; /

To compile it in session S2

Session S2:

1 Create or Replace procedureProc_test is2N_num Number:=0;3N_cnt Number;4Start_time Number;5End_time Number;6 Begi7.......... Omitted.

Wait is displayed at this time (end, unknown)
The state of a session in the database

Select  from where status='ACTIVE' and module='  PL/SQL Developer';

	Sid	COMMAND	Blocking_session	EVENT
1	45	3		DB file Scattered read
2	434	24	45	Library Cache Pin

It is clear that the SID 45 session is blocking SID 434, which is the session where I want to compile the code.

Check the Dba_ddl_locks view to see more clearly

	session_id	OWNER	NAME	TYPE	Mode_held	mode_requested
1	45	Mhiskfdata	Proc_test	Table/procedure/type	Null	None
2	434	Mhiskfdata	Proc_test	Table/procedure/type	Exclusive	None

V$lock Display as follows

SELECT * from V$lock where SID in (' ', ' 434 ') the order by SID;

ADDR	Kaddr	Sid	TYPE	ID1	ID2	Lmode	REQUEST	CTIME	BLOCK
00002b0774d9c4d8	00002b0774d9c538	45	Tm	96902	0	3	0	2754	0
00000000fa47be08	00000000fa47be60	45	Ae	100	0	4	0	2757	0
00000000f6fc7258	00000000f6fc72d0	45	TX	720925	365753	6	0	2753	0
00000000fa47d320	00000000fa47d378	434	Ae	100	0	4	0	2085	0
00000000fa479120	00000000fa479178	434	Ae	0	1	4	0	226	0

The thing to do now is to kill 45 of the conversation.

Select ' Alter system kill session ' | | sid| | ', ' | | serial#| | ' immediate; ' from V$session where sid= ' 45 ';

1	Alter system kill session ' 45,39175 ' immediate;

Compiling again should be no problem.

If there's a problem, then we have to kill the process.

Select P.spid from V$process p, v$session s where p.addr = S.paddr and s.sid= ' 45 ';

Linux under

Kill-9 &spid.

Compile stored procedure encounters DDL lock