Free space on DATAFILES

Query to findout free space in Datafiles.

select rownum as rank, a.*
from (
select df.tablespace_name tspace,
df.file_id,
df.bytes/(1024*1024) tot_ts_size,
sum(fs.bytes)/(1024*1024) free_ts_size,
(df.bytes-sum(fs.bytes))/(1024*1024) used_ts_size
from dba_free_space fs, (select tablespace_name,file_id, sum(bytes) bytes
from dba_data_files
group by tablespace_name,file_id ) df
where fs.tablespace_name = df.tablespace_name
and fs.file_id = df.file_id
and df.tablespace_name like UPPER('%&tablespace_name%')
group by df.tablespace_name, df.file_id, df.bytes
ORDER BY free_ts_size DESC) a
where rownum < 11
/

Inactive Sessions in Oracle applications

Query for Inactive Sessions:
Select distinct SUBSTR(d.user_name,1,20)"User Name",b.status,b.sid,b.serial# FROM v$process a,v$session b,fnd_logins c,fnd_user d WHERE a.pid=c.pid AND d.user_id AND a.adr=b.addr AND TO_DATE(c.start_time)=TO_DATE(sysdate) AND c.end_time IS NULL order by 1;

TEMP table space and usage

Query to findout the usage and free space on TEMP tablespace:
SELECT tablespace_name, SUM(bytes_used)/1024/1024/1024, SUM(bytes_free)/1024/1024/1024 from v$temp_space_header GROUP BY tablespace_name;

Query to findout temp file locations:
select file_name,sum(bytes)/1024/1024/1024 from dba_temp_files group by file_name;

Concurrent manager log file is empty

Error:The log file for this request is empty.The concurrent manager may have encountered an error while attempt to create this file.

solution:

check the softlink on that excutable..may be it is pointing to wrong location.

Example:

phoenix:FINIT3OA:/FINIT3OA/ora03/FINIT3OA/finit3oaappl/fr/1.0/bin$ ls -ltr /FINIT3OA/ora03/FINIT3OA/finit3oaappl/fr/1.0/bin/fr_msc_plan_interface

lrwxrwxrwx 1 appltst3 dba 61 Jul 7 04:33 /FINIT3OA/ora03/FINIT3OA/finit3oaappl/fr/1.0/bin/fr_msc_plan_interface -> /FINIT3OA/ora03/FINIT3OA/finit3oaappl/fnd/11.5.0/bin/fndcpesr

fr_msc_plan_interface -> /FINIT3OA/ora03/FINIT3OA/finit3oaappl/fnd/11.5.0/bin/fndcpesr

APPLPTMP set properly

ERROR:
ORA-10ORA-20100: File o0079057.tmp creation for FND_FILE failed.

You will find more information on the cause of the error in request log.


Solution:

check the APPLPTMP location on DB tier in init.ora file (UTL_FILE_DIR).It shou;d be set properly.
And check the permissions on that temp file for that application user.

FR_TOP issue after autoconfig or Postclone steps

Error:
function not available for this responsibility.Change responsobilities or contact system administrator.

1.Set FR_TOP and FRLAAPPS location in $IAS_APACHE_TOP/Apache/Jserv/etc/formservlet.ini on application tier.

2.Check the FR_TOP locationin topfile.txt($APPL_TOP/admin)

Inactive no manager

1.Restart Concurrent manager.

2.Purge concurrent manager/request log files regular interval.

or

3. Logon to SQL*Plus as 'APPS' and run the following SQL statement:

update fnd_concurrent_queues set control_code = 'A' where concurrent_queue_name = 'FNDCRM';

Batchmgr:XXXX/XXXX/XXXX.log :cannot create

For this issue:

1.Check the mount point space on concurrent manager log directory.And delete the $OAD_TOP/temp directory and recreate it.

or

2.check the read/write permissions on applmgr for that concurrent manager log directory.

SQL commnadto hold/cancel all concurrent requests

Manually cancel the request out of the queue with the following SQL against the offending
request id(s). This can be safely done while managers are up and running:


SQL> UPDATE fnd_concurrent_requests
SET phase_code = 'C', status_code = 'X'
WHERE request_id = '';
commit;

Note: To obtain request details prior to cancelling the request, use Note 134035.1 ANALYZEREQ.SQL - Detailed Analysis of One Concurrent Request. When prompted, provide the request id to be analyzed. This can be useful for determining the reason why the request may be stuck in the queue