Removing Oracle Sessions

There are times when you need to drop the connection for a user or process in the database, we will review the basic forms to perform these tasks;

 

To drop a session in Oracle use the traditional way

— Sintax —

alter system kill session (sid, serial #) [immediate]

— Example —

ALTER SYSTEM KILL SESSION ‘5340, 43713 ‘;

 

A special case is KILL command for RAC enviroments, where you add a third number with @ prefix to specific the instance number inside the cluster, you can see this sid in the inst_id from gv$session view

— Syntax —

alter system kill session ‘sid , serial# ,@instance_id’ [immediate]

— Kill session 5427 in node 1 —

alter system kill session ‘5427,14599,@1’

— Kill session 3588 in node 2 —

alter system kill session ‘3588,14088@2’

 

Another way is to use the kill connections disconnect session, this system is primarily designed for MTS connections, because if  the session we drop to kill dispatcher, eliminating all connections through this dispatcher, which can be a high number of the order of tens or hundreds

— Syntax —
alter system disconnect session ‘sid , serial#’ [POST_TRANSACTION] [IMMEDIATE]

— Example —
ALTER SYSTEM DISCONNECT SESSION ‘5340, 43713 ‘ ;

 

  • The POST_TRANSACTION setting (only for DISCONNECT SESSION) allows ongoing transactions to complete before the session is disconnected. If the session has no ongoing transactions,
  • The IMMEDIATE setting (for both, DISCONNECT SESSION and KILL SESSION) disconnects the session and recovers the entire session state immediately, without waiting for ongoing transactions to complete

if you also specify POST_TRANSACTION and the session has ongoing transactions, then the IMMEDIATE keyword is ignored.

 

Of course, kill sessions from within the database many times, sessions want to live forever and not want to die, others die but starting doing rollback for a while… Another way is to remove connections from the OS. usually in Unix with KILL command, or OpenVMS with the STOP command. Ok, this is a much more radical way but in medium or large databases is the most efficiently.

# Example

kill -9 7954

 

 
Microsoft Windows is a special case where the oracle has ORAKILL (execute from Windows MsDos) command

REM Syntax —

orakill sid thread

REM Example —

orakill ORCL 1234

 

HTH – Antonio NAVARRO

 

Always Compared To Oracle

Last week I was on a course of MySQL tuning, I will always go to courses of  databases with many going  expectations and hoping to discover many new things. The truth is that if there’s news about the new version (MySQL 5.7) and new features of the product but as in a course that I made on  May, it returned to repeat the fact that during entire course compared to Oracle.  I have seen this since the 90s, but the last years thoughts about being “top trending”.

From the point of view Oracle as market positioning is a real success be the constant point of reference  competition. On the side of the teachers / training centers to some comparative that’s fine, but you can limit the knowledge of  product and that seems wrong. For the part of the other  manufacturers (Microsoft, SAP Sybase, IBM …) are observed tend to copy up nomenclatur., There are already several RDBMS (more traditional)  in which speaks of  “tablespace”. Must be a little sad to have to adapt their products to be a kind of clone. Copy Vs  innovate.

 

HTH – Antonio NAVARRO

 

Decimal Point Problem

Today I’ve been reported a problem from an user. He is downloading a table from an Oracle database to ACCESS database, the problem is that He have is that the numeric fields are of real type but in access it convert to integer, without the decimal point, for example, for the 10.24 number it appears like 1024. e In Oracle the data correct.

Do some testing, I created two DSN one wiht Oracle ODBC “Oracle ODBC Driver Connect”, in which the problem occurs.

CONNECTION_ORACLE

 

Using the Microsoft ODBC “Microsoft ODBC for Oracle Connect” the problem does not occur.

connection_microsoft

 
HTH – Antonio NAVARRO

 

Connect to hanged database

There are situations where the database has a high level of locking or other issues that prevents us sign in. A simple way that we are going to allow access is using the parameter -prelim from sqlplus. In accordance with this connection we will be able to basically to perform two actions
hanganalyze or systemstate.

Below is an example of connection with the -prelim option. If you run any type of statement it gives us a error.

 

foto_prelim

 
HTH – Antonio NAVARRO