Shrink Datafiles

Shrink Datafiles – Adjusting HWM

This below example is derived from “Shrinking datafiles” scripts from asktom.oracle.com
Scenario:-
Lets suppose the datafile size is 100m with autoexted upto maxsize 32gb.
1) Created a table
2) Inserted 50millions of rows with commit
— Data file size extended from 100mb to 20gb.
3) performed so many DML’s
— Data file size extended from 100mb to >20gb.
4) Truncated Table/Dropped Table.
Space will can be used further to use for other objects in datafile. But as per my requirement when i really won’t use that datafile, then what is the use to keep datafile size > 20gb and which is causing much disk usage at OS level? Instead of that check the HWM of datafile, at what minimum size you can resize it?
Script for MAX-Shrink:-

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
/

In lower versions you can use below query to find out possible savings from each data files of Database.

set linesize 400
col tablespace_name format a15
col file_size format 99999
col file_name format a50
col hwm format 99999
col can_save format 99999
SELECT tablespace_name, file_name, file_size, hwm, file_size-hwm can_save
FROM (SELECT /*+ RULE */ ddf.tablespace_name, ddf.file_name file_name,
ddf.bytes/1048576 file_size,(ebf.maximum + de.blocks-1)*dbs.db_block_size/1048576 hwm
FROM dba_data_files ddf,(SELECT file_id, MAX(block_id) maximum FROM dba_extents GROUP BY file_id) ebf,dba_extents de,
(SELECT value db_block_size FROM v$parameter WHERE name='db_block_size') dbs
WHERE ddf.file_id = ebf.file_id
AND de.file_id = ebf.file_id
AND de.block_id = ebf.maximum
ORDER BY 1,2);

Sample Output of above query:-

                                                    Smallest
                                                       Size  Current    Poss.
FILE_NAME                                             Poss.     Size  Savings
-------------------------------------------------- -------- -------- --------
/u02/oradata/cpdevdb/cp_jrnl_idx3.dbf                13,697   32,708   19,011
/u02/oradata/cpdevdb/cp_jrnl_idx2.dbf                13,761   32,748   18,987
/u02/oradata/cpdevdb/cp_jrnl_idx1_01.dbf             13,953   32,748   18,795
/u02/oradata/cpdevdb/cp_jrnl_idx1.dbf                13,953   32,728   18,775
/u02/oradata/cpdevdb/cp_jrnl_idx3_02.dbf             15,681   32,738   17,057
/u02/oradata/cpdevdb/cp_jrnl_idx3_03.dbf             15,681   32,718   17,037
/u02/oradata/cpdevdb/psindex_01.dbf                  11,285   26,718   15,433
/u02/oradata/cpdevdb/cp_jrnl_idx4.dbf                22,913   32,728    9,815
/u02/oradata/cpdevdb/cpled_2010_1.dbf                 7,721   10,858    3,137
/u02/oradata/cpdevdb/cpled_2009_1.dbf                 4,745    6,828    2,083
/u02/oradata/cpdevdb/cpled_2008_1.dbf                 4,873    6,948    2,075
/u02/oradata/cpdevdb/cpled_2009_2.dbf                 4,825    6,728    1,903
/u02/oradata/cpdevdb/cpled_idx_1.dbf                 19,905   21,000    1,095
                                                                     --------
sum                                                                   145,203

By the output i can reclaim space around 141gb of space, without reorganizing any objects.
Resize Datafiles:-
We can resize datafile up to “Smallest Size Poss” value (or) we can assign any fixed size (or) On top of that we can enable autoextend up to maximum size of datafile.

alter database datafile '/u02/oradata/cpdevdb/cp_jrnl_idx3.dbf' resize 13700m;
alter database datafile '/u02/oradata/cpdevdb/cp_jrnl_idx3.dbf' autoextend on next 10m maxsize 32767m;

After resizing datafile, Possible savings recorded is around 19GB.
Execute above Script to check changes after resizing of datafiles:-

                                                   Smallest
                                                       Size  Current    Poss.
FILE_NAME                                             Poss.     Size  Savings
-------------------------------------------------- -------- -------- --------
/u02/oradata/cpdevdb/cp_jrnl_idx3.dbf                13,697   13,700        3
/u02/oradata/cpdevdb/cp_jrnl_idx2.dbf                13,761   13,770        9
/u02/oradata/cpdevdb/cp_jrnl_idx1_01.dbf             13,953   13,970       17
/u02/oradata/cpdevdb/cp_jrnl_idx1.dbf                13,953   13,970       17
/u02/oradata/cpdevdb/cp_jrnl_idx3_02.dbf             15,681   15,690        9
/u02/oradata/cpdevdb/cp_jrnl_idx3_03.dbf             15,681   15,690        9
/u02/oradata/cpdevdb/psindex_01.dbf                  11,285   11,300       15
/u02/oradata/cpdevdb/cp_jrnl_idx4.dbf                22,913   22,920        7
/u02/oradata/cpdevdb/cpled_2010_1.dbf                 7,721    7,730        9
/u02/oradata/cpdevdb/cpled_2009_1.dbf                 4,745    4,750        5
/u02/oradata/cpdevdb/cpled_2008_1.dbf                 4,873    4,900       27
/u02/oradata/cpdevdb/cpled_2009_2.dbf                 4,825    4,850       25
/u02/oradata/cpdevdb/cpled_idx_1.dbf                 19,905   19,910        5
                                                                     --------
sum                                                                       157

In earlier output possible savings is 141gb, Now possible savings is 157mb, So we reclaimed ~141gb of space at OS level. 
Now lets compare OS level free space  Before & After

Killing multiple sessions in Oracle Database

Hi,

We often get task to kill sessions which are active,inactive and the session related with a particular program.When we have one or two sessions we can kill it easily,if we have multiple sessions we can do it as follows:


Step 1:Check the Name of the Database
sqlplus "/as sysdba"

SQL>Select name from v$database;

Step 2:How to kill all inactive session in Database

sqlplus "/as sysdba"

SQL>set heading off

SQL>spool kill12.sql

SELECT 'ALTER SYSTEM KILL SESSION '||''''||sid ||','|| serial#||''''||' immediate;'
 FROM v$session
 WHERE status ='INACTIVE' and type != 'BACKGROUND';

SQL>spool off

then execute the sql script

SQL> @kill12.sql

Note:For RAC Database where we have more than 1 instances,we need to follow step 2 on all the Oracle instances.

How to kill all active session in Database:
sqlplus "/as sysdba"

SQL>set heading off
SQL>spool kill_active.sql

SELECT 'ALTER SYSTEM KILL SESSION '||''''||sid ||','|| serial#||''''||' immediate;'
FROM v$session
WHERE status ='ACTIVE' and type != 'BACKGROUND';

SQL>spool off

Note:For RAC Database where we have more than 1 instances,we need to follow step 2 on all the Oracle instances.


How to kill all ODI sessions in Database:
sqlplus "/as sysdba"

SQL>set heading off
SQL>set lines 1000
SQL>set linesize 2000
SQL>spool kill_active.sql

SELECT 'ALTER SYSTEM KILL SESSION '||''''||sid ||','|| serial#||''''||' immediate;'
FROM v$session
WHERE status ='ACTIVE' AND USERNAME LIKE '%ODI%';

SQL>spool off


Note:For RAC Database where we have more than 1 instances,we need to follow step 2 on all the Oracle instances.


How to get the list of Users and Processes running ODI sessions:

SQL> SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45

SELECT s.inst_id,s.sid,s.serial#,p.spid, s.username, s.program
 FROM   gv$session s
 JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND' AND S.USERNAME LIKE '%ODI%'; 

 How to kill a particular object blocking session:

 1.Find the tables(objects) which are locked:

SELECT o.owner, o.object_name, o.object_type, o.last_ddl_time, o.status, l.session_id, l.oracle_username, l.locked_mode
FROM dba_objects o, gv$locked_object l
WHERE o.object_id = l.object_id and o.object_name='XX_OBJECT';

2.Killing the session holding the lock:

--Find the serial# for the sessions holding the lock:

SQL> select SERIAL# from v$session where SID=667;

SERIAL#
----------
21091

SQL> alter system kill session '667,21091';

Note:For RAC Database where we have more than 1 instances,we need to follow step 2 on all the Oracle instances.

Enjoy DBA tasks...

Happy DBA learning..

Find Trace to Application

FND Debug profiles options enabled userd details | Oracle Apps R12

set pagesize 200
col NAME for a25
col LEV for a6
col CONTEXT for a25
col VALUE for a50
col USER_PROFILE_OPTION_NAME for a37
select po.profile_option_name "NAME",
po.USER_PROFILE_OPTION_NAME,
decode(to_char(pov.level_id),
'10001', 'SITE',
'10002', 'APP',
'10003', 'RESP',
'10005', 'SERVER',
'10006', 'ORG',
'10004', 'USER', '???') "LEV",
decode(to_char(pov.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.name,
'10004', usr.user_name,
'???') "CONTEXT",
pov.profile_option_value "VALUE"
from FND_PROFILE_OPTIONS_VL po,
FND_PROFILE_OPTION_VALUES pov,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
where (po.profile_option_name like '%AFLOG_ENABLED%' or po.profile_option_name like
'%FND_INIT_SQL%')
and pov.application_id = po.application_id
and pov.profile_option_id = po.profile_option_id
and usr.user_id (+) = pov.level_value
and rsp.application_id (+) = pov.level_value_application_id
and rsp.responsibility_id (+) = pov.level_value
and app.application_id (+) = pov.level_value
and svr.node_id (+) = pov.level_value
and org.organization_id (+) = pov.level_value
order by "NAME", pov.level_id, "VALUE";

*********** OR ***************************
set lines 400
col APP_SHORT for a20
col OPTVAL for a30
col OPTNAME for a40
col D_LEVEL for a40
select distinct
a.application_short_name app_short,
user_profile_option_name optname,
decode(level_id,
10001,'SITE',
10002,'APP : '||a2.application_short_name,
10003,'RESP: '||r.responsibility_key,
10004,'USER: '||u.user_name,
'Unknown') d_level,
profile_option_value optval,
v.last_update_date updated
from fnd_profile_options_vl o,
fnd_profile_option_values v,
fnd_application a,
fnd_application a2,
fnd_responsibility r,
fnd_user u
where (
o.user_profile_option_name like '%Debug%' or
o.user_profile_option_name like '%DEBUG%' or
o.user_profile_option_name like '%Trace%' or
o.user_profile_option_name like '%TRACE%'
)
and a.application_id = v.application_id
and o.application_id = v.application_id
and o.profile_option_id = v.profile_option_id
-- Find the associate level for profile
and r.application_id (+) = v.level_value_application_id
and r.responsibility_id (+) = v.level_value
and a2.application_id (+) = v.level_value
and u.user_id (+) = v.level_value
and profile_option_value = 'Y'
order by 2,1,3,4;



****************************How to truncate FND_LOG_MESSAGES files***********************************
select owner,tablespace_name,segment_name,sum(bytes/1024/1024/1024) from dba_segments where segment_name='FND_LOG_MESSAGES' GROUP BY owner,segment_name, tablespace_name;

OWNER                          TABLESPACE_NAME SEGMENT_NAME                                                                      SUM(BYTES/1024/1024/1024)
------------------------------ --------------- --------------------------------------------------------------------------------- -------------------------
APPLSYS                        APPS_TS_TX_DATA FND_LOG_MESSAGES                                                                                 77.0758057


SQL>truncate table APPLSYS.FND_LOG_MESSAGES;


****************LOG messages are not created in FND_LOG_MESSAGES*****************************
   
ALTER SEQUENCE  applsys.FND_LOG_MESSAGES_S MAXVALUE 9999999999;

SQL> select FND_LOG_MESSAGES_S.NEXTVAL from DUAL;
NEXTVAL
----------
2147483815
SQL> select FND_LOG_MESSAGES_S.NEXTVAL from DUAL;
NEXTVAL

Find OPP logfile

SELECT fcpa.concurrent_request_id req_id, fcp.node_name, fcp.logfile_name
  FROM fnd_conc_pp_actions fcpa, fnd_concurrent_processes fcp
 WHERE fcpa.processor_id = fcp.concurrent_process_id
   AND fcpa.action_type = 6
   AND fcpa.concurrent_request_id = :P_REQUEST_ID;

Script Find Concurrent request

Very Important Concurrent Request Scripts | Oracle Apps R12

****HISTORY OF CONCURRENT REQUEST - SCRIPT (PROGRAM WISE) *****

set pagesize 200
set linesize 200
col "Who submitted" for a25
col "Status" for a10
col "Parameters" for a20
col USER_CONCURRENT_PROGRAM_NAME for a42
SELECT distinct t.user_concurrent_program_name,
r.REQUEST_ID,
to_char(r.ACTUAL_START_DATE,'dd-mm-yy hh24:mi:ss') "Started at",
to_char(r.ACTUAL_COMPLETION_DATE,'dd-mm-yy hh24:mi:ss') "Completed at",
decode(r.PHASE_CODE,'C','Completed','I','Inactive','P ','Pending','R','Running','NA') phasecode,
decode(r.STATUS_CODE, 'A','Waiting', 'B','Resuming', 'C','Normal', 'D','Cancelled', 'E','Error', 'F','Scheduled', 'G','Warning', 'H','On Hold', 'I','Normal', 'M',
'No Manager', 'Q','Standby', 'R','Normal', 'S','Suspended', 'T','Terminating', 'U','Disabled', 'W','Paused', 'X','Terminated', 'Z','Waiting') "Status",r.argument_text "Parameters",substr(u.description,1,25) "Who submitted",round(((nvl(v.actual_completion_date,sysdate)-v.actual_start_date)*24*60)) Etime
FROM
apps.fnd_concurrent_requests r ,
apps.fnd_concurrent_programs p ,
apps.fnd_concurrent_programs_tl t,
apps.fnd_user u, apps.fnd_conc_req_summary_v v
WHERE
r.CONCURRENT_PROGRAM_ID = p.CONCURRENT_PROGRAM_ID
AND r.actual_start_date >= (sysdate-30)
--AND r.requested_by=22378
AND   r.PROGRAM_APPLICATION_ID = p.APPLICATION_ID
AND t.concurrent_program_id=r.concurrent_program_id
AND r.REQUESTED_BY=u.user_id
AND v.request_id=r.request_id
--AND r.request_id ='2260046' in ('13829387','13850423')
and t.user_concurrent_program_name like '%%'
order by to_char(r.ACTUAL_COMPLETION_DATE,'dd-mm-yy hh24:mi:ss');

 *** Requests completion date details ***

SELECT request_id, TO_CHAR( request_date, 'DD-MON-YYYY HH24:MI:SS' )

request_date, TO_CHAR( requested_start_date,'DD-MON-YYYY HH24:MI:SS' )

requested_start_date, TO_CHAR( actual_start_date, 'DD-MON-YYYY HH24:MI:SS' )

actual_start_date, TO_CHAR( actual_completion_date, 'DD-MON-YYYY HH24:MI:SS' )

actual_completion_date, TO_CHAR( sysdate, 'DD-MON-YYYY HH24:MI:SS' )

current_date, ROUND( ( NVL( actual_completion_date, sysdate ) - actual_start_date ) * 24, 2 ) duration

FROM fnd_concurrent_requests

WHERE request_id = TO_NUMBER('&p_request_id');

*** Reqid_from sid **

SELECT a.request_id, a.PHASE_CODE, a.STATUS_CODE,

d.sid as Oracle_SID,

d.serial#,

d.osuser,

d.process,

c.SPID as OS_Process_ID

FROM apps.fnd_concurrent_requests a,

apps.fnd_concurrent_processes b,

gv$process c,

gv$session d

WHERE a.controlling_manager = b.concurrent_process_id

AND c.pid = b.oracle_process_id

AND b.session_id=d.audsid AND a.PHASE_CODE='R' AND a.STATUS_CODE='R'

AND d.sid = &SID;

How to Determine Which Manager Ran a Specific Concurrent Request?

col USER_CONCURRENT_QUEUE_NAME for a100

select b.USER_CONCURRENT_QUEUE_NAME from 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 = '&conc_reqid';

Concurrent request status for a given sid?

col MODULE for a20

col OSUSER for a10

col USERNAME for a10

set num 10

col MACHINE for a20

set lines 200

col SCHEMANAME for a10

select s.INST_ID,s.sid,s.serial#,p.spid os_pid,s.status, s.osuser,s.username, s.MACHINE,s.MODULE, s.SCHEMANAME,

s.action from gv$session s, gv$process p WHERE s.paddr = p.addr and s.sid = '&oracle_sid';

Find out request id from Oracle_Process Id: 

select REQUEST_ID,ORACLE_PROCESS_ID,OS_PROCESS_Id from apps.fnd_concurrent_requests where ORACLE_PROCESS_ID='&a';

To find concurrent program name,phase code,status code for a given request id?

SELECT request_id, user_concurrent_program_name, DECODE(phase_code,'C','Completed',phase_code) phase_code, DECODE(status_code,'D', 'Cancelled' ,

'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X', 'Terminated',  'C', 'Normal', status_code) status_code, to_char(actual_start_date,'dd-mon-yy:hh24:mi:ss') Start_Date, to_char(actual_completion_date,'dd-mon-yy:hh24:mi:ss'), completion_text FROM apps.fnd_conc_req_summary_v WHERE request_id = '&req_id' ORDER BY 6 DESC;

To find the sql query for a given concurrent request sid?

select sid,sql_text from gv$session ses, gv$sqlarea sql where 

ses.sql_hash_value = sql.hash_value(+) and ses.sql_address = sql.address(+) and ses.sid='&oracle_sid'

/

To find child requests for Parent request id.

set lines 200

col USER_CONCURRENT_PROGRAM_NAME for a40

col PHASE_CODE for a10

col STATUS_CODE for a10

col COMPLETION_TEXT for a20

SELECT sum.request_id,req.PARENT_REQUEST_ID,sum.user_concurrent_program_name, DECODE(sum.phase_code,'C','Completed',sum.phase_code) phase_code, DECODE(sum.status_code,'D', 'Cancelled' ,

'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X', 'Terminated',  'C', 'Normal', sum.status_code) status_code, sum.actual_start_date, sum.actual_completion_date, sum.completion_text FROM apps.fnd_conc_req_summary_v sum, apps.fnd_concurrent_requests req where  req.request_id=sum.request_id and req.PARENT_REQUEST_ID = '&parent_concurrent_request_id';

set col os_process_id for 99

select HAS_SUB_REQUEST, is_SUB_REQUEST, parent_request_id, ORACLE_PROCESS_ID, ORACLE_SESSION_ID, OS_PROCESS_ID from fnd_concurrent_requests where request_id= '&Req_ID' ;

Cancelling Concurrent request :

--By request id 

update fnd_concurrent_requests

set status_code='D', phase_code='C'

where request_id=&req_id;

--by program_id

update fnd_concurrent_requests

set status_code='D', phase_code='C'

where CONCURRENT_PROGRAM_ID=&prg_id;

To terminate the all concurrent requests using by Module wise.

select 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' immediate;' from gv$session where MODULE like 'GLPREV';

History of concurrent requests which are error out 

SELECT a.request_id "Req Id"

,a.phase_code,a.status_code

, actual_start_date

, actual_completion_date

,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name "program"

FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b

,applsys.fnd_concurrent_queues q

,APPLSYS.fnd_concurrent_programs c

,APPLSYS.fnd_concurrent_programs_tl ctl

WHERE a.controlling_manager = b.concurrent_process_id

AND a.concurrent_program_id = c.concurrent_program_id

AND a.program_application_id = c.application_id

AND a.status_code = 'E'

AND a.phase_code = 'C'

AND actual_start_date > sysdate - 2

AND b.queue_application_id = q.application_id

AND b.concurrent_queue_id = q.concurrent_queue_id

AND ctl.concurrent_program_id = c.concurrent_program_id

AND ctl.LANGUAGE = 'US'

ORDER BY 5 DESC; 

***** Find out Concurrent Program which enable with trace****

col User_Program_Name for a40

col Last_Updated_By for a30

col DESCRIPTION for a30

SELECT A.CONCURRENT_PROGRAM_NAME "Program_Name",

SUBSTR(A.USER_CONCURRENT_PROGRAM_NAME,1,40) "User_Program_Name",

SUBSTR(B.USER_NAME,1,15) "Last_Updated_By",

SUBSTR(B.DESCRIPTION,1,25) DESCRIPTION

FROM APPS.FND_CONCURRENT_PROGRAMS_VL A, APPLSYS.FND_USER B

WHERE A.ENABLE_TRACE='Y'

AND A.LAST_UPDATED_BY=B.USER_ID;

***Concurrent Program count under QUEUE ***

col  "program name" format a55;

col "name" format  a17;

col "queue name" format a15

col "statuscode" format a3

select user_CONCURRENT_PROGRAM_NAME "PROGRAM NAME",concurrent_queue_name "QUEUE NAME", priority,decode(phase_code,'P','Pending') "PHASE", 

decode(status_code,'A','Waiting','B','Resuming','C','Normal','D','Cancelled','E','Error','F',

'Scheduled','G','Warning','H','On Hold','I','Normal','M','No Manager','Q','Standby','R','Normal','S',

'Suspended','T','Terminating','U','Disabled','W','Paused','X','Terminated','Z','Waiting') " 

NAME", status_code,count(*) from 

fnd_concurrent_worker_requests 

where  phase_code='P' and hold_flag!='Y' 

and requested_start_date<=sysdate

and concurrent_queue_name<> 'FNDCRM'

and concurrent_queue_name<> 'GEMSPS'

group by 

user_CONCURRENT_PROGRAM_NAME,

concurrent_queue_name,priority,phase_code,status_code

order by count(*) desc

/

***Lists the Manager Names with the No. of Requests in Pending/Running ***

col "USER_CONCURRENT_QUEUE_NAME" format a40;

SELECT a.USER_CONCURRENT_QUEUE_NAME,a.MAX_PROCESSES,

sum(decode(b.PHASE_CODE,'P',decode(b.STATUS_CODE,'Q',1,0),0)) Pending_Standby,

sum(decode(b.PHASE_CODE,'P',decode(b.STATUS_CODE,'I',1,0),0)) Pending_Normal,

sum(decode(b.PHASE_CODE,'R',decode(b.STATUS_CODE,'R',1,0),0)) Running_Normal

FROM FND_CONCURRENT_QUEUES_VL a, FND_CONCURRENT_WORKER_REQUESTS b

where a.concurrent_queue_id = b.concurrent_queue_id

AND b.Requested_Start_Date<=SYSDATE

GROUP BY a.USER_CONCURRENT_QUEUE_NAME,a.MAX_PROCESSES;

*** Concurrent QUEUE Details *** 

set echo off

set linesize 130

set serveroutput on size 50000

set feed off

set veri off

DECLARE

running_count NUMBER := 0;

pending_count NUMBER := 0;

crm_pend_count NUMBER := 0;

--get the list of all conc managers and max worker and running workers

CURSOR conc_que IS

SELECT concurrent_queue_id,

concurrent_queue_name,

user_concurrent_queue_name, 

max_processes,

running_processes

FROM apps.fnd_concurrent_queues_vl

WHERE enabled_flag='Y' and 

concurrent_queue_name not like 'XDP%' and 

concurrent_queue_name not like 'IEU%' and 

concurrent_queue_name not in ('ARTAXMGR','PASMGR') ;

BEGIN

DBMS_OUTPUT.PUT_LINE('====================================================================================================');

DBMS_OUTPUT.PUT_LINE('QueueID'||' '||'Queue          '||

'Concurrent Queue Name              '||' '||'MAX '||' '||'RUN '||' '||

'Running '||' '||'Pending   '||' '||'In CRM');

DBMS_OUTPUT.PUT_LINE('====================================================================================================');

FOR i IN conc_que 

LOOP

--for each manager get the number of pending and running requests in each queue

SELECT /*+ RULE */ nvl(sum(decode(phase_code, 'R', 1, 0)), 0), 

nvl(sum(decode(phase_code, 'P', 1, 0)), 0)

INTO running_count, pending_count

FROM fnd_concurrent_worker_requests

WHERE

requested_start_date <= sysdate

and concurrent_queue_id = i.concurrent_queue_id

AND hold_flag != 'Y'; 

--for each manager get the list of requests pending due to conflicts in each manager

SELECT /*+ RULE */ count(1)

INTO crm_pend_count

FROM apps.fnd_concurrent_worker_requests a

WHERE concurrent_queue_id = 4

AND hold_flag != 'Y'

AND requested_start_date <= sysdate

AND exists (

SELECT 'x' 

FROM apps.fnd_concurrent_worker_requests b

WHERE a.request_id=b.request_id

and concurrent_queue_id = i.concurrent_queue_id

AND hold_flag != 'Y'

AND requested_start_date <= sysdate);

--print the output by joining the outputs of manager counts,  

DBMS_OUTPUT.PUT_LINE(

rpad(i.concurrent_queue_id,8,'_')||

rpad(i.concurrent_queue_name,15, ' ')||

rpad(i.user_concurrent_queue_name,40,' ')||

rpad(i.max_processes,6,' ')||

rpad(i.running_processes,6,' ')||

rpad(running_count,10,' ')||

rpad(pending_count,10,' ')||

rpad(crm_pend_count,10,' '));

--DBMS_OUTPUT.PUT_LINE('----------------------------------------------------------------------------------------------------');

END LOOP;

DBMS_OUTPUT.PUT_LINE('====================================================================================================');

END;

/

set verify on

set echo on