How to terminate a session on a specific RAC instance

In many cases, we need to use alter system kill session to terminate a session in the database and release the resources occupied by the session to solve the problem. For example, daniel's environment often uses the following statement to kill sessions in the database in batches. Select 'alter system kill session ''' | B. sid | ',' | B. serial # | '''; 'from gv $ access a, gv $ session bwhere. SID = B. SIDand B. program like 'dis % 'group by B. sid, B. serial #; this script is very effective in the case of a single instance (replace all gv $ with v $) and can indeed kill the required session. However, in the RAC environment, the generated scripts must be executed on all instances to kill all the sessions to be killed. Let's take a look at The alter system kill session Syntax: The kill session clause lets you mark a session as terminated, roll back ongoing transactions, release all session locks, and partially recover session resources. to use this clause, your instance must have the database open. your session and the session to be terminated must be on the same instance unless you specify integer3.You must identify the session wit H the following values from the V $ SESSION view: kill session 'integer1, integer2 [, @ integer3] '[IMMEDIATE] For integer1, specify the value of the SID column. for integer2, specify the value of the SERIAL # column. for the optional integer3, specify the ID of the instance where the target session to be killed exists. you can find the instance ID by querying the GV $ tables. IMMEDIATE Specify IMMEDIATE To instruct Oracle Database to roll back ongoing transactions, release all session locks, recover the entire session state, and return control to you immediately. in the RAC environment, we use the gv $ session view to obtain the relevant session information. In addition to obtaining the session sid and serial #, we also need to obtain the inst_id that the session runs, in the alter system kill session clause, specify @ inst_id to kill the session. Otherwise, you must connect to the corresponding RAC instance to kill the session. Example: run the following session on node 1 in the RAC environment: 21: 15: 16 sys @ RAC> select sid, SERIAL #, INST_ID from gv $ session where username = 'Scott '; sid serial # INST_ID ---------- 158 75 1 run the following statement on node 2 in the RAC environment to terminate the session: node 2: 21: 17: 32 sys @ RAC> alter system kill session '2017, 75, @ 1'; System altered. node 1: 21: 19: 26 scott @ RAC> select * from dual; select * from dual * ERROR at line 1: ORA-00028: your session has been killed in RAC environment, the batch script should be modified to the following format: col kill_session for a50select 'alter system kill session ''' | sid | ',' | serial # | ', @ '| inst_id |'; 'kill_session from gv $ session where username = 'Scott ';