The stored procedure of Oracle database compilation is suspended.

Oracle Database tutorial compilation Stored Procedure false

In this case, if the Stored Procedure compilation is forcibly terminated, the Recompile stored procedure will find that it is still suspended. This is mainly because the session is INACTIVE after the forcible termination, however, the session is not actually released. Use the following statement to query a suspended session:

Select v. OSUSER, V. PROCESS, V. PROGRAM, v. MACHINE, V. TERMINAL, V. MODULE, V. USERNAME, V. STATUS, V. SID, V. SERIAL #, 'alter system kill session ''' | V. sid | ',' | V. SERIAL # | '''; 'as KILL_SESSION, V. PADDR

From v $ SESSION V

Where v. username like '% USERNAME %'

And v. PROGRAM like 'sqldev %'

And v. osuser like '%'

And v. STATUS = 'inactive ';

Copy the KILL_SESSION column in the result, execute it under sqlplus, kill the unreleased sessions, and then log on to the recompile stored procedure A under plsql again.

Solution 1:
1. SELECT saddr, sid, serial #, paddr, username, status FROM v $ session
WHERE username IS NOT NULL
Example: saddr, sid, serial #, paddr, username, status FROM v $ session WHERE
Username = 'xn00000419 ';
Saddr sid serial # PADDR USERNAME STATUS
------------------------------------
--------------------------------------
542E0E6C 11 314 542B70E8 eygle inactive 542E5044 18 662 542B6D38 SYS
ACTIVE
2. alter system kill session 'sid, serial #';
Example: alter system kill session '20160301'; (Note: Run in sqlplus)
Result: a message is displayed, indicating that the session to be deleted cannot be released and cannot be unlocked.
========================================================== ========================================================== ================================
Solution 2:
A. Find the SESSION you want to kill and write down paddr.
SELECT sid, username, paddr, status FROM v $ session WHERE username =
'Username ';
Example: SELECT sid, username, paddr, status FROM v $ session WHERE
Username = 'xn00000419 ';
SID USERNAMEPADDRSTATUS
--------------------------------------------------------
10 xn000004192b5e2e2c KILLED
B. Find the spid corresponding to this SESSION.
SELECT * FROM v $ process WHERE addr = 'paddr searched above ';
Example: select addr, PID, SPID, USERNAME, SERIAL #, TERMINAL FROM
V $ process WHERE addr = '2b5e2e2c ';
Addrpid spidusernameserial # TERMINAL
-------------------------------------------------------
----------------
2B5E2E2C44 1204SYSTEM-99 JWC
C. Kill the process identified by the spid.
D:> orakill sid spid (Note: Only the oracle server has the orakill command in the cmd command window)
Example: D:> orakill oemrep 1204
Tip: kill of thread id 1204 in instance oemrep successfully signalled.
Result: The SESSION is successfully deleted and the table is unlocked.