Query to check patches applied correctly with the expected sequence.

1.1.Run this sql statement: select * from ad_adop_session_patches order by end_date desc; 1.2. Run this piece of sql code: set pagesize 200; set linesize 160; column adop_session_id format 999999999999; column bug_number format a15; column status format a15; column applied_file_system_base format a23; column patch_file_system_base format a23; column adpatch_options format a15; column node_name format a15; column end_date format a15; column clone_status format a15; select ADOP_SESSION_ID, BUG_NUMBER, STATUS, APPLIED_FILE_SYSTEM_BASE, PATCH_FILE_SYS TEM_BASE, ADPATCH_OPTIONS, NODE_NAME, END_DATE, CLONE_STATUS from ad_adop_session_patches order by end_date desc; Below are possible values of STATUS column: N - Not Applied In the current node but applied in other nodes R - Patch Application is going on. H - Patch failed in the middle. (Hard Failure) F - Patch failed in the middle but user tried to skip some failures. S - Patch Application succeeded after skipping the failed jobs. Y - Patch Application succeeded. C - Reserved for clone and config_clone. Indicates clone completed

SQL query to get the version of a file

select sub.filename, sub.version , sub.last_update_date, sub.app_short_name, sub.subdir from ( select adf.filename filename, afv.version version,afv.LAST_UPDATE_DATE,adf.app_short_name, adf.subdir, rank()over(partition by adf.filename order by afv.version_segment1 desc, afv.version_segment2 desc,afv.version_segment3 desc, afv.version_segment4 desc,afv.version_segment5 desc, afv.version_segment6 desc,afv.version_segment7 desc, afv.version_segment8 desc,afv.version_segment9 desc, afv.version_segment10 desc, afv.translation_level desc) as rank1 from ad_file_versions afv, ( select filename, app_short_name, subdir, file_id from ad_files where upper(filename) like upper('%&filename%') ) adf where adf.file_id = afv.file_id ) sub where rank1 = 1 order by 1;

Query to find Plan Hash Values for a SQLID in Oracle

Query to find Plan Hash Values for a SQLID in Oracle SELECT DISTINCT sql_id, plan_hash_value FROM dba_hist_sqlstat dhs, ( SELECT /*+ NO_MERGE */ MIN(snap_id) min_snap, MAX(snap_id) max_snap FROM dba_hist_snapshot ss WHERE ss.begin_interval_time BETWEEN (SYSDATE - &No_Days) AND SYSDATE ) s WHERE dhs.snap_id BETWEEN s.min_snap AND s.max_snap AND dhs.sql_id IN ( '&SQLID')

Long-running sessions in Oracle

 Long-running sessions in Oracle using below queries:

You can check long running sessions using v$session_longops script which will show you, % completed, remaining time, sofar completed and much more detailed information. You can also use below queries to kill long running sessions in Oracle.

 

Query1:

SELECT SID, SERIAL#,OPNAME, CONTEXT, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM V$SESSION_LONGOPS WHERE OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK;

Query2:

set lines 300

col TARGET for a40

col SQL_ID for a20

select SID,TARGET||OPNAME TARGET, TOTALWORK, SOFAR,TIME_REMAINING/60 Mins_Remaining,ELAPSED_SECONDS,SQL_ID from v$session_longops where TIME_REMAINING>0 order by TIME_REMAINING;

 

 

TO find out sql_id for the above sid:

SQL> select sql_id from v$session where sid='&SID';



To find sql text for the above sql_id:

SQL> select sql_fulltext from V$sql where sql_id='bgf07y9xn8grx';



To find wait event of the query for which it is waiting for:

SQL>select sql_id, state, last_call_et, event, program, osuser from v$session where sql_id='&sql_id';

 

 

How to Kill Long Running Concurrent Job in R12

 

How to Kill Long Running Concurrent Job in R12

Step 1) Find SID and Serial Number for Concurrent Job

Step 2) Kill the long running session

Step 3) Update fnd_concurrent_requests table to mark the request in error and completed

STEP1:

SELECT a.request_id, d.sid, d.serial# ,d.osuser,

d.process , c.SPID ,d.inst_id

FROM apps.fnd_concurrent_requests a,

apps.fnd_concurrent_processes b,

gv$process c,

gv$session d

WHERE a.controlling_manager = b.concurrent_process_id

AND c.pid = b.oracle_process_id

AND b.session_id=d.audsid

AND a.request_id = 344625347--------------req id

AND a.phase_code = 'R';

STEP2:

select ORACLE_PROCESS_ID from apps.fnd_concurrent_requests where REQUEST_ID=&REQUEST_ID;

select ORACLE_PROCESS_ID from apps.fnd_concurrent_requests where REQUEST_ID=&REQUEST_ID;

alter system kill session ‘sid,serial#’ immediate:

STEP3:

 UPDATE fnd_concurrent_requests

SET phase_code = 'C', status_code = 'D'

WHERE Request_id=&request_id;

If this does not solve your problem then try restarting Standard Concurrent Manager and Output Post Processor Concurrent Manager.

Enable Diagnostics in Oracle Apps

 

Enable Diagnostics in Oracle Apps

The below steps can be followed for enabling diagnostics at user level.

1. Navigate to System Administrator responsibility> Profile> System>

 

 

2. Enter profile name: Utilities:Diagnostics .
    Enter Application User for whom you want to enable Diagnostics.

 

 

Enter value Yes at User level and Save the Changes . You can set yes at site level for all application users.

 

3. Navigate to System Administrator responsibility> Profile> System>

Enter profile name: Hide Diagnostics menu entry.

Enter Application User for whom you do not want to hide Diagnostics menu entry .

Enter value No at User level and Save the Changes .

 

 

 

 

 

4. Logout from Oracle Application and login again.Diagnostic would be enabled.

 

 

 

Current Running SQLs

 

OCT

31

How to find SQL,SQL_ID history on Oracle

How to find SQL,SQL_ID history on Oracle

Session related Queries

Last/Latest Running SQL
-----------------------
set pages 50000 lines 32767
col "Last SQL" for 100
SELECT t.inst_id,s.username, s.sid, s.serial#,t.sql_id,t.sql_text "Last SQL"
FROM gv$session s, gv$sqlarea t
WHERE s.sql_address =t.address AND
s.sql_hash_value =t.hash_value
/

Current Running SQLs
--------------------
set pages 50000 lines 32767
col HOST_NAME for a20
col EVENT for a40
col MACHINE for a30
col SQL_TEXT for a50
col USERNAME for a15

select sid,serial#,a.sql_id,a.SQL_TEXT,S.USERNAME,i.host_name,machine,S.event,S.seconds_in_wait sec_wait,
to_char(logon_time,'DD-MON-RR HH24:MI') login
from gv$session S,gV$SQLAREA A,gv$instance i
where S.username is not null
--  and S.status='ACTIVE'
AND S.sql_address=A.address
and s.inst_id=a.inst_id and i.inst_id = a.inst_id
and sql_text not like 'select S.USERNAME,S.seconds_in_wait%'
/