Category: SQL Scripts For DBA

How to Kill Oracle RAC Sessions

Kill a session on any RAC instance:
SQL>alter system kill session ‘SID, SERIAL#,@INST_ID’ immediate;

We know the syntax for killing a session is :

SQL>alter system kill session 'SID, SERIAL#' immediate;

In RAC environment, the syntax will be :

SQL>alter system kill session 'SID, SERIAL#,@INST_ID' immediate;

Kill all the sessions from username ‘TESTUSER’:

SQL> select 'alter system kill session '||''''||sid||','||serial#||','
            ||'@'||INST_ID||''''||' immediate;' 
     from gv$session 
    where USERNAME='TESTUSER' order by INST_ID;

'ALTERSYSTEMKILLSESSION'||''''||SID||','||SERIAL#||','||'@'||INST_ID||''''||'IMMEDIATE;'
--------------------------------------------------------------------------
alter system kill session '3839,1429,@1' immediate;
alter system kill session '3269,62522,@1' immediate;
alter system kill session '2135,2483,@1' immediate;
alter system kill session '1994,10353,@1' immediate;
alter system kill session '1144,30683,@1' immediate;
alter system kill session '1003,59576,@1' immediate;
alter system kill session '4,33789,@1' immediate;
alter system kill session '4268,11193,@1' immediate;
alter system kill session '2846,21471,@2' immediate;
alter system kill session '2987,43213,@2' immediate;
alter system kill session '3983,62058,@2' immediate;
alter system kill session '4122,56820,@2' immediate;
alter system kill session '4125,54104,@2' immediate;
alter system kill session '4263,55362,@2' immediate;
alter system kill session '4268,43052,@2' immediate;
alter system kill session '2704,41337,@2' immediate;
alter system kill session '2564,24435,@3' immediate;
alter system kill session '1851,17069,@3' immediate;
alter system kill session '857,25207,@3' immediate;
alter system kill session '4408,37890,@3' immediate;
alter system kill session '576,38233,@4' immediate;
alter system kill session '432,5600,@4' immediate;
alter system kill session '6,29398,@4' immediate;
alter system kill session '719,42236,@4' immediate;

24 rows selected.

Check sessions are killed :

SQL> select username, machine,count(*) 
       from gv$session 
      where USERNAME = 'TESTUSER' 
   group by username, machine;

no rows selected

Check Tablespace Space Usage by Querying DBA_TABLESPACE_USAGE_METRICS

Quick way to know tablespace space usage by querying “DBA_TABLESPACE_USAGE_METRICS”.

DBA need know a tablespace space usage.  There are a number of ways to do so by joining below different views:

DBA_TABLESPACE
DBA_DATA_FILES
DBA_FREE_SPACE
…
..
.

There is a quicker way to do so by querying DBA_TABLESPACE_USAGE_METRICS.

SQL> desc DBA_TABLESPACE_USAGE_METRICS
 Name               Null?   Type
 ----------------  -------  -----------
 TABLESPACE_NAME            VARCHAR2(30)
 USED_SPACE                 NUMBER
 TABLESPACE_SIZE            NUMBER
 USED_PERCENT               NUMBER

SQL> select * from DBA_TABLESPACE_USAGE_METRICS;

TABLESPACE_NAME  USED_SPACE   TABLESPACE_SIZE   USED_PERCENT
---------------- ------------ ----------------- ------------
PSAPP                6255360            7635328 81.9265394
PSAPPIDX             7025984           11028864 63.7054188
PSHUGE01            28452352           41633280 68.3404046
PSHUGE01IDX         33479424           46721792 71.6569775
PSHUGE02             4750856           11776000 40.3435462
PSHUGE02IDX          3838600            9728000 39.4592928
PSIBTRAN             1822832            2621440 69.5355225

Please note :

  •  USED_SPACE and  TABLESPACE_SIZE are in blocks.
  • TABLESPACE_SIZE  is the maximum possible size if AUTO extended on,  not the current size.  The same applies to USED_PERCENT.

Joining DBA_TABLESPACE_USAGE_METRICS with DBA_TABLESPACE,  we can get easily readable size in MB instead of default blocks.

SQL>SELECT a.tablespace_name,
  ROUND((a.used_space * b.block_size)/1024/1024, 2) AS "USED_SPACE(MB)",
  ROUND((a.tablespace_size * b.block_size)/1024/1024, 2) AS "TABLESPACE_SIZE(MB)",
  ROUND(a.used_percent, 2) AS "USED_PERCENT"
  FROM DBA_TABLESPACE_USAGE_METRICS a JOIN
  DBA_TABLESPACES b
  ON a.tablespace_name = b.tablespace_name;
 

TABLESPACE_NAME  USED_SPACE(MB) TABLESPACE_SIZE(MB) USED_PERCENT
---------------- -------------- ------------------- ------------
PSHUGE02IDX            29989.06            76000           39.46
PSHUGE02               37116.06            92000           40.34
PSAPPIDX               54890.5             86163           63.71
PSHUGE01              222284              325260           68.34
...
..
.

Sometimes we see “USED_PERCENT” is not refreshed immediately after datafiles are added or dropped. in this case, alter tablespace offline then online helps, if only you can do it.

TABLESPACE_NAME  USED_SPACE(MB) TABLESPACE_SIZE(MB) USED_PERCENT 
---------------- -------------- ------------------- ------------
TESTUSER_TBL       23.06          123                 18.75

SQL> alter tablespace TESTUSER_TBL offline;
Tablespace altered.

TABLESPACE_NAME  USED_SPACE(MB) TABLESPACE_SIZE(MB) USED_PERCENT 
---------------- -------------- ------------------- ------------
TESTUSER_TBL        22                  22            100