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.

db_name : testdb
Version :   11.2.0.3.0
Server:      Red Hat Enterprise Linux Server release 6.5

Here is video for visual understanding - kill LOCAL=NO processes

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>