Category: sql queries

How to Stop Concurrent Requests from Executing on New Cloned Environment

 If you do not want concurrent requests scheduled in Source to run on a newly cloned environment, Follow the below steps

1. Run “perl adcfgclone.pl appsTier” as normal.
Before starting the application services, run the below update commands


-Take Backup of fnd_concurrent_requests

create table  fnd_concurrent_requests_bkp as select * from fnd_concurrent_requests;

-Terminate ‘Running’ Requests

UPDATE fnd_concurrent_requests
SET phase_code = ‘C’, status_code = ‘X’
WHERE status_code =’R’
OR phase_code = ‘R’
/

-Set Pending jobs to ‘On Hold’

UPDATE fnd_concurrent_requests
SET hold_flag = ‘Y’
WHERE phase_code = ‘P’
AND status_code in (‘Q’,’I’)
/

Check free space in Temp tablespace

 To check the free space in a temporary tablespace in Oracle, you can query the DBA_FREE_SPACE view or use the V$TEMP_FREE_SPACE dynamic view. These views provide information about the free space available in the temporary tablespace.

Here is a commonly used query to check the free space in a temporary tablespace:

Query 1: Using DBA_TEMP_FREE_SPACE

This view provides information about the free space in the temporary tablespaces.


SELECT
    tablespace_name,
    SUM(bytes)/1024/1024 AS free_space_MB
FROM
    dba_temp_free_space
GROUP BY
    tablespace_name;
  • This query will return the available free space in each temporary tablespace in MB.
  • The bytes are divided by 1024*1024 to convert from bytes to megabytes.

Query 2: Using V$TEMP_SPACE_HEADER

You can also use V$TEMP_SPACE_HEADER, which provides information about the temporary tablespace files and their usage.


SELECT
    tablespace_name,
    file_id,
    SUM(bytes)/1024/1024 AS free_space_MB
FROM
    v$temp_space_header
GROUP BY
    tablespace_name, file_id;

This query will give you a detailed breakdown of free space available in each temporary file associated with the temporary tablespaces.

Query 3: Checking Free Space Using DBA_DATA_FILES

This query checks the free space in the temporary tablespace by querying the DBA_TEMP_FILES and DBA_DATA_FILES views:


SELECT
    t.tablespace_name,
    f.file_name,
    f.bytes / 1024 / 1024 AS total_size_MB,
    (f.bytes - NVL(s.bytes, 0)) / 1024 / 1024 AS free_space_MB
FROM
    dba_temp_files f
LEFT JOIN
    (SELECT file_id, SUM(bytes) AS bytes
     FROM v$temp_space_header
     GROUP BY file_id) s
    ON f.file_id = s.file_id
WHERE
    t.tablespace_name = f.tablespace_name;

This query joins DBA_TEMP_FILES and V$TEMP_SPACE_HEADER to show the total space and the free space (in MB) for each file in the temporary tablespace.

These queries can help you track the available free space in your Oracle database’s temporary tablespace(s). If you need more granular details (such as how much space is actually being used), you can expand the query to get more detailed statistics from dynamic views like V$TEMP_FILE or V$SORT_SEGMENT.

Query to check High Watermark in Datafiles

 set verify off

column file_name format a50 word_wrapped

column smallest format 999,990 heading “Smallest|Size|Poss.”

column currsize format 999,990 heading “Current|Size”

column savings  format 999,990 heading “Poss.|Savings”

break on report

compute sum of savings on report

column value new_val blksize

select value from v$parameter where name = ‘db_block_size’;

/

select file_name,

       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,

       ceil( blocks*&&blksize/1024/1024) currsize,

       ceil( blocks*&&blksize/1024/1024) –

       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings

from dba_data_files a,

     ( select file_id, max(block_id+blocks-1) hwm

         from dba_extents

        group by file_id ) b

where a.file_id = b.file_id(+) order by savings desc

/

Query to check blocking sessions in Oracle database

 Query to check blocking sessions in Oracle database from v$lock:

SELECT DECODE(request,0,’Holder: ‘,’Waiter: ‘) || sid sess,
  id1,
  id2,
  lmode,
  request,
  type
FROM v$lock
WHERE (id1, id2, type) IN
  (SELECT id1, id2, type FROM v$lock WHERE request > 0
  )
ORDER BY id1,
  request;

How to update override email address using backend sql query in EBS 11i/12.x?

Query to check Override address in EBS workflow notification Mailer:

SQL>
 select fscpv.parameter_value
    from fnd_svc_comp_params_tl fscpt
    ,fnd_svc_comp_param_vals fscpv
    where fscpt.display_name = ‘Test Address’
    and fscpt.parameter_id = fscpv.parameter_id;   

PARAMETER_VALUE
——————————————————————————–
NONE

SQL Query to update Override email address from backend in EBS :

SQL> update fnd_svc_comp_param_vals
set    parameter_value = ‘TEST_EMAIL@DOMAIN.COM’
where  parameter_id =
( select parameter_id
 from   fnd_svc_comp_params_tl
 where  display_name = ‘Test Address’);  

1 row updated.

SQL> commit;

Commit complete.


Verify that override email address is updated:

SQL> select fscpv.parameter_value
    from fnd_svc_comp_params_tl fscpt
    ,fnd_svc_comp_param_vals fscpv
    where fscpt.display_name = ‘Test Address’
    and fscpt.parameter_id = fscpv.parameter_id;  
PARAMETER_VALUE
——————————————————————————–
TEST_EMAIL@DOMAIN.COM

SQL>

EBSO 12.2.x : Query to check status of ADOP session

Check Status of adop session using below sql query:

set pagesize 200;
set linesize 160;
col PREPARE_STATUS format a15
col APPLY_STATUS format a15
col CUTOVER_STATUS format a15
col ABORT_STATUS format a15
col STATUS format a15
select NODE_NAME,ADOP_SESSION_ID, PREPARE_STATUS , APPLY_STATUS  ,CUTOVER_STATUS , CLEANUP_STATUS , ABORT_STATUS , STATUS
from AD_ADOP_SESSIONS
order by ADOP_SESSION_ID;

Decoding Cutover status:



cutover_status=’Y’ ‘COMPLETED’
cutover_status not in (‘N’,’Y’,’X’) and status=’F’ ‘FAILED’
cutover_status=’0′ ‘CUTOVER STARTED’
cutover_status=’1′ ‘SERVICES SHUTDOWN COMPLETED’
cutover_status=’3′ ‘DB CUTOVER COMPLETED’
cutover_status=’D’ ‘FLIP SNAPSHOTS COMPLETED’
cutover_status=’4′ ‘FS CUTOVER COMPLETED’
cutover_status=’5′ ‘ADMIN STARTUP COMPLETED’
cutover_status=’6′ ‘SERVICES STARTUP COMPLETED’
cutover_status=’N’ ‘NOT STARTED’
cutover_status=’X’ ‘NOT APPLICABLE’

Query to check installed modules in Oracle EBS


The below query query gives the list of Installed/Not Installed/Shared Product Modules in Oracle E-Business Suite (EBS) application

set pages 20000;
col application_id for 9999;
col application_name for A50;
col status for A1;
col application_short_name for A10;
select fa.application_id,
fa.application_short_name,
fpi.status,
fatl.application_name
from
fnd_product_installations fpi,
fnd_application fa,
fnd_application_tl fatl
where
(
fa.application_id = fpi.application_id and
fa.application_id = fatl.application_id and
fatl.language = ‘US’
)
order by fa.application_short_name;

Check for values in Status column of the output, usually either I or N or S.

I – Installed
S – Shared
N – Not Licensed