Monday, March 09, 2015

Terminate Oracle Session on Amazon RDS

To kill an Oracle session on Amazon RDS instance, in stead of using "alter system kill session", we use the procedure provided by Amazon rdsadmin.rdsadmin_util.kill(sid, serial#).
First, we find out the sid and serial# for the session that we want to terminated. We log in as the master user of an Amazon RDS Oracle instance.
SQL> select username, sid, serial#, command, status from v$session;

USERNAME                              SID    SERIAL#    COMMAND STATUS
------------------------------ ---------- ---------- ---------- --------
TESTPROD723                            12      56369          3 ACTIVE
                                       13         17          0 ACTIVE
RDSADMIN                               15         27          0 INACTIVE
TEST1001                               21      12787          0 INACTIVE
                                       28      50603          0 ACTIVE
                                      609          1          0 ACTIVE
                                      610          1          0 ACTIVE
                                      611          1          0 ACTIVE
                                      612          1          0 ACTIVE
                                      613          1          0 ACTIVE
                                      614          1          0 ACTIVE
                                      615          1          0 ACTIVE
                                      616        103          0 ACTIVE
                                      618          3          0 ACTIVE
                                      619          7          0 ACTIVE
                                      622          1          0 ACTIVE
                                      623          3          0 ACTIVE
                                      625        131          0 ACTIVE
TEST3                                 627      35083         74 ACTIVE

29 rows selected.
We are not allowed to run "alter system kill session".
SQL> alter system kill session '627,35083';
alter system kill session '627,35083'
*
ERROR at line 1:
ORA-01031: insufficient privileges
We can terminate a session using rdsadmin.rdsadmin_util.kill
SQL> exec rdsadmin.rdsadmin_util.kill(627,35083);

PL/SQL procedure successfully completed.

No comments: