How to terminate concurrent request from server (backend)

 

We normally terminate the concurrent requests using System Administrator responsibility. But sometimes we may need to terminate the request from server.

Use below update  command to terminate the concurrent request from backend.

update apps.fnd_concurrent_requests set phase_code='C' , status_code='X' where request_id='&REQUEST_ID';

commit;

Thanks for going through the post...........

How to find the OPP log file using concurrent request ID

 

Use below query to find the OPP log file using request ID.

SELECT fcpp.concurrent_request_id req_id, fcp.node_name, fcp.logfile_name 
FROM fnd_conc_pp_actions fcpp, fnd_concurrent_processes fcp 
WHERE fcpp.processor_id = fcp.concurrent_process_id 
AND fcpp.action_type = 6 
AND fcpp.concurrent_request_id = &&request_id;

Thanks for going through the post...................

How to find the concurrent program assigned to which concurrent manager using request ID

 

Use below query to find concurrent program assigned to which concurrent manager using concurrent request ID.

select b.USER_CONCURRENT_QUEUE_NAME from apps.fnd_concurrent_processes a,
fnd_concurrent_queues_vl b, fnd_concurrent_requests c
where a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID
and a.CONCURRENT_PROCESS_ID = c.controlling_manager
and c.request_id = <Request ID>;

Thanks for going through the post...........

How to find a user is existed in OID

 

Sometimes new users won't flow to EBS from OID due to some reason. As part of troubleshooting we need to find user is available in OID or not to fix the issue.

Log in to OID application server and issue below command.

$ ldapsearch -p 3060 -h <OID SERVER IP> -D "cn=orcladmin" -w <orcladmin password> -b "" -s sub "uid=User Name"

Thanks for going through the post............

Query to find execution plan using sql_id in oracle database

 

Use below query to find the execution plan of currently running sql using sql_id.

set lines 500

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('<sql_id',0));

Thanks for going through the post...............

How to find SID using SPID in oracle database

 

Use below query to find SID using SPID.

col sid format 999999
col username format a20
col osuser format a15
select b.spid,a.sid, a.serial#,a.username, a.osuser
from v$session a, v$process b
where a.paddr= b.addr
and b.spid='&spid'
order by b.spid;

Thanks for going through the post.............

How to find Temp tablespace usage in oracle database

 

Use below query to find total and free temp tablespace usage in oracle database.

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
 
SELECT 
   A.tablespace_name tablespace, 
   D.mb_total,
   SUM (A.used_blocks * D.block_size) /1024/1024/1024 gb_used,
   D.mb_total - SUM (A.used_blocks * D.block_size) /1024/1024/1024 gb_free
FROM 
   v$sort_segment A,
(
SELECT 
   B.name, 
   C.block_size, 
   SUM (C.bytes) /1024/1024/1024 mb_total
FROM 
   v$tablespace B, 
   v$tempfile C
WHERE 
   B.ts#= C.ts#
GROUP BY 
   B.name, 
   C.block_size) D
WHERE 
   A.tablespace_name = D.name
GROUP by 
   A.tablespace_name, 
   D.mb_total
/

Thanks for going through this post................

How to find concurrent request ID using SID in 11i/R12

 

Use below query to find the concurrent request ID using SID in oracle applications 11i/R12

SELECT s.inst_id,a.request_id,s.sid,s.serial#,c.spid
FROM apps.fnd_concurrent_requests a, gv$process c, gv$session s
WHERE s.sid in ('&SID')
AND s.paddr = c.addr
AND a.oracle_process_id = c.spid
AND a.phase_code = UPPER ('R');

Thanks for going through the post..................

How to find which session is using more UNDO in oracle database

Use below query to find the session which is taking more UNDO.

select s.sid, t.name, s.value
from v$sesstat s, v$statname t
where s.statistic#=t.statistic#
and t.name='undo change vector size'
order by s.value desc;

Thanks for going through the post...........

Query to find the ADOP session info

 

Use below query to find ADOP session info.

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_SYSTEM_BASE, ADPATCH_OPTIONS, NODE_NAME, END_DATE, CLONE_STATUS from ad_adop_session_patches order by end_date desc;

Thanks for going through the post............

How to find the existing network ACLs and proxies in oracle database

 

Use below query to find the existing network ACLs in oracle database.

Network ACLs:

col owner for a15
col host for a30
col acl for a55
set lines 500

select * from dba_network_acls;

Database Proxies

col proxy for a15

col client for a15

col authentication for a15

col role for a15

col proxy_authority for a15

set lines 500

select * from dba_proxies where proxy like 'XYZ';

Thanks for going through the post........

How to find total archives generated per a day

 

Use below query to find the total archive logs generated in a single day.

SELECT A.*,
Round(A.Count#*B.AVG#/1024/1024) Daily_Avg_Mb
 FROM
 (
 SELECT
 To_Char(First_Time,'YYYY-MM-DD') DAY,
 Count(1) Count#,
 Min(RECID) Min#,
 Max(RECID) Max#
 FROM
 v$log_history
 GROUP BY
 To_Char(First_Time,'YYYY-MM-DD')
 ORDER
 BY 1 DESC) A,
 (
 SELECT
 Avg(BYTES) AVG#,
 Count(1) Count#,
 Max(BYTES) Max_Bytes,
 Min(BYTES) Min_Bytes
 FROM
 v$log
 ) B;

Thanks for going through the post.....

How to find invalid objects with timestamp in oracle database.

 

Use below query to find the invalid objects with time stamp when they got invalidated.

select owner,object_name,timestamp, to_char(created,'hh24:mi:ss'), to_char(last_ddl_time,'hh24:mi:ss'),status from dba_objects 
where status = 'INVALID' 
and owner = 'APPS' 
order by timestamp;

Thanks for going through the post...............

How to find the workflow logfile in 11i/R12

 

Use below query to find the workflow logfile with complete path.

SELECT concurrent_queue_name manager, fcp.last_update_date, fcp.concurrent_process_id pid, meaning, fcp.logfile_name 
FROM fnd_concurrent_queues fcq, fnd_concurrent_processes fcp, fnd_lookups flkup 
WHERE concurrent_queue_name = 'WFALSNRSVC' 
AND fcq.concurrent_queue_id = fcp.concurrent_queue_id 
AND fcq.application_id = fcp.queue_application_id 
AND flkup.lookup_code=fcp.process_status_code 
AND lookup_type ='CP_PROCESS_STATUS_CODE' 
AND meaning='Active';

Thanks for going through the post........ 

How to find SPID using SID in oracle database

 

Use below query to find the SPID associated with a particular SID.

col sid format 999999
col username format a20
col osuser format a15

select a.sid, a.serial#,a.username, a.osuser, b.spid
from v$session a, v$process b
where a.paddr= b.addr
and a.sid='&sid'
order by a.sid;

Note: Pass the SID for which we want to find the SPID.

Thanks for going through the post..............

How to gather stats for a single table in oracle applications 11i/R12

Use fnd_stats to gather stats of a table in oracle applications 11i/R12. 

connect as apps user and run below statement.

begin 

FND_STATS.GATHER_TABLE_STATS(ownname=>'&owner',
                                                                     tabname =>'&table_name',
                                                                     percent=> 80,
                                                                     degree=> 80,
                                                                     cascade=>TRUE,
                                                                     granularity=>'ALL'); 
end;  
/

How to find application user end date in oracle applications 11i and R12

 

We can not delete the user in oracle applications 11i/R12. Instead of we can end date the user. Use below query to find the end date of a user.

select user_name,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATED_BY,
START_DATE,END_DATE from fnd_user where user_name='TVIJK';

How to compile a form in 11i and R12

 

Copy form binary(.fmb) to $AU_TOP/forms/US directory and compile the forms using below commands in 11i/R12.

Note: Take backup of existing form binary file(.fmb) before compilation. In some scenarios we may need to restore old file due to some issues and we can utilize backed up file to regenerate the old form.

Form compilation in 11i

f60gen module=XXTEST_DATA.fmb userid=apps/<apps_pwd> output_file=$FND_TOP/forms/US/XXTEST_DATA.fmx module_type=form batch=no compile_all=special

Form compilation in R12

frmcmp_batch userid=apps/<apps_pwd> module=XXTEST_DATA.fmb output_file=$AU_TOP/forms/US/XXTEST_DATA.fmx module_type=form batch=no compile_all=special

Thanks for going through the post..........

Multiple queries to get the SQLTEXT in the oracle database

Use below queries to find the SQL Texts using sql_address and sql_hashvalue

Find sqltext through sqladdress

select sql_address from gv$session where sid=1999;

select sql_text from v$sqltext where ADDRESS='C00000027FF00AF0' order by PIECE;

To find sqltext for different sql hashvalues:

select hash_value,sql_text from v$sql where hash_value in (1937378691,1564286875,248741712,
2235840973,2787402785);

Multiple queries to get session details

Session details associated with Oracle SID 

set head off
set verify off
set echo off
set pages 1500
set linesize 100
set lines 120
prompt
prompt Details of SID / SPID / Client PID
prompt ==================================
select /*+ CHOOSE*/
'Session  Id.............................................: '||s.sid,
'Serial Num..............................................: '||s.serial#,
'User Name ..............................................: '||s.username,
'Session Status .........................................: '||s.status,
'Client Process Id on Client Machine ....................: '||'*'||s.process||'*'  Client,
'Server Process ID ......................................: '||p.spid Server,
'Sql_Address ............................................: '||s.sql_address,
'Sql_hash_value .........................................: '||s.sql_hash_value,
'Schema Name ..... ......................................: '||s.SCHEMANAME,
'Program  ...............................................: '||s.program,
'Module .................................................: '|| s.module,
'Action .................................................: '||s.action,
'Terminal ...............................................: '||s.terminal,
'Client Machine .........................................: '||s.machine,
'LAST_CALL_ET ...........................................: '||s.last_call_et,
'S.LAST_CALL_ET/3600 ....................................: '||s.last_call_et/3600
from v$session s, v$process p
where p.addr=s.paddr and
s.sid=nvl('&sid',s.sid) 
/

Active Sessions running from more than 1 hour

set pages 50000 lines 32767
col USERNAME for a10
col MACHINE for a15
col PROGRAM for a40


SELECT USERNAME,machine,inst_id,sid,serial#,PROGRAM,
to_char(logon_time,'dd-mm-yy hh:mi:ss AM')"Logon Time",
ROUND((SYSDATE-LOGON_TIME)*(24*60),1) as MINUTES_LOGGED_ON,
ROUND(LAST_CALL_ET/60,1) as Minutes_FOR_CURRENT_SQL
From gv$session
WHERE STATUS='ACTIVE'
AND USERNAME IS NOT NULL and ROUND((SYSDATE-LOGON_TIME)*(24*60),1) > 60
ORDER BY MINUTES_LOGGED_ON DESC;

SQLs Running from longtime

alter session set nls_date_format = 'dd/mm/yyyy hh24:mi';

set pages 50000 lines 32767

col target format a25

col opname format a40

select sid

      ,opname

      ,target

      ,round(sofar/totalwork*100,2)   as percent_done

      ,start_time

      ,last_update_time

      ,time_remaining

from 

       gv$session_longops

/

select inst_id,SID,SERIAL#,OPNAME,SOFAR,TOTALWORK,

START_TIME,LAST_UPDATE_TIME, username from gv$session_longops;

Session details with SPID

select sid, serial#, USERNAME, STATUS, OSUSER, PROCESS,

MACHINE, MODULE, ACTION, to_char(LOGON_TIME,'yyyy-mm-dd hh24:mi:ss')

from gv$session where paddr in (select addr from gv$process where spid = '&spid')

/ 

To find Undo Generated For a given session

select  username,

t.used_ublk ,t.used_urec

from    gv$transaction t,gv$session s

where   t.addr=s.taddr and

s.sid='&sid';

How to find currently running SQLs in the database

Use below query to find the currently running SQLs in the database.

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%'
/

Thanks for going through the post................

How to get DDLs of multiple objects in oracle database

 

This document describes how to get DDLs of multiple objects lik package, package body, table, index, procedure etc....

Syntax:

SQL>set lines 200

SQL>set pages 99999
SQL>set long 1000000

SQL> SELECT DBMS_METADATA.GET_DDL('<object_type','<object_name>','<object owner>') FROM DUAL;

Use below query to get the DDL of Package specification from HR schema in oracle database.

SQL> SELECT DBMS_METADATA.GET_DDL('PACKAGE','DEPT.PKG','HR') FROM DUAL;

Use below query to get the DDL of Package body from HR schema in oracle database.

SQL> SELECT DBMS_METADATA.GET_DDL('PACKAGE_BODY','DEPT.PKG','HR') FROM DUAL;

Get the Index structure from HR schema using below query.

SQL> SELECT DBMS_METADATA.GET_DDL('INDEX','DEPT_IND_P1','HR') FROM DUAL;

Use below command to get the DDL of a tablespace.

SQL>  SELECT DBMS_METADATA.GET_DDL('TABLESPACE','EBS_APP_DATA') FROM dual;

Use below command to get the DDL of a table from HR schema.

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','DEPT','HR') FROM DUAL;

Use below query to get the system grants of a schema.

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','<schema name>') from dual;

Role grants of a schema:

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','<schema name>') from dual;

Object grants of a schema:

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','<schema name>') from dual;

Thanks for going through the post.................

Query to find APEX and APEX logger version

This document describes how to find APEX logger version.

Use below query to find APEX logger version.

select pref_value from logger_prefs where pref_name = 'LOGGER_VERSION';

APEX Version:

We can find APEX version in below 2 methods from database.

Method 1:

col version_no for a30

col API_COMPATIBILITY for a20

col PATCH_APPLIED for a20

SQL> select * from apex_release;

VERSION_NO                  API_COMPATIBILITY    PATCH_APPLIED

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

4.2.5.00.08                   2012.01.01

SQL> 

 

Method 2:

col COMP_ID for a20

col COMP_NAME for a40

col VERSION for a25

SQL> select COMP_ID,COMP_NAME,VERSION from dba_registry;

COMP_ID      COMP_NAME       VERSION

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

DV      Oracle Database Vault       12.1.0.2.0

APEX      Oracle Application Express       4.2.5.00.08

OLS      Oracle Label Security       12.1.0.2.0

SDO      Spatial       12.1.0.2.0

ORDIM      Oracle Multimedia       12.1.0.2.0

CONTEXT      Oracle Text       12.1.0.2.0

OWM      Oracle Workspace Manager       12.1.0.2.0

XDB      Oracle XML Database       12.1.0.2.0

CATALOG      Oracle Database Catalog Views       12.1.0.2.0

CATPROC      Oracle Database Packages and Types       12.1.0.2.0

JAVAVM      JServer JAVA Virtual Machine       12.1.0.2.0

COMP_ID      COMP_NAME       VERSION

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

XML      Oracle XDK       12.1.0.2.0

CATJAVA      Oracle Database Java Packages       12.1.0.2.0

APS      OLAP Analytic Workspace       12.1.0.2.0

XOQ      Oracle OLAP API       12.1.0.2.0

RAC      Oracle Real Application Clusters       12.1.0.2.0

Thanks for going through the post...........

How to convert SCN to TIMESTAMP or TIMESTAMP to SCN in oracle database

 

This document describes how to convert SCN to TIMESTAMP and TIMESTAMP to SCN in oracle database.

Sometimes we get the requirement convert SCN to Timestamp and Timestamp to SCN to recover the database until SCN or until Timestamp.

We use below two seeded functions to convert SCN to Timestamp.

SCN_TO_TIMESTAMP

TIMESTAMP_TO_SCN

Convert SCN to Timestamp:

SQL> select current_scn from v$database;

CURRENT_SCN
----------------------
    1772955

SQL> select scn_to_timestamp(1772955) from dual;

SCN_TO_TIMESTAMP(1772955)
---------------------------------------------------------------------------
20-FEB-21 12.13.06.000000000 AM

Convert TIMESTAMP to SCN:

SQL> select timestamp_to_scn(to_timestamp('20-02-2021 00:15:00','dd-mm-yyyy hh24:mi:ss')) SCN from dual;

       SCN

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

   1777489

 

Thanks for going through the document...........

Query to find blocking sessions in oracle database and how to kill blocking sessions.

 

This document describes how to find the blocking sessions in the database and how to kill them. 

We can find blocking sessions by using v$session (standalone database or instance vise in RAC database) view or gv$session (RAC database) view.

Query to find blocking sessions:

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

select 

   blocking_session, 
   sid, 
   serial#, 
   wait_class,
   seconds_in_wait
from 
   gv$session
where 
   blocking_session is not NULL
order by blocking_session;

 

How to kill blocking session:

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

Find the blocking session by using above query and kill it using below query.

RAC database:

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

alter system kill session('SID,SERIAL#,@inst_id') immediate;

Example: alter system kill session ('65,32555,@1') immediate;

Non-RAC database:

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

alter system kill session('SID,SERIAL#') immediate;

Example: alter system kill session('SID,SERIAL#,@1) immediate;

Thanks for going through the document.......................

Query to find table size in oracle

 

Sometimes we may need to find the table size in oracle database. Below query provides the size of  a table in the database.

How to find table size:

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

select sum(bytes)/(1021*1024*2014) as size_in_GB from dba_segments where SEGMENT_NAME like 'XXEMPLOYEES' and segment_type='TABLE';

Thanks for going through the post.............

Useful queries to find the details of ASM

 

This document describes how to find the asm diskgroup details and it's size.

Find the asm disk details.

SQL> select GROUP_NUMBER DG#, name, ALLOCATION_UNIT_SIZE AU_SZ, STATE,TYPE, TOTAL_MB, FREE_MB, OFFLINE_DISKS from v$asm_diskgroup;

Query to find the ASM disk size.

set linesize 400 

pagesize 400
col path for a30

SQL> select HEADER_STATUS, group_number,disk_number,name, path,os_mb/1024 OS_GB , total_mb/1024 TOTGB from v$asm_disk order by group_number,disk_number;

Query to find the ASM operations.

select * from v$asm_operation;

Thanks for going through this post..............

How to terminate running concurrent request from the server(Backend).

 

This document describes how to terminate the running concurrent requests from backend.

Sometimes we may need to cancel the running concurrent requests from server instead of concurrent requests form.

Use below update statement to terminate the running request.

SQL> update apps.fnd_concurrent_requests set phase_code='C' , status_code='X' where request_id='&REQUEST_ID';

Note: Pass the request ID which we need to terminate.

commit;

Thanks for going through the post........

Query to find database monthly growth

 

This document describes how to find the monthly database growth rate in last one year.

Use the below query to find database monthly growth.

set pagesize 50000

tti "Database growth per month for last year"
select to_char(creation_time, 'RRRR Month') "Month",
       sum(bytes)/1024/1024/1024 "Growth in GB"
  from sys.v_$datafile
where creation_time > SYSDATE-365
group by to_char(creation_time, 'RRRR Month')

/

Thanks for going through this post..................

Query to find tablespace total and free space.

This document describes how to find the total allocated size and available free size of tablespaces in the database.

Find the tablespace size using below query.

SELECT /* + RULE */
df.tablespace_name AS "Tablespace"
,df.bytes / (1024 * 1024 ) AS "Total Size in (MB)"
,Trunc(fs.bytes / (1024 * 1024 )) AS "Total Free Size in (MB)"
FROM (
SELECT tablespace_name
,Sum(bytes) AS bytes
FROM dba_free_space
GROUP BY tablespace_name
) fs
,(
SELECT tablespace_name
,SUM(bytes) AS bytes
FROM dba_data_files
GROUP BY tablespace_name
) df

WHERE fs.tablespace_name = df.tablespace_name

ORDER BY 3 DESC;

Thanks for going through the post..........

Query to flush the running sql in oracle database.

 This describes how to flush a running sql from shared pool using sql_id in oracle database.

Some times we have to flush the running sql due to bad sql plan. New sql plan can be picked if we can flush the running sql. Make sure the running sql has bad sql plan before flushing it.

Find the sql_id using below query.

select sid,serial#,sql_id from v$session where sid=27;

Flush the running sql from shared pool using below steps.

Step 1: 

select address, hash_value from v$sqlarea where sql_id like 'fr7q58byvjtsu';

Step 2:

exec dbms_shared_pool.purge('00000000B2E45B48, 513826068','C');

Thanks for going through the post.......

Query to find database links(db links) and it's DDLs in a database.

This document describes the details of database links in the database.

Some times we need to find the existing database links in the database and it's DDLs to recreate the database links. Mostly this kind of scenario we get as post refresh (clone) of database.

Please click Here to know how to create database links and what is the difference between private and public database links.

We can find existing database links using below query.

col owner for a15    
col db_link for a45
col username for a16
col host for a30

set lines 500

select * from dba_db_links;

We can find db link DDLs using below query.

set long 900000

SELECT DBMS_METADATA.GET_DDL('DB_LINK',a.db_link,a.owner) FROM dba_db_links a;

Thanks for going through this post......