How to Kill session in Oracle

The command 'alter system kill session' is not actually killing the target session (like kill -9 would do for OS processes). It just sets a bit in the target sessions state object, which marks that the target session should end. But its entirely up the target session to check this bit and act on it! Normally the target sessions are nice and check that bit often enough in their code, act on it and die. But sometimes when the target session happens to be busy looping in some tight loop (due a bug perhaps) or is hung, then it never gets to check that “please die” bit and never exits. This is why DBAs often need to kill the OS process or thread via OS tools to get rid of that session (and its locks, transactions) as when you kill the OS process, PMON will detect it (if not fast enough then it can be woken up via ORADEBUG WAKEUP call few times) and clean up after that session.

Steps to kill session in Oracle:
1. Use 'alter system kill session' to kill session.
2. If that doesn't work immediately then check whether the target session has acknowleged the kill and rolling back its transaction.

SQL>select used_urec from v$transaction where addr = (select taddr from v$session where sid= and serial#=);

3. If there is no rolling back happening an session just seems to be stuck, then it's time to kill that session's process from OS level.
4. If couple of minutes after killing the process from OS level that session is still not released (no rollback), then attaching to the OS process with oradebug and run 'ORADEBUG WAKEUP 2' couple of times and check is the session is gone. The "2" means Oracle PID of PMON process which is usually 2, but you should check it from your v$process view.

SQL>oradebug setospid ;
SQL>oradebug wakeup 2;

5. If step 4 does not work, try to kill the oracle process using oradebug.

SQL>oradebug setospid ;
SQL>oradebug event immediate crash;

Logging Attributes in Create Table Statement

Tables created with logging or nologging clause will stay in that way. If the logging clause is not used when creating table (other than LOBs), it defaults to the logging attribute of the tablespace in which it resides.

With respect to the nologging option, three benefits listed in the Administrator's Guide are:

• Space is saved in the redo log files
• The time it takes to create the table is decreased
• Performance improves for parallel creation of large tables

The logging clause lets you specify whether creation of a database object will be logged in the redo log file (LOGGING) or not (NOLOGGING). If you create a table with NOLOGGING, but cannot afford to lose the data (which implicitly means you need the table it is stored in), the first step after the data load is complete is to take a backup.

The NOLOGGING clause also specifies that subsequent direct loads using SQL*Loader and direct load INSERT operations are not logged. Subsequent DML statements (UPDATE, DELETE, and conventional path insert) are unaffected by the NOLOGGING attribute of the table and generate redo.