Query To Check Inventory Interface Managers Status

Download(moninterfacemgrs.sql)

REM +======================================================================+
REM                    
REM File Name: moninterfacemgrs.sql
REM 
REM Description:
REM   Query To Check Inventory Interface Managers Status
REM   Such As Cost Manager , Lot Move Transaction , Material transaction & 
REM   Move transaction Interface Managers.
REM
REM Notes:
REM   Usage: sqlplus <apps_user/apps_passwd> @moninterfacemgrs.sql 
REM   
REM +======================================================================+

Clear columns
set lines 180
 col "Name" for a30
 SELECT 
   x.PROCESS_TYPE "Name", 
   decode((select '1' 
  FROM APPS.FND_CONCURRENT_REQUESTS cr, 
  APPS.FND_CONCURRENT_PROGRAMS_VL cp, 
  APPS.FND_APPLICATION A 
    WHERE cp.concurrent_program_id = cr.concurrent_program_id 
   AND cp.CONCURRENT_PROGRAM_NAME = x.PROCESS_NAME 
   AND cp.APPLICATION_ID = a.application_id 
   AND a.APPLICATION_SHORT_NAME = x.PROCESS_APP_SHORT_NAME 
   AND PHASE_CODE != 'C' and rownum=1),'1','Active','Inactive') "Status", 
   x.WORKER_ROWS "Worker Rows", 
   x.TIMEOUT_HOURS "Timeout Hours", 
   x.TIMEOUT_MINUTES "Timeout Minutes", 
   x.PROCESS_HOURS "Process Interval Hours", 
   x.PROCESS_MINUTES "Process Interval Minutes", 
   x.PROCESS_SECONDS "Process Interval Seconds" 
 FROM ( 
   SELECT 
   MIPC.PROCESS_CODE , 
   MIPC.PROCESS_STATUS , 
   MIPC.PROCESS_INTERVAL , 
   MIPC.MANAGER_PRIORITY , 
   MIPC.WORKER_PRIORITY , 
   MIPC.WORKER_ROWS , 
   MIPC.PROCESSING_TIMEOUT , 
   MIPC.PROCESS_NAME , 
   MIPC.PROCESS_APP_SHORT_NAME , 
   A.MEANING PROCESS_TYPE , 
   FLOOR(MIPC.PROCESS_INTERVAL/3600) PROCESS_HOURS , 
   FLOOR((MIPC.PROCESS_INTERVAL - 
   (FLOOR(MIPC.PROCESS_INTERVAL/3600) * 3600))/60) PROCESS_MINUTES , 
   (MIPC.PROCESS_INTERVAL - (FLOOR(MIPC.PROCESS_INTERVAL/3600) * 3600) - 
   (FLOOR((MIPC.PROCESS_INTERVAL - 
   (FLOOR(MIPC.PROCESS_INTERVAL/3600) * 3600))/60) * 60)) PROCESS_SECONDS , 
   FLOOR(MIPC.PROCESSING_TIMEOUT/3600) TIMEOUT_HOURS , 
   FLOOR((MIPC.PROCESSING_TIMEOUT - 
   FLOOR(MIPC.PROCESSING_TIMEOUT/3600) * 3600)/60) TIMEOUT_MINUTES 
   FROM 
   APPS.MTL_INTERFACE_PROC_CONTROLS MIPC, 
   APPS.MFG_LOOKUPS A 
   WHERE 
   A.LOOKUP_TYPE = 'PROCESS_TYPE' AND 
   A.LOOKUP_CODE = MIPC.PROCESS_CODE 
 ) x 
 -- WHERE x.PROCESS_TYPE = 'Cost Manager' -- uncomment this to display only the cost manager; Possible Values: Cost Manager; Lot Move Transaction; Material transaction; Move transaction
 ORDER BY 1;

Query To Check All Workflow Mailer Component Status

Download(allwfstat.sql)

REM +======================================================================+
REM                    
REM File Name: allwfstat.sql
REM 
REM Description:
REM   Query To Check All Workflow Mailer Component Status
REM   
REM Notes:
REM   Usage: sqlplus <apps_user/apps_passwd> @allwfstat.sql 
REM   
REM +======================================================================+

Clear columns
SET lines 180
SET pages 100
COL STARTUP_MODE for a15
COL COMPONENT_NAME for a50
COL COMPONENT_STATUS for a20
SELECT   component_id, component_name, component_type, startup_mode, component_status
 FROM applsys.fnd_svc_components
 --WHERE component_status <> 'RUNNING'
ORDER BY component_status;

Query To Check Inactive/Down Concurrent Managers Details

Download(downcmstat.sql)

REM +==========================================================================+
REM                    
REM File Name: downcmstat.sql
REM 
REM Description:
REM   Query To Check Concurrent Managers Which are Currently Down or INACTIVE
REM   
REM Notes:
REM   Usage: sqlplus <apps_user/apps_passwd> @downcmstat.sql 
REM   
REM +==========================================================================+

Clear columns
SET lines 180
 COL "Concurrent Manager" for a50
 col "Node" for a20
 SELECT b.user_concurrent_queue_name "Concurrent Manager", a.TARGET_NODE "Node", a.running_processes "ACTUAL Processes", a.max_processes "TARGET Processes"
    ,DECODE (b.control_code
      ,'D', 'Deactivating'
      ,'E', 'Deactivated'
      ,'N', 'Node unavai'
      ,'A', 'Activating'
      ,'X', 'Terminated'
      ,'T', 'Terminating'
      ,'V', 'Verifying'
      ,'O', 'Suspending'
      ,'P', 'Suspended'
      ,'Q', 'Resuming'
      ,'R', 'Restarting'
      ) status
   FROM apps.fnd_concurrent_queues a, apps.fnd_concurrent_queues_vl b
  WHERE a.concurrent_queue_id = b.concurrent_queue_id AND a.running_processes <> a.max_processes
 UNION
 SELECT b.user_concurrent_queue_name "Concurrent Manager", a.TARGET_NODE "Node", a.running_processes "ACTUAL Processes", a.max_processes "TARGET Processes"
    ,DECODE (b.control_code
      ,'D', 'Deactivating'
      ,'E', 'Deactivated'
      ,'N', 'Node unavai'
      ,'A', 'Activating'
      ,'X', 'Terminated'
      ,'T', 'Terminating'
      ,'V', 'Verifying'
      ,'O', 'Suspending'
      ,'P', 'Suspended'
      ,'Q', 'Resuming'
      ,'R', 'Restarting'
      ) status
   FROM apps.fnd_concurrent_queues a, apps.fnd_concurrent_queues_vl b
  WHERE a.concurrent_queue_id = b.concurrent_queue_id AND a.concurrent_queue_name = 'FNDICM' AND (a.running_processes = 0 OR a.max_processes = 0);

Query To Check Concurrent Managers Status

Download(allcmstat.sql)

REM +======================================================================+
REM                    
REM File Name: allcmstat.sql
REM 
REM Description:
REM   Query To Check All Concurrent Managers Status
REM   This query will give the details for all concurrent managers  
REM   which are currently active or running.
REM
REM Notes:
REM   Usage: sqlplus <apps_user/apps_passwd> @allcmstat.sql 
REM   
REM +======================================================================+

Clear Columns
SET lines 180
 COL "Concurrent Manager" for a50
 col "Node" for a20
 SELECT   b.user_concurrent_queue_name "Concurrent Manager", a.TARGET_NODE "Node", a.running_processes "ACTUAL Processes", a.max_processes "TARGET Processes"
   ,DECODE (b.control_code
     ,'D', 'Deactivating'
     ,'E', 'Deactivated'
     ,'N', 'Node unavai'
     ,'A', 'Activating'
     ,'X', 'Terminated'
     ,'T', 'Terminating'
     ,'V', 'Verifying'
     ,'O', 'Suspending'
     ,'P', 'Suspended'
     ,'Q', 'Resuming'
     ,'R', 'Restarting'
     ) status
  FROM apps.fnd_concurrent_queues a, apps.fnd_concurrent_queues_vl b
    WHERE a.concurrent_queue_id = b.concurrent_queue_id AND a.running_processes = a.max_processes
 ORDER BY a.max_processes DESC;

SQL Script To Lookup Database Details for a given spid

REM +======================================================================+
REM                    
REM File Name: oraspidinfo.sql
REM 
REM Description:
REM   SQL Script To Lookup Database Details for a given spid
REM   
REM Notes:
REM   Usage: sqlplus "/ as sysdba" @oraspidinfo.sql spid
REM
REM   This Query Requires Oracle SPID as an Input
REM   
REM +======================================================================+

Since this is a large script please click on below link to download and view the SQL Script:

Download(oraspidinfo.sql)

SQL Script To Lookup Database Details for a given sid

REM +======================================================================+
REM                    
REM File Name: orasidinfo.sql
REM 
REM Description:
REM   SQL Script To Lookup Database Details for a given sid
REM   
REM Notes:
REM   Usage: sqlplus "/ as sysdba" @orasidinfo.sql sid
REM
REM   This Query Requires Oracle SID as an Input
REM   
REM +======================================================================+

Since this is a large script please click on below link to download and view the SQL Script:

Download(orasidinfo.sql)

Query To Get Datafile Details for a Given Tablespace

Download(datafilesforts.sql)

REM +======================================================================+
REM                    
REM File Name: datafilesforts.sql
REM 
REM Description:
REM   Query To Get Datafile Details for a Given Tablespace
REM   
REM Notes:
REM   Usage: sqlplus "/ as sysdba" @datafilesforts.sql 
REM
REM   This Query Requires Tablespace Name as an Input
REM   
REM +======================================================================+

ACCEPT TS_NAME PROMPT "Enter Tablespace Name: ";

clear columns
set lines 180
set pages 100
col AUTOEXTENSIBLE for a16
col DATA_FILE_NAME for a60 WORD_WRAPPED
SELECT SUBSTR (df.file_name, 1, 60) DATA_FILE_NAME, df.bytes / 1024 / 1024 Size_MB,
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))
used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_mb,df.AUTOEXTENSIBLE,
df.increment_by * (df.bytes/df.blocks) /1024/1024 "NEXT_EXTENT_MB",
df.MAXBYTES  / 1024 / 1024 MAXSIZE_MB
FROM dba_data_files df, dba_free_space dfs
WHERE df.FILE_ID = dfs.file_id(+)
AND df.TABLESPACE_NAME = '&TS_NAME'
GROUP BY dfs.file_id, df.file_NAME, df.file_id, df.bytes,df.AUTOEXTENSIBLE,df.MAXBYTES,df.increment_by,df.blocks
ORDER by df.file_name;
undef TS_NAME