How to cancel running concurrent request

To cancel running concurrent request
———————————-
sqlplus>

UPDATE fnd_concurrent_requests
SET PHASE_CODE=’C’
,STATUS_CODE=’E’
WHERE request_id=’3477766′;

sqlplus>

UPDATE fnd_concurrent_requests
SET  PHASE_CODE=’C’
,STATUS_CODE=’E’
WHERE  CONCURRENT_PROGRAM_ID=’20393′;

(when large number of request are pending …kill all thiose with concurrent_program_id)

Hope this will help you.


Ur's
AmarAlam

DB Link Script

CREATE DATABASE LINK one_next
 CONNECT TO apps-- User Name 2 nd Db or Instance
 IDENTIFIED BY apps-- pwd  2 nd Db or Instance
 USING '
 (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 122.166.245.174)(PORT = 1620)) )
    (CONNECT_DATA =
      (SERVICE_NAME = prod)
    )  )';

after creating Db Link Access The 3rd party Ex: select * from oe_order_headers@one_next


Ur's
AmarAlam

Oracle Sql Script To Find The Installed Modules & Data Base Version

Version :

 SELECT * FROM V$VERSION;

Installed Modules :

select * from (select   substr(a.APPLICATION_NAME,1,60) "AN"
,  substr(i.PRODUCT_VERSION,1,4)  "Version"
,  i.PATCH_LEVEL    "Patch Level"
,  i.APPLICATION_ID   "Application ID"
,  i.LAST_UPDATE_DATE   "Last Update"
from   APPS.FND_PRODUCT_INSTALLATIONS  i
,  APPS.FND_APPLICATION_ALL_VIEW  a
where   i.APPLICATION_ID   = a.APPLICATION_ID
--  not all applications update the next field correctly
--  and i.PATCH_LEVEL   like '11i%'
--  these are the applications that concern me most
--  and i.APPLICATION_ID in   ('0','140','260','101','200','275','201','222','185')
order by a.APPLICATION_NAME)
--where AN like  ('%CPC%').


Ur's
AmarAlam

DBA Queries

-- Database Details
SELECT * FROM v$database

-- Instance Details
SELECT * FROM v$instance

-- License Details
SELECT * FROM v$license

-- Version Details
SELECT * FROM v$version

--Release Details
SELECT * FROM apps.fnd_product_groups

-- Patch Details
SELECT * FROM ad_applied_patches
SELECT * FROM ad_bugs


-- Concurrent Manager

SELECT concurrent_queue_name,
       user_concurrent_queue_name,
       description,
       enabled_flag
  FROM apps.fnd_concurrent_queues_vl fcq
 WHERE user_concurrent_queue_name LIKE 'AA%'



-- Partitioning Installed
SELECT DECODE (COUNT (*), 0, 'No', 'Yes') partitioning
FROM (SELECT 1
FROM dba_part_tables
WHERE owner NOT IN ('SYSMAN', 'SH', 'SYS', 'SYSTEM') AND ROWNUM = 1);

-- Spatial Installed
SELECT DECODE (COUNT (*), 0, 'No', 'Yes') spatial
FROM (SELECT 1
FROM all_sdo_geom_metadata
WHERE ROWNUM = 1);

-- RAC Installed
SELECT DECODE (COUNT (*), 0, 'No', 'Yes') rac
FROM (SELECT 1
FROM v$active_instances
WHERE ROWNUM = 1);

-- Unix Product Top Value
SELECT variable_name, value
FROM apps.fnd_env_context
WHERE variable_name = 'AP_TOP'
AND concurrent_process_id =
(SELECT MAX (concurrent_process_id) FROM apps.fnd_env_context);

-- Command to Kill Session for Releasing Lock
ALTER SYSTEM KILL SESSION '(sid, serial#)';


Ur's
AmarAlam

Want to forcefully kill any session

alter system kill session 'sid,serial#'

e.g.
altery system kill session '123,5325';

How To Kill Oracle Database Session

How To Kill Oracle Database Session:

First identify the offending sessions using active running processes query :

The SQL*Plus Approach

-------------------------------

Sessions can be killed from within oracle using the ALTER SYSTEM KILL SESSION syntax.

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';

In some situations the Oracle.exe is not able to kill the session immediately. 

In these cases the session will be "marked for kill". It will then be killed as soon as possible.

Issuing the ALTER SYSTEM KILL SESSION command is the only safe way to kill an Oracle session. 

If the marked session persists for some time you may consider killing the process at the 

Operating system level, as explained below. 

Killing OS processes is dangerous and can lead to instance failures, so do this at your own peril.

It is possible to force the kill by adding the IMMEDIATE keyword:

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

This should prevent you ever needing to use the orakill.exe in Windows, or the kill command in UNIX/Linux.

The NT Approach

----------------------

To kill the session via the NT operating system

The SID and SPID values of the relevant session can then be substituted into the following command issued from the command line:

C:> orakill ORACLE_SID spid

The session thread should be killed immediately and all resources released.

The UNIX Approach

---------------------

To kill the session via the UNIX operating system 

then substitute the relevant SPID into the following command:

% kill -9 spid

If in doubt check that the SPID matches the UNIX PROCESSID shown using:

% ps -ef | grep ora

The session thread should be killed immediately and all resources released

note:-sid is different from ORACLE_SID

A.Check if the Package or table are locked using the below query :

SELECT b.object_name,

a.session_id,

a.oracle_username,

a.os_user_name,

a.process,

a.locked_mode

FROM v$locked_object a,

all_objects b

WHERE a.object_id = b.object_id

B.Get the serial number for the session based on the session id got from above qyery.

SELECT SID,

serial#,

ownerid,

status,

server,

username,

osuser,

process,

machine

FROM v$session

WHERE SID = 'Session id from above query'

C. Command to kill the session ALTER SYSTEM KILL SESSION 'Sid from query, Serial# from Query 2'

Ur's
AmarAlam