Kill Oracle 11i or R12 FORM Java/JInitiator Session

Here is a common problem, Oracle EBusiness Suite users have ... You opened a Forms window. You searched for a specific data or LOV Results. The page is trying to open the new results, but hanging for a long time. What is the way out?

Solution: Oracle forms are displayed by JInitiator - an Oracle developed custom Java Application. To start a new session afresh, we need to kill Java Executable program and the browser program (Eg: internet Explorer).

Steps:
1. Open Windows Command prompt (Start -> Run -> <cmd> -> <ENTER>)

2. Run the following command to kill all java sessions: (This will kill JInitiator windows)
taskkill /f /im java.exe

3. Run the following command to kill all browser sessions: (This will kill Internet Explorer windows. If you use any browser other than IE, please change the command accordingly.)
taskkill /f /im iexplore.exe

4. Close Command Prompt

This solution will work for all versions of Oracle Applications (R12 and 11i). But this will work only when you access Apps using Microsoft Windows :)

Note: This process will terminate ALL Browser sessions in the machine. Make sure that all the possible data saved before running the command. This will also help to get rid of all Oracle Sessions and Cookie effects.
 

Steps - Kill Oracle or SQL Session - sqlplus

You are connected to TOAD. You ran a complex query and the window is hanging for a long time. Even the CANCEL button does not respond appropriately. What to do?

A very common problem, Oracle Developers have. Some quick solutions are <CNTL> C and <CNTL> D buttons. But many times, they also fail.

Here is a more complete and perfect way to kill the session.


Step 1: Identify the session - SID and Serial

Use appropriate WHERE clauses to identify the hanging session. You may use client (Toad/SQL - MODULE), OSUSER (Operating System User, Eg: CORP/AbThomas), MACHINE (Eg: server_dns_name) or TIMESTAMPS.


-- get non-unix sql sessions, kill one session
SELECT S.OSUSER, SUBSTR(S.SID || ',' || S.SERIAL#, 0, 20) AS KILL_STRING, MODULE, MACHINE, LOGON_TIME
FROM   V$SESSION S
where OSUSER = 'athomas'
-- AND MODULE LIKE 'T%' -- 'SQ%'
-- AND MACHINE LIKE '%NRBVUEBSAS02%'
-- AND MODULE LIKE 'JDBC Thin Client%'

In the results from above query, the module SQL is SQL Plus client, TOAD is Toad Client and anything related to Java or JVM will be the connections created from web server. (The Application Server connects to Database server through JDBC conneciton).

Step 2: Open another window, close the session:
Identify the Session Id and Serial (Second column in the above query) and substitute in the below line ...

ALTER SYSTEM KILL SESSION '876,24615'

The connection will be lost  with a message. Still you have the SQLs in the screen. In this way, all your query will be preserved, even though the connection is lost.


Simple steps. But very useful many times ... NJoy :)
 

Oracle Data Base (DB) Link Introduction

Business Need:

Database Links are used to connect from one database to a remote database without exposing/repeating connection details to the end user.

Oracle DB 12g is used for these SQLs. But the SQLs will be applicable most of the recent versions.

DB Link Creation:

Here is the command to create DB Link:

CREATE DATABASE LINK DBLINK_NAME

CONNECT TO REMOTE_SCHEMA IDENTIFIED BY REMOTE_PWD

USING 'REMOTE_TNSSTRING';

To use the DB Link, the REMOTE_TNSSTRING needs to be defined in the DB Server’s TNSNAMES.ORA file, you are connecting to. Otherwise this will give TNS Error.

 

DB Link Creation without TNS Definition:

Here is command to create DB Link to a remote database whose TNS String is not defined in the server:

CREATE DATABASE LINK DBLINK_NAME

    CONNECT TO REMOTE_SCHEMA IDENTIFIED BY REMOTE_PWD

    USING '(DESCRIPTION=

                (ADDRESS=(PROTOCOL=TCP)(HOST=<mymachine.mydomain.com>)(PORT=<1521>))

                (CONNECT_DATA=(SERVICE_NAME=<service or sid name>))

            )';

<mymachine.mydomain.com> à Domain Name or IP Address of Remote Database

<1521> à Port Number of Remote Database

<service or sid name> à Service Name of Remote Database

 

DB Link Usage:

Tables within DB Link can be accessed using the format <table name> @ <DB Link name>, just like any local table (Eg: USERS@DEV).

If frequently used, create a local synonym:

CREATE SYNONYM RTUSERS FOR USERS@DBLINK_NAME;

So the below SQLs work in the same way:

SELECT * FROM USERS@DBLINK_NAME;

SELECT * FROM RTUSERS;

  

Other Notes/Considerations:

Few points to add before using DB Links: 

1. Direct DB Links to Production might cause data security policy violation. Be careful over sensitive data exposure.
2. Query Optimization is poor across DB Links. A few steps might help improve performance over DB Links:
3. If a single table is accessed frequently, use a local temp table and fill the required data in the local table and use. DB Link to be used only in the first selection (to store data in temp table)
4. Use query-in-query format to choose remote data, if possible

Eg: Only 1 order type will be present in remote table. Use “(SELECT COL1, COL2 FROM ORDERS WHERE ORDER_TYPE = 21) X” instead of Order Type in the main query.

  

Keywords:

Oracle, DB Link, Database Link, Remote Database, TNSNAMES, Query, SQL, SQLPLUS