Windows中ORA-00020的解决过程

最新推荐文章于 2022-05-06 17:49:14 发布

lwei_998

最新推荐文章于 2022-05-06 17:49:14 发布

阅读量6.9k

点赞数

文章标签： windows thread sql oracle session tcp

本文链接：https://blog.csdn.net/lwei_998/article/details/6145207

版权

今天在windows7测试中遇到了ORA-00020错误。

下面模拟一下解决过程
C:/Users/Liangwei>sqlplus user/password
SQL*Plus: Release 11.2.0.1.0 Production on 星期日 1月 16 16:16:26 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-00020: maximum number of processes (150) exceeded

此时使用：sqlplus / as sysdba 或 sqlplus -prelim / as sysdba 同样无法登陆

C:/Users/Liangwei>netstat -bo
活动连接
协议本地地址          外部地址        状态           PID
TCP    [fe80::942a:8432:c23f:69ff%19]:1521 SS-LiangWei:50248      ESTABLISHED     2528
[TNSLSNR.exe]
TCP    [fe80::942a:8432:c23f:69ff%19]:1521 SS-LiangWei:50350      ESTABLISHED     2528
[TNSLSNR.exe]
TCP    [fe80::942a:8432:c23f:69ff%19]:50248 SS-LiangWei:1521       ESTABLISHED     2604
[ORACLE.EXE]
TCP    [fe80::942a:8432:c23f:69ff%19]:50350 SS-LiangWei:1521       ESTABLISHED     3920
[plsqldev.exe]

C:/Users/Liangwei>taskkill /pid 3920
成功: 给进程发送了终止信号，进程的 PID 为 3920。

-- 杀掉pid为3920的进程后，可以登录。
--只要可以进入oracle我们就可以利用orakill工具来为所欲为，呵呵。

C:/Users/Liangwei>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on 星期日 1月 16 16:17:35 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL> COL SQL FOR A22;
SQL> COL SPID FOR A5;
SQL> COL OSUSER FOR A20;
SQL> COL PROGRAM FOR A12;
SQL> select ' orakill ' || (select instance_name from v$instance) || ' '|| spid SQL ,
2         spid,
3         osuser,
4         s.program
5    from v$process p, v$session s
6   where p.addr = s.paddr
7   and osuser!='SYSTEM';

SQL                                     SPID OSUSER                       PROGRAM
----------------------               -----    --------------------            ------------
orakill liangwei 3508        3508 SS-LIANGWEI/Liangwei   plsqldev.exe
orakill liangwei 3336        3336 SS-LIANGWEI/Liangwei sqlplus.exe
orakill liangwei 868        868   SS-LIANGWEI/Liangwei    plsqldev.exe
orakill liangwei 2324        2324 SS-LIANGWEI/Liangwei   sqlplus.exe
orakill liangwei 3576       3576 SS-LIANGWEI/Liangwei    plsqldev.exe

SQL>host orakill
Usage: orakill sid thread
where sid    = the Oracle instance to target
        thread = the thread id of the thread to kill
The thread id should be retrieved from the spid column of a query such as:
        select spid, osuser, s.program from
        v$process p, v$session s where p.addr=s.paddr

SQL> host orakill liangwei 3508
Kill of thread id 3508 in instance liangwei successfully signalled.

SQL> /

SQL                                SPID OSUSER                          PROGRAM
----------------------          ----- -------------------------------    ------------
orakill liangwei 3336   3336 SS-LIANGWEI/Liangwei   sqlplus.exe
orakill liangwei 868      868   SS-LIANGWEI/Liangwei    plsqldev.exe
orakill liangwei 2324 2324 SS-LIANGWEI/Liangwei    sqlplus.exe