RSS

Category Archives: Queries

Queries: list useful queries for both Database and EBS in the administration, configuration and performance fields

Data Objects in a data file in Oracle

How to know, find out data objects “Segments” in a given data file??

SELECT <distinct> a.segment_name,
 a.SEGMENT_TYPE,
 a.TABLESPACE_NAME,
 a.file_id,
 b.file_name Datafile_name
 FROM dba_extents a, dba_data_files b
 WHERE a.file_id = b.file_id
 AND b.file_id = <data file id>;

 

For the opposite purpose where you want to know in with data file a data object stored, you can use the following query:

SELECT a.segment_name, a.file_id, b.file_name Datafile_name
 FROM dba_extents a, dba_data_files b
 WHERE a.file_id = b.file_id AND a.segment_name = '<object_name>';
 
 

Tags: , , ,

XML Report Publisher Fails With java.lang.OutOfMemoryError

Problem:
XML publisher completing in Warning when data size is big. One or more post processing actions failed java.lang.OutOfMemoryError

Symptoms:
– report ends with Warning
– with consulting OPP log file shows ==> Caused by: java.lang.OutOfMemoryError: Java heap space

Solution:
1. First test how much memory you can create in OS using the following command
$java -mx2700m -version

2. Determine what the heap size per OPP process is currently:
select DEVELOPER_PARAMETERS from FND_CP_SERVICES
where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME = ‘FNDCPOPP’);

3. Increase the Heap Space per Process to 1024 or to maximum result of OS command:
update FND_CP_SERVICES
set DEVELOPER_PARAMETERS =’J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx1024m’
where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME = ‘FNDCPOPP’);
commit;

4. Increase the values for these profiles for at least 2 hours each (7200):
Concurrent:OPP Process Timeout
Concurrent:OPP Response Timeout

5. Restart the concurrent managers
 

Tags: , , , , , , ,

EBS, How much time each run of a program takes

Here is a query that help you to  know how much time each run of a program takes, however it is for administration and performance tracing purposes.

SELECT REQUEST_ID,
TO_CHAR(REQUEST_DATE,’DD-MON-YYYY’) “Date”,
RPAD(USER_CONCURRENT_PROGRAM_NAME,60) “Program Name”,
RPAD(ARGUMENT_TEXT,20) “Arg”,
RPAD(TO_CHAR(A.ACTUAL_START_DATE, ‘hh24:mi:ss’),10) “Start”,
RPAD(TO_CHAR(A.ACTUAL_COMPLETION_DATE, ‘hh24:mi:ss’),10) “End”,
RPAD(TO_CHAR( (TRUNC(SYSDATE) + ( A.ACTUAL_COMPLETION_DATE – A.ACTUAL_START_DATE)), ‘hh24:mi:ss’),10) “Actual”,
C.USER_NAME,
STATUS_CODE “S”
FROM APPLSYS.FND_CONCURRENT_REQUESTS A, APPLSYS.FND_CONCURRENT_PROGRAMS_TL B, APPS.FND_USER C
WHERE B.USER_CONCURRENT_PROGRAM_NAME LIKE ‘Autoinvoice Import Program’ — insert program “request” to query
AND A.CONCURRENT_PROGRAM_ID = B.CONCURRENT_PROGRAM_ID
AND A.REQUEST_DATE >= ’24-sep-2012′ — insert date you need to query from
AND A.REQUESTED_BY = C.USER_ID
ORDER BY REQUEST_ID DESC;

———

Cheers

Muhammad Rashed

 
Leave a comment

Posted by on February 4, 2013 in Queries

 

Tags: , , , ,

SQL to get profile option values list using specific criteria

 

[HTD000069] Oracle E-Business Suite Show Active Database Sessions Details (Machine, Program, Module, Username, Action,…… )

 

Oracle E-Business Suite Show Active Database Sessions Details (Machine, Program, Module, Username, Action,…… )

 

Oracle 11i/R12 List all Requests/Programs within a Responsibility

 
SELECT DISTINCT FCPT.USER_CONCURRENT_PROGRAM_NAME,
 FCPT.DESCRIPTION,
 FA_VL.APPLICATION_NAME,
 FA_VL.APPLICATION_SHORT_NAME,
 RSP_TL.RESPONSIBILITY_NAME
 , FRG.REQUEST_GROUP_NAME

FROM FND_REQUEST_GROUPS FRG,FND_CONCURRENT_PROGRAMS_TL FCPT

 INNER JOIN FND_REQUEST_GROUP_UNITS FRGU
 ON FCPT.CONCURRENT_PROGRAM_ID = FRGU.REQUEST_UNIT_ID
 OR FCPT.APPLICATION_ID = FRGU.APPLICATION_ID

 INNER JOIN FND_RESPONSIBILITY FR
 ON FR.REQUEST_GROUP_ID = FRGU.REQUEST_GROUP_ID

 INNER JOIN FND_RESPONSIBILITY_TL RSP_TL
 ON FR.RESPONSIBILITY_ID = RSP_TL.RESPONSIBILITY_ID

 INNER JOIN FND_APPLICATION_VL FA_VL
 ON FA_VL.APPLICATION_ID = FCPT.APPLICATION_ID

WHERE UPPER (FCPT.USER_CONCURRENT_PROGRAM_NAME) LIKE
    NVL (UPPER ('%' || :_PROGRAM_NAME || '%'), '')
  AND UPPER (RESPONSIBILITY_NAME) LIKE
    NVL (UPPER ('%' || :RESPONSIBILITY_NAME || '%'), '')
  AND FCPT.LANGUAGE = USERENV ('LANG')
  AND FRG.REQUEST_GROUP_ID = FRGU.REQUEST_GROUP_ID
ORDER BY 3, 1;
 
Leave a comment

Posted by on January 5, 2013 in Queries

 

Tags: , , , , ,