How to kill all users processes LOCAL=NO

We often get questions about how to kill all users processes with single command and remain database in open mode.Here I write how to kill all LOCAL=NO processes in oracle database, it means kill every process except oracle background processes, which are not marked as LOCAL=NO and database will go in normal mode without crash. Of course our database is installed on unix (Linux) system and every oracle process have it's operating system process.

Login on server where database reside with database software owner user and find all users processes with command :

ps -ef | grep LOCAL=NO | grep testdb

NOTE : If you have multiple database on same server ensure that your ORACLE_SID is set correctly.

kill all LOCAL=NO processes with command:

[oracle@testdb ~]$ export ORACLE_SID=testdb
[oracle@testdb ~]$ kill -9 `ps -ef | grep LOCAL=NO | grep $ORACLE_SID | grep -v grep | awk '{print $2}'`

Demo :

[oracle@testdb ~]$ ps -ef | grep LOCAL=NO | grep testdb
oracle    3368     1  0 13:19 ?        00:00:00 oracletestdb (LOCAL=NO)
oracle    3374     1  0 13:20 ?        00:00:00 oracletestdb (LOCAL=NO)
oracle    3401     1  0 13:20 ?        00:00:00 oracletestdb (LOCAL=NO)
oracle    3407     1  0 13:21 ?        00:00:00 oracletestdb (LOCAL=NO)
oracle    3409     1  0 13:21 ?        00:00:00 oracletestdb (LOCAL=NO)
oracle    3411     1  0 13:21 ?        00:00:00 oracletestdb (LOCAL=NO)
oracle    3413     1  0 13:21 ?        00:00:00 oracletestdb (LOCAL=NO)
oracle    3415     1  0 13:21 ?        00:00:00 oracletestdb (LOCAL=NO)
oracle    3418     1  0 13:21 ?        00:00:00 oracletestdb (LOCAL=NO)
oracle    3420     1  0 13:21 ?        00:00:00 oracletestdb (LOCAL=NO)
oracle    3422     1  0 13:21 ?        00:00:00 oracletestdb (LOCAL=NO)
oracle    3424     1  0 13:21 ?        00:00:00 oracletestdb (LOCAL=NO)
oracle    3426     1  0 13:21 ?        00:00:00 oracletestdb (LOCAL=NO)
oracle    3428     1  0 13:21 ?        00:00:00 oracletestdb (LOCAL=NO)
oracle    3430     1  0 13:21 ?        00:00:00 oracletestdb (LOCAL=NO)
[oracle@testdb ~]$ echo $ORACLE_SID
testdb
[oracle@testdb ~]$ export ORACLE_SID=testdb
[oracle@testdb ~]$ kill -9 `ps -ef | grep LOCAL=NO | grep $ORACLE_SID | grep -v grep | awk '{print $2}'`
[oracle@testdb ~]$ ps -ef | grep LOCAL=NO | grep testdb
[oracle@testdb ~]$
[oracle@testdb ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Dec 22 13:33:20 2017

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL>