DB Monitoring & Performance Script

DB Monitoring & Performance Script

The Monitoring of predefined events that generates a message or warning when a certain threshold has been exceeded. This is done in an effort to ensure that an issue doesn't become a problem. The database monitoring is required for the following reason:

–        Smooth running of production

–        Keeping an eye on development

–        Database performance

–        In Support of an SLA (service level agreement)

Types of DB Monitoring

1. Status
2. Performance
3. Trend Analysis

Status Monitoring:

Monitor the current status of an event and reports when it exceeds a defined threshold.

Database:

–        Database/Listener

–        Monitor Alert. log Message on regular basis.

–        Check all last night backup is successful.

–        Tablespace/Datafiles full or Fragmented.

–        Identify bad growth of segment.

–        Identify at least 1 top resource consuming query

–        Monitor Locking

–        Check Maximum Extent about to be reached.

–        Redo log Tracking

–        UNDO and Temp Segment Free space.

–        Monitor Running Job

–        Tracking DB User/Session Information.

–        Important Object Information

OS:

–        SGA/PGA information

–        CPU Usage Information

–        Memory Utilization

–        Disk Utilization

Performance Monitoring:

Monitor a defined set of performance statistics. This is done in an effort to maintain the best possible DB performance.

Trend Analysis Monitoring:

Collect the historical data for specified events and analyze these data on schedule basis to reveal any potential problems. For Example watching growth of data in a tablespace and predicting when it will fill.

Apart from the above checklist some of the other checklist a DBA are using. It is depend on the requirement. I am mentioning here some of the related query and scripts. It is fully related to DB Monitoring Purpose.
Note: Keep every one informed specially your senior or Junior DBA, System Admin, Manager and do not forget to document very important update.

Database Information:

******************************************************************************************************************************************************************
Track OS Reboot Time:
net statistics server
systeminfo | find "Up Time"  -- to find system last uptime
systeminfo | find "System Boot Time"  -- to find system boot time
net statistics workstation | find "Statistics" Workstation Statistics for \\A5541TAG-WKS   --perticular workstation statistics
Database and Instance Last start time:
SELECT to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time"
FROM   sys.v_$instance;
SELECT SYSDATE-logon_time "Days", (SYSDATE-logon_time)*24 "Hours"
from  sys.v_$session where  sid=1;
Track Database Version:
SELECT * from v$version;
Track Database Name and ID information:
SELECT DBID, NAME FROM V$DATABASE;‎
Track Database Global Name information:
SELECT * FROM GLOBAL_NAME;‎
Track Database Instance name:
SELECT INSTANCE_NAME FROM V$INSTANCE;‎
Track Database Host Details:
SELECT UTL_INADDR.GET_HOST_ADDRESS, UTL_INADDR.GET_HOST_NAME FROM DUAL;
Track Database Present Status:
SELECT created, RESETLOGS_TIME, Log_mode FROM V$DATABASE;
DB Character Set Information:
Select * from nls_database_parameters;
Track Database default information:
Select username, profile, default_tablespace, temporary_tablespace from dba_users;
Track Total Size of Database:
select a.data_size+b.temp_size+c.redo_size "Total_Size (GB)"
from ( select sum(bytes/1024/1024/1024) data_size
         from dba_data_files ) a, ( select nvl(sum(bytes/1024/1024/1024),0) temp_size
         from dba_temp_files ) b, ( select sum(bytes/1024/1024/1024) redo_size
         from sys.v_$log ) c;
Total Size of Database with free space:
Select round(sum(used.bytes) / 1024 / 1024/1024 ) || ' GB' "Database Size",round(free.p / 1024 / 1024/1024) || ' GB' "Free space"
from (select bytes from v$datafile
      union all
      select bytes from v$tempfile
      union all
      select bytes from v$log) used, (select sum(bytes) as p from dba_free_space) free group by free.p;
Track Database Structure:
select name from   sys.v_$controlfile
/
select group#,member from   sys.v_$logfile
/
Select F.file_id Id, F.file_name name, F.bytes/(1024*1024) Mbyte,
       decode(F.status,'AVAILABLE','OK',F.status) status, F.tablespace_name Tspace
from   sys.dba_data_files F
order by tablespace_name;

Tablespace/Datafile/Temp/UNDO Information:
******************************************************************************************************************************************************************
Track Tablespace Used/Free Space:
SELECT /* + RULE */  df.tablespace_name "Tablespace",  df.bytes / (1024 * 1024) "Size (MB)",
       SUM(fs.bytes) / (1024 * 1024) "Free (MB)", Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free", Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
  FROM dba_free_space fs, (SELECT tablespace_name,SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
       fs.bytes / (1024 * 1024), SUM(df.bytes_free) / (1024 * 1024), Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1), Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
  FROM dba_temp_files fs, (SELECT tablespace_name,bytes_free,bytes_used
          FROM v$temp_space_header
         GROUP BY tablespace_name,bytes_free,bytes_used) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
 ORDER BY 4 DESC;
Track all Tablespaces with free space < 10%
Select a.tablespace_name,sum(a.tots/1048576) Tot_Size, sum(a.sumb/1024) Tot_Free, sum(a.sumb)*100/sum(a.tots) Pct_Free, ceil((((sum(a.tots) * 15) - (sum(a.sumb)*100))/85 )/1048576) Min_Add
from (select tablespace_name,0 tots,sum(bytes) sumb
from dba_free_space a
group by tablespace_name
union
Select tablespace_name,sum(bytes) tots,0 from dba_data_files
group by tablespace_name) a group by a.tablespace_name
having sum(a.sumb)*100/sum(a.tots) < 10
order by pct_free;
Track Tablespace Fragmentation Details:
Select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
     sum(a.sumb/1048576) Tot_Free, sum(a.sumb)*100/sum(a.tots) Pct_Free,
     sum(a.largest/1024) Max_Free,sum(a.chunks) Chunks_Free
     from  ( select tablespace_name,0 tots,sum(bytes) sumb,
     max(bytes) largest,count(*) chunks
     from dba_free_space a
     group by tablespace_name
     union
     select tablespace_name,sum(bytes) tots,0,0,0 from dba_data_files
     group by tablespace_name) a  group by a.tablespace_name
order by pct_free;
Track Non-Sys owned tables in SYSTEM Tablespace:
SELECT owner, table_name, tablespace_name FROM dba_tables WHERE tablespace_name = 'SYSTEM' AND owner NOT IN ('SYSTEM', 'SYS', 'OUTLN');
Track Default and Temporary Tablespace:
SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME like '%DEFAULT%';
select username,temporary_tablespace,default_tablespace from dba_users where username='HRMS';  --for Particular User
Select default_tablespace,temporary_tablespace,username from dba_users;   --for All Users
Track DB datafile used and free space:
SELECT SUBSTR (df.NAME, 1, 40) file_name,dfs.tablespace_name, df.bytes / 1024 / 1024 allocated_mb, ((df.bytes / 1024 / 1024) -  NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes,dfs.tablespace_name
ORDER BY file_name;
Track Datafile with Archive Details:
SELECT NAME, a.status, DECODE (b.status, 'Active', 'Backup', 'Normal') arc, enabled, bytes, change#, TIME ARCHIVE FROM sys.v_$datafile a, sys.v_$backup b WHERE a.file# = b.file#;
Track Datafiles with highest I/O activity:
Select * from (select name,phyrds, phywrts,readtim,writetim
from v$filestat a, v$datafile b
where a.file#=b.file#
order by readtim desc) where rownum <6 br="">Track Datafile as per the Physical Read/Write Percentage:
WITH totreadwrite AS (SELECT SUM (phyrds) phys_reads, SUM (phywrts) phys_wrts FROM v$filestat)
SELECT   NAME, phyrds, phyrds * 100 / trw.phys_reads read_pct, phywrts, phywrts * 100 / trw.phys_wrts write_pct FROM totreadwrite trw, v$datafile df, v$filestat fs WHERE df.file# = fs.file# ORDER BY phyrds DESC;
Checking  Autoextend ON/OFF for Datafile:
select substr(file_name,1,50), AUTOEXTENSIBLE from dba_data_files
‎select tablespace_name,AUTOEXTENSIBLE from dba_data_files;
More on Tablespace/Datafile size click on the link: DB Tablespace/Datafile Details
Temp Segment:
Track Temp Segment Free space:
SELECT tablespace_name, SUM(bytes_used/1024/1024) USED, SUM(bytes_free/1024/1024) FREE
FROM   V$temp_space_header
GROUP  BY tablespace_name;
SELECT   A.tablespace_name tablespace, D.mb_total,
         SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
         D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM  v$sort_segment A, (SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
         FROM     v$tablespace B, v$tempfile C
         WHERE    B.ts#= C.ts#
         GROUP BY B.name, C.block_size ) D
WHERE    A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
Track Who is Currently using the Temp:
SELECT b.tablespace, ROUND(((b.blocks*p.value)/1024/1024),2)||'M' "SIZE",
a.sid||','||a.serial# SID_SERIAL, a.username, a.program
FROM sys.v_$session a, sys.v_$sort_usage b, sys.v_$parameter p
WHERE p.name  = 'db_block_size' AND a.saddr = b.session_addr
ORDER BY b.tablespace, b.blocks;
Undo & Rollback Segment:
Monitor UNDO information:
select to_char(begin_time,'hh24:mi:ss'),to_char(end_time,'hh24:mi:ss'), maxquerylen,ssolderrcnt,nospaceerrcnt,undoblks,txncount from v$undostat
order by undoblks;
Track Active Rollback Segment:
SELECT   r.NAME, l.sid, p.spid, NVL (p.username, 'no transaction') "Transaction",
p.terminal "Terminal" FROM v$lock l, v$process p, v$rollname r
WHERE l.sid = p.pid(+) AND TRUNC (l.id1(+) / 65536) = r.usn AND l.TYPE(+) = 'TX' AND l.lmode(+) = 6 ORDER BY R.NAME;
Track Currently Who is using UNDO and TEMP:
SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial,
 NVL(s.username, 'None') orauser, s.program, r.name undoseg,
t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"
FROM sys.v_$rollname    r, sys.v_$session s, sys.v_$transaction t, sys.v_$parameter   x
 WHERE s.taddr = t.addr AND r.usn   = t.xidusn(+) AND x.name  = 'db_block_size';

Redolog Information:
******************************************************************************************************************************************************************
Track Redo Generation by Calender Year:
select to_char(first_time,'mm.DD.rrrr') day,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23"
from v$log_history group by to_char(first_time,'mm.DD.rrrr')
order by day;
Track Redo generation by day:
select trunc(completion_time) logdate, count(*) logswitch, round((sum(blocks*block_size)/1024/1024)) "REDO PER DAY (MB)" from v$archived_log
group by trunc(completion_time) order by 1;
Track How much full is the current redo log file:
SELECT le.leseq   "Current log sequence No", 100*cp.cpodr_bno/le.lesiz "Percent Full",
 cp.cpodr_bno   "Current Block No", le.lesiz   "Size of Log in Blocks"
FROM x$kcccp cp, x$kccle le
WHERE le.leseq =CP.cpodr_seq
AND bitand(le.leflg,24) = 8;

Monitor Running Jobs:
******************************************************************************************************************************************************************
Long Jobs:
Select username,to_char(start_time, 'hh24:mi:ss dd/mm/yy') started, time_remaining remaining, message
from v$session_longops
where time_remaining = 0 order by time_remaining desc;
Monitor Long running Job:
SELECT SID, SERIAL#, opname, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) COMPLETE
FROM   V$SESSION_LONGOPS
WHERE TOTALWORK != 0 AND SOFAR != TOTALWORK order by 1;
Track Long Query Progress in database:
SELECT a.sid, a.serial#, b.username , opname OPERATION, target OBJECT,
TRUNC(elapsed_seconds, 5) "ET (s)", TO_CHAR(start_time, 'HH24:MI:SS') start_time,
ROUND((sofar/totalwork)*100, 2) "COMPLETE (%)"
FROM v$session_longops a, v$session b
WHERE a.sid = b.sid AND b.username not IN ('SYS', 'SYSTEM') AND totalwork > 0
ORDER BY elapsed_seconds;
Track Running RMAN backup status:
SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'  AND OPNAME NOT LIKE '%aggregate%'
  AND TOTALWORK != 0 AND SOFAR  != TOTALWORK;
Monitor Import Rate:
Oracle Import Utility usually takes hours for very large tables and we need to track the execution of Oracle Import Process. Below option can help you monitor the rate at which rows are being imported from a running import job.
select   substr(sql_text,instr(sql_text,'into "'),30) table_name,
   rows_processed, round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
   trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_minute
from   sys.v_$sqlarea
where   sql_text like 'insert %into "%' and command_type = 2 and open_versions > 0;

Database SGA Report:
******************************************************************************************************************************************************************
Monitor SGA Information:
SELECT SUM(VALUE)/1024/1024 "Size in MB" from SYS.v_$sga;
select     NAME,   BYTES from     v$sgastat  order by NAME;
Monitor Shared Pool Information:
select to_number(value) shared_pool_size, sum_obj_size, sum_sql_size, sum_user_size,
(sum_obj_size + sum_sql_size+sum_user_size)* 1.3 min_shared_pool
  from (select sum(sharable_mem) sum_obj_size
  from v$db_object_cache where type <> 'CURSOR'),
 (select sum(sharable_mem) sum_sql_size from v$sqlarea),
 (select sum(250 * users_opening) sum_user_size from v$sqlarea), v$parameter
 where name = 'shared_pool_size';
Monitor PGA Information:
Select st.sid "SID", sn.name "TYPE", ceil(st.value / 1024 / 1024/1024) "GB"
from v$sesstat st, v$statname sn where st.statistic# = sn.statistic#
and sid in (select sid from v$session where username like UPPER('hrms'))
and upper(sn.name) like '%PGA%' order by st.sid, st.value desc;
Monitor CPU Usage Information:
select  ss.username, se.SID, VALUE/100 cpu_usage_seconds
from v$session ss,  v$sesstat se,  v$statname sn where se.STATISTIC# = sn.STATISTIC#
and NAME like '%CPU used by this session%' and se.SID = ss.SID
and  ss.status='ACTIVE' and  ss.username is not null order by VALUE desc;
Disk I/O Report:
WITH totreadwrite AS (SELECT SUM (phyrds) phys_reads, SUM (phywrts) phys_wrts FROM v$filestat)
SELECT   NAME, phyrds, phyrds * 100 / trw.phys_reads read_pct,
    phywrts, phywrts * 100 / trw.phys_wrts write_pct
 FROM totreadwrite trw, v$datafile df, v$filestat fs
   WHERE df.file# = fs.file# ORDER BY phyrds DESC;
IO Usage for a Query:
select b.sql_text "Statement ", a.Disk_reads "Disk Reads", a.executions "Executions",
a.disk_reads/decode(a.executions,0,1,a.executions) "Ratio",c.username
from  v$sqlarea a, v$sqltext_with_newlines b,dba_users c
where  a.parsing_user_id = c.user_id and a.address=b.address and a.disk_reads>100000
order by a.disk_reads desc,b.piece;
Display the System write batch size:
SELECT kviival write_batch_size
  FROM x$kvii
 WHERE kviidsc = 'DB writer IO clump' OR kviitag = 'kcbswc'
Monitor Disk I/O Contention:
select   NAME,  PHYRDS "Physical Reads",
    round((PHYRDS / PD.PHYS_READS)*100,2) "Read %",   PHYWRTS "Physical Writes",
    round(PHYWRTS * 100 / PD.PHYS_WRTS,2) "Write %",   fs.PHYBLKRD+FS.PHYBLKWRT "Total Block I/O's" from (    select     sum(PHYRDS) PHYS_READS, sum(PHYWRTS) PHYS_WRTS
    from    v$filestat    ) pd,  v$datafile df,  v$filestat fs
where     df.FILE# = fs.FILE#
order     by fs.PHYBLKRD+fs.PHYBLKWRT desc;

DB Locks/Blocks/Blocker Details:
******************************************************************************************************************************************************************
Track Block session in oracle 9i/10g  
‎select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid ||  ' )  is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' ||  s2.sid || ' ) ' AS blocking_status from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2 where s1.sid = l1.sid and s2.sid = l2.sid  and l1.BLOCK = 1  and l2.request > 0  and l1.id1 = l2.id1  and l2.id2 = l2.id2;
select do.object_name, row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
dbms_rowid.rowid_create(1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#)
from gv$session s, dba_objects do
where sid = 543 and s.ROW_WAIT_OBJ# = do.OBJECT_ID;
For detail description of blocking you can run this on your Oracle-Home
oracle-home\rdbms\admin\utllockt.sql
Select process,sid, blocking_session from v$session where blocking_session is not null;  --in 10g
Track Locked Session & Blocked:
PROMPT Blocked and Blocker Sessions
select /*+ ORDERED */ blocker.sid blocker_sid, blocked.sid blocked_sid ,
TRUNC(blocked.ctime/60) min_blocked, blocked.request
from (select *from v$lock
where block != 0 and type = 'TX') blocker, v$lock blocked
where blocked.type='TX' and blocked.block = 0 and blocked.id1 = blocker.id1;
Track Database Lock:
Select /*+ ORDERED */ l.sid, l.lmode,
TRUNC(l.ctime/60) min_blocked, u.name||'.'||o.NAME blocked_obj
from (select * from v$lock
where type='TM' and sid in (select sid
from v$lock where block!=0)) l, sys.obj$ o, sys.user$ u
where o.obj# = l.ID1 and o.OWNER# = u.user#;
Track the Session Waiting for Lock:
SELECT holding_session bsession_id, waiting_session wsession_id, b.username busername, a.username wusername, c.lock_type TYPE, mode_held, mode_requested, lock_id1, lock_id2
FROM sys.v_$session b, sys.dba_waiters c, sys.v_$session a
WHERE c.holding_session = b.sid AND c.waiting_session = a.sid;
Track Blocker Details:
SELECT sid, serial#, username, osuser, machine
FROM v$session
WHERE sid IN (select sid from v$lock
where block != 0 and type = 'TX');


Users/Sessions/Processes Details:
******************************************************************************************************************************************************************
Average Wait Time for Particular Event:
SELECT EVENT,  TOTAL_WAITS,  TOTAL_TIMEOUTS,  TIME_WAITED, round(AVERAGE_WAIT,2) "Average Wait"
 from v$system_event order    by TOTAL_WAITS;
Sessions Waiting On A Particular Wait Event:
SELECT count(*), event
FROM v$session_wait
WHERE wait_time = 0 AND event NOT IN ('smon timer','pipe get','wakeup time manager', 'pmon timer','rdbms ipc message', 'SQL*Net message from client')
GROUP BY event ORDER BY 1 DESC;
Track Logon time of DB user and OS user:
Select to_char(logon_time,'dd/mm/yyyy hh24:mi:ss'),osuser,status,schemaname,machine from v$session where type !='BACKGROUND'; ‎
Track all Session User Details:
select sid, serial#,machine, status, osuser,username from v$session where username!='NULL';
Track Active Session User Details:
SELECT SID, Serial#, UserName, Status, SchemaName, Logon_Time FROM V$Session WHERE Status= 'ACTIVE' AND UserName IS NOT NULL;
Track Active User Details:
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';
Report OS Process ID for each session:
SELECT    ses.username  || '('  || ses.sid  || ')' users, acc.owner owner, acc.OBJECT OBJECT, ses.lockwait, prc.spid os_process
  FROM v$process prc, v$access acc, v$session ses
 WHERE prc.addr = ses.paddr AND ses.sid = acc.sid;
Show Username and SID/SPID with Program Name:
select sid,name,value from v$spparameter where isspecified='TRUE';‎
SELECT SID, Serial#, UserName, Status, SchemaName, Logon_Time FROM V$Session
WHERE Status= 'ACTIVE' AND UserName IS NOT NULL;  --to find active session
SELECT s.inst_id,  s.sid,  s.serial#,  p.spid,  s.username,  s.program    --active users details
FROM gv$session s  JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND';
Track Current Transaction in Database:
‎‎select a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk  from v$session a, v$transaction b
where a.saddr = b.ses_addr;‎

Important Object Information:
******************************************************************************************************************************************************************
Database Object Information:
Select owner,object_type,count(*) from dba_objects Where owner not IN ('SYS','MDSYS','CTXSYS','HR','ORDSYS','OE','ODM_MTR','WMSYS','XDB','QS_WS', 'RMAN','SCOTT','QS_ADM','QS_CBADM', 'ORDSYS','OUTLN','PM','QS_OS','QS_ES','ODM','OLAPSYS','WKSYS','SH','SYSTEM','ORDPLUGINS','QS','QS_CS')
Group by owner,object_type order by owner;
Query to Find 5 largest object in Database:
SELECT * FROM (select SEGMENT_NAME, SEGMENT_TYPE, BYTES/1024/1024/1024 GB, TABLESPACE_NAME from dba_segments order by 3 desc ) WHERE ROWNUM <= 5;
Track Last DDL Performed in database:
Select CREATED, TIMESTAMP, last_ddl_time from all_objects WHERE OWNER='HRMS' AND OBJECT_TYPE='TABLE' order by timestamp desc;
Count Invalid Object:
Select owner, object_type, count(*) from dba_objects where status='INVALID' group by  owner, object_type;
Report all Invalid Object in Database:
SELECT owner, object_name, object_type,‎ TO_CHAR (last_ddl_time, 'DD-MON-YY hh:mi:ss') last_time FROM dba_objects‎ WHERE status = 'INVALID';
Report Invalid Object with Next Action:
select 'Alter ' || decode(object_type,'PACKAGE BODY','PACKAGE',object_type) || ' ' || object_name || ' compile ' || decode(object_type,'PACKAGE BODY',' body;',';') from user_objects where object_type in ('FUNCTION','PACKAGE','PACKAGE BODY','PROCEDURE','TRIGGER','VIEW') and status = 'INVALID' order by object_type , object_name;
Click on the link to Report Invalid object and How to Compile them: Report All Invalid Objects

Track Total Number of Table/Index/Mviews:

Select count(1) from user_tables where table_name not like '%$%'
Select count(1) from user_mviews;
Select count(1) from user_indexes where index_type in ('FUNCTION-BASED NORMAL','NORMAL');
Number of Objects Created in last week:
Select count(1) from user_objects where CREATED >= sysdate - 7
Track Mviews Not Refreshed since last Week:
Select mview_name from user_mviews where LAST_REFRESH_DATE < sysdate - 7;

Error : workflow background engines is down

schedule the concurrent request WorkFlow Background Process as follows

WorkFlow Development recommends that users run the he Workflow Background Engine with these basic settings:
==========================================================================================

A. Schedule one concurrent request to process deferred activities:

1. Item Type :
2. Min Threshold :
3. Max Threshold :
4. Process Deferred : Yes
5. Process Timeout : No
6. Process Stuck : No

B. Schedule one concurrent request to process timeout activities:

1. Item Type :
2. Min Threshold :
3. Max Threshold :
4. Process Deferred : No
5. Process Timeout : Yes
6. Process Stuck : No

C. Run for stuck activities periodically (monthly or weekly):


1. Item Type :
2. Min Threshold :
3. Max Threshold :
4. Process Deferred : No
5. Process Timeout : No
6. Process Stuck : Yes

Instructions to run the request.

1. Log into the Application as System Administrator Responsibility.

2. Navigation: Concurrent -> Request -> View Concurrent Request

3. Query WorkFlow Background Process in the Name field,
and verify it has run and how it was scheduled.


How do you start the Workflow Background Process if it is not running?

1. Sign onto the Oracle Application using the System Administrator 
Responsibility.

2. Navigation: Concurrent -> Request -> (B) Run, click OK for Single Request.

3. Open the Submit Request form. Click on the Pick List and choose 
Workflow Background Process

4. In the parameters window, enter the following parameters then click OK:

a. Item Type: 
If you want to restrict workflow engine to a specific Item Type, 
specify the same here. Else, the engine will process all/any 
deferred activity regardless of the Item Type.

b. Minimum Threshold: 
If you want to restrict this engine to activities with specific minimum 
cost, define it here. Otherwise the Workflow engine will process any 
deferred activity regardless of cost.

c. Maximum Threshold: 
If you want to restrict this engine to activies with specific maximum 
cost, define it here. Otherwise the workflow engine will process any 
deferred activities regardless of cost.

d. Process Deferred:
Specify whether the workflow engine should run deferred. Set it to Yes or
No. If this set to yes, the Workflow Background Process will run the 
workflow activities as a deferred process.

e. Process Time out: 
Specify whether the workflow engine should check for activities that have 
been timed out. Set it to Yes or No.

5. When you have finished the above steps, define Scheduling for the background 
process engine (as per the specific business needs) and click the submit 
button.

Issues before and after 12.1.1 Installation on OEL 5.8

Issues before and after 12.1.1 Installation on OEL 5.8

Some of the issues which you might face during EBS 12.1.1 installation on OEL 5.8 are listed below:

Pre-Installation Issues:

On OEL 5.8, ideally you won't face any pre-installation issues apart from RPM issues like missing RPMs in OEL DVD, Order of RPM Installation, Simultaneous installation of couple of RPMs etc

The order of RPMs which I recommend you is as below:

·         openmotif21-2.1.30-11.EL5.i3861

·         xorg-x11-libs-compat-6.8.2-1.EL.33.0.1.i386

Download the above 2 RPMs from the below URL:

http://oss.oracle.com/projects/compat-oracle/files/Enterprise_Linux

Similarly install the following RPMs (Available in OEL DVD):

·         compat-glibc-2.3.4-2.26

·         gcc-4.1.2-14.el5

·         gcc-c++-4.1.2-14.el5

·         glibc-common-2.5-123

·         glibc-devel-2.5-12

·         libgcc-4.1.2-14.el53

·         libstdc++-devel-4.1.2-14.el5

·         libstdc++-4.1.2-14.el53

·         make-3.81-1.13

·         gdbm-1.8.0-26.2.13

·         libXp-1.0.0-8.1.el5

·         libaio-0.3.106-3.23

·         libgomp-4.1.2-14.el5

·         sysstat-7.0.0-3.el5

·         compat-libstdc++-296-2.96-138

·         compat-libstdc++-33-3.2.3-61

The following RPMs are required for 11gR1 Database. As 12.1.1 comes with 11.1.0.7 Database, install the below RPMs as well:

·         elfutils-libelf-devel-0.125

·         elfutils-libelf-devel-static-0.125

Install the above 2 RPMs in the same command.

·         libaio-devel-0.3.106

·         unixODBC-libs-2.2.11-10.el5.i386.rpm

·         unixODBC-2.2.11

·         unixODBC-devel-2.2.11kernel-headers-2.6

Most of the above are already generally installed with OS Installation.

Post-Installation Issues:

When installating 12.1.1 on OEL5 its common to see some of the Post-Installation checks fail as seen in the screenshot due to a library issue which can be resolved by following the below steps:

1. Stop Applications services by using adstpall.sh

2. Execute the following command as root:

ln -s /usr/lib/libgdbm.so.2.0.0 /usr/lib/libdb.so.2

3. Start the Applications Services by using adstrtal.sh

4. Click on Retry button in Rapid Install GUI. You must now see all Post-Install checks succeeding.

Removing Credentials from a Cloned EBS Production Database

Removing Credentials from a Cloned EBS Production Database

  Step 1 - Clear All Credentials
  Step 2 - Re-establish Bootstrap Credentials
  Step 3 - Prepare Scripts for Setting Additional Passwords
  Step 4 - Assign New Passwords to All Schemas Not Managed with EBS
  Step 5 - Assign New Passwords to All Schemas Managed with EBS
  Additional Steps
  Running AutoConfig

References
Applies to:

Oracle Applications Manager - Version 11.5.9 to 12 [Release 11.5 to 1.2]
Information in this document applies to any platform.
Abstract

When cloning a Production database in Oracle E-Business Suite (EBS) it is a best practice to remove all Production account credentials in the cloned copy of the database. This will help to prevent retrieval of Production credentials, which could be used to compromise the security and integrity of the Production database.

It is ideal to complete this process as soon as possible after the database data files have been copied. At a minimum it should be completed before the database is turned over to any party less trusted than the Production database DBA team.

This document describes the steps required to remove the Production EBS database credentials, such as database user (schema) password hashes and encrypted passwords. Additionally information is provided about how to reestablish credentials in the cloned copy so that the clone may be used for functional, performance or patch application testing.

Steps from this paper should be incorporated into your database cloning process and procedures.

History

Author :
Create Date 14-Mar-2007
Update Date 11-JUL-2011
Expire Date

Details

The steps outlined in this White Paper will:
Help to ensure that Production credentials are not retrievable from a cloned copy of an EBS Production database.
Boot strap the cloned copy with enough "clone credentials" that it may be used for testing.
The steps in this document should be integrated in your database cloning process, see the "Reference" section below for documentation on cloning EBS systems for Releases 11i and 12.

The following sequence of steps will remove production account credentials from the cloned copy of the production database and reestablish new credentials in the cloned copy. All of the new accounts on the clone target will have the password "clone".

Step 1 - Clear all credentials
Step 2 - Re-establish basic accounts (for runtime: SYS,SYSTEM,APPLSYSPUB,APPLSYS,APPS + GUEST,SYSADMIN)
Step 3 - Prepare scripts for setting additional passwords
Step 4 - Assign new passwords to all database users not managed with EBS
Step 5 - Assign new passwords to all database users managed with EBS
Optional additional steps
Steps 1 through 4 are run on the database server running as the Operating System user, "oracle", using "sqlplus" connected as the "SYS" or "APPS" database user. Step 5 is run as the Operating System user "applmgr" on an application tier and uses the "FNDCPASS" command line utility. This means that steps 1 through 4 can be performed the first time the cloned database is started, i.e. before it is made accessible to the network via the database TNS listener. Step 5 is not time critical and can be performed when access to the cloned system for patch purposes is required.

All application tier processes must be stopped during this procedure.

Step 1 - Clear All Credentials

To clear all credentials on a target clone of a production database you must establish a shell environment with sufficient Oracle environment variables to successfully start "sqlplus" via the "BEQ" (bequeth) driver. If Rapid Clone has been completed successfully, then each Oracle Home should have a .env file. However, in the event you need to set the environment manually, here are the minimal environment settings: 

$ export ORACLE_SID=
$ export ORACLE_HOME=
$ export PATH=$ORACLE_HOME/bin
$ unset TWO_TASK

oracle$ sqlplus '/ as sysdba'

To clear all credentials in the cloned copy of a Production database, create and execute the following 3 SQL scripts:

REM --- step1.sql
spool  step1.lst

REM Start the database clone for the first time
startup restrict

REM Clear all production credentials from the cloned database

update SYS.user$ set
 password = translate(password,'0123456789ABCDEF','0000000000000000')
 where type#=1 and length(password) = 16
/
update APPLSYS.FND_ORACLE_USERID set
 ENCRYPTED_ORACLE_PASSWORD='INVALID'
/

update APPLSYS.FND_USER set
 ENCRYPTED_FOUNDATION_PASSWORD='INVALID',
 ENCRYPTED_USER_PASSWORD='INVALID'
/
commit;

REM Shutdown the database
shutdown
exit

REM end of script
At this point, the cloned copy of the database is free from Production credentials. The database was shut down by the script in order for the unusual way of clearing the database user (schema) passwords to take effect. You will need to restart the cloned copy of the database in preparation for steps 2, 3 and 4:

oracle$ echo startup | sqlplus '/ as sysdba'
Step 2 - Re-establish Bootstrap Credentials

The database at the moment has no credentials. Now log on as "SYS" with operation system authentication. This will allow you to establish new credentials.

oracle$ sqlplus '/ as sysdba'
Here is the script for step 2, including inline comments which explains what is done.

REM --- step2.sql
spool step2.lst

REM Set a new password for a few initial database users

alter user SYS identified by CLONE;
alter user SYSTEM identified by CLONE;
alter user APPLSYSPUB identified by CLONE;
alter user APPLSYS identified by CLONE;
alter user APPS identified by CLONE;

REM Provide boot-strap info for FNDCPASS...
update APPLSYS.FND_ORACLE_USERID set
 ENCRYPTED_ORACLE_PASSWORD='CLONE'
 where ORACLE_USERNAME = 'APPLSYSPUB'
/

update APPLSYS.FND_ORACLE_USERID set
 ENCRYPTED_ORACLE_PASSWORD='ZG' ||
 'B27F16B88242CE980EF07605EF528F9391899B09552FD89FD' ||
 'FF43E4DDFCE3972322A41FBB4DDC26DDA46A446582307D412'
 where ORACLE_USERNAME = 'APPLSYS'
/

update APPLSYS.FND_ORACLE_USERID set
 ENCRYPTED_ORACLE_PASSWORD='ZG' ||
 '6CC0BB082FF7E0078859960E852F8D123C487C024C825C0F9' ||
 'B1D0863422026EA41A6B2B5702E2299B4AC19E6C1C23333F0'
 where ORACLE_USERNAME = 'APPS'
/
commit;

REM We run as SYS, now connect as APPS to run some plsql
connect APPS/CLONE

REM Every EBS database needs a GUEST user
select APPS.fnd_web_sec.change_guest_password( 'CLONE', 'CLONE' ) "RES"
 from dual;
commit;

REM Set GUEST credential in site level profile option
set serveroutput on
declare
 dummy boolean;
begin
 dummy := APPS.FND_PROFILE.SAVE('GUEST_USER_PWD', 'GUEST/CLONE', 'SITE');
 if not dummy then
 dbms_output.put_line( 'Error setting GUEST_USER_PWD profile' );
 end if;
end;
/
commit;

REM One more time for luck (avoid session caching of profiles)
connect APPS/CLONE

REM Set SYSADMIN password
select APPS.fnd_web_sec.change_password('SYSADMIN','CLONE') "RES"
 from dual;
commit;
exit

The expected output from step 2 is as follows:

User altered.
User altered.
User altered.
User altered.
User altered.
1 row updated.
1 row updated.
1 row updated.
Commit complete.
Connected.
RES
------
Y
Commit complete.
PL/SQL procedure successfully completed.
Commit complete.
Connected.
RES
------
Y
Commit complete.

It is important to verify that no errors are reported and that the 2 returned "RES" values are both "Y", which indicates success.


ATTENTION :

It has been identified, that some Customers running into an error for the SQL PLus command
select APPS.fnd_web_sec.change_password('SYSADMIN','CLONE') "RES" from dual;
In this case, please check Note 1350776.1 for the solution, before your are going ahead with the next steps !

Now we have completed establishing a set of bootstrap EBS credentials in the database.



Step 3 - Prepare Scripts for Setting Additional Passwords

In this step scripts are prepared to assign passwords to the other database users which were disabled in Step 1. Dynamically generated scripts are used to accomplish this because the set of database users may differ between instances of EBS. Create the script below and run it as the Operating System user "oracle":

$ sqlplus '/ as sysdba'

The comments in script below explains what is done in step 3.

REM --- step3.sql

REM Prepare SQL and SHELL scripts to set more passwords later
spool step3.lst

REM Generate a sql script to set password for db users not managed with EBS

select 'alter user "'|| USERNAME ||'" identified by CLONE; '
 from SYS.DBA_USERS
 where USERNAME not in (select ORACLE_USERNAME from APPLSYS.FND_ORACLE_USERID)
 and USERNAME not in ('SYS','SYSTEM');

REM Generate a shell script to set password for all base product schemas

select 'FNDCPASS apps/clone 0 Y system/clone ALLORACLE clone' from dual;

REM Generate a shell script to set password for non-EBS db users managed with EBS

select 'FNDCPASS apps/clone 0 Y system/clone ORACLE "' ||
 replace(ORACLE_USERNAME,'$','\$') || '" clone'
 from APPLSYS.FND_ORACLE_USERID
 where READ_ONLY_FLAG = 'X'
 and ORACLE_USERNAME in (select USERNAME from SYS.DBA_USERS);

REM Generate a shell script to set password for APPS/APPLSYS/APPM_mrc db users

select 'FNDCPASS apps/clone 0 Y system/clone SYSTEM APPLSYS clone' from dual;

REM Generate scripts for steps 4 & 5
spool off

HOST grep '^alter user ' step3.lst > dbusers4.sql
HOST grep '^FNDCPASS ' step3.lst > dbusers5.sh

exit

REM End of Script
NOTE: The script above calls the UNIX command "grep" to extract 2 sets of lines from the step3.lst spool file. If you are running Windows, the shell redirection will fail when attempted from within sqlplus. You can perform the failed step by going to a command prompt (using the HOST command from sqlplus). If you have your MKS environment set, then you can use the "grep" syntax or alternatively you can use the below syntax from a Windows command (cmd.exe) prompt.

# alternative commands for extracting sql and shell commands from step3.lst
C:\ORACLE\Clone> findstr "^alter user " step3.lst > dbusers4.sql
C:\ORACLE\Clone> findstr "^FNDCPASS " step3.lst > dbusers5.cmd
Step 4 - Assign New Passwords to All Schemas Not Managed with EBS

This Step runs the SQL script, "dbusers4.sql", generated in Step 3.

Sample content of "dbusers4.sql" listed below for illustration purposes only, you must run the one you generated on your system.


NOTE:  "dbusers4.sql", for example purposes only!
alter user "OLAPSYS" identified by CLONE;
 ...
alter user "MDSYS" identified by CLONE;
alter user "ORDPLUGINS" identified by CLONE;
alter user "ORDSYS" identified by CLONE;
alter user "DBSNMP" identified by CLONE;
alter user "OUTLN" identified by CLONE;
alter user "AD_MONITOR" identified by CLONE;
alter user "EM_MONITOR" identified by CLONE;
Note: Prior to running your script, you should review the contents of the script for any obvious problems or syntax errors- this is good advice for any dynamically-created SQL scripts.
Connect as "SYSDBA":

$ sqlplus "/ as sysdba"

Now run the "dbusers4.sql" file:
SQL> spool step4.lst
SQL> start dbusers4.sql
SQL> exit

The output spool file should show many output lines stating "User altered.". No error messages (ORA-nnnnn) should appear.

At this point, the database should be started and running. Stop and restart the database at this time. To ensure that the application tier code can access the database for  Step 5, you must also ensure that the database TNS-listener service is running.

$ echo shutdown | sqlplus "/ as sysdba"
$ echo startup | sqlplus "/ as sysdba"
$ lsnrctl start
Step 5 - Assign New Passwords to All Schemas Managed with EBS

This step uses the "FNDCPASS" command to set the passwords for all the EBS managed schemas and all the base product schemas. The "FNDCPASS" must be run from an application tier node.(Any node with an APPL_TOP file system.)

You will need to locate and copy the "dbusers5.sh" script from the directory where it was created in Step 3. Again, as with any dynamcially generated scripts that you run on your system, you should review the contents of the file before running it.

Note for Windows users: In the unlikely event that any of the usernames contain the dollar sign "$" it has been escaped by prefixing it by a backslash "\"; on Windows the backslash should be removed.

To run "FNDCPASS" you also need a number of environment variables set, at a minimum ensure that:

"FNDCPASS" is in the "$PATH" ("$ which FNDCPASS" will tell you if it is.)
The "ORACLE_HOME" environment variable points to the "Tools" ORACLE_HOME (8.0.6 on 11i, 10.1.2 on R12)
The "TWO_TASK" environment variable is set to a value that can be resolved via the "$TNS_ADMIN/tnsnames.ora file", in order to access the clone target database.
# Verify that the Oracle client environment is set to correct database (as "applmgr" OS user)

applmgr$ sqlplus -s apps/clone < select SYSDATE,NAME from v\$DATABASE;
EOF

SYSDATE NAME
--------- ---------
25-JUL-07 PRD12

applmgr$ mkdir ~/s5 ; cd ~/s5 # create new directory to hold output files
applmgr$ sh dbusers5.sh # Run the FNDCPASS shell script

The following is sample content of a "dbusers5.sh" file is listed below for illustration purposes only, run the one you generated on your system.

NOTE: This "dbusers5.sh" is for example only!

 FNDCPASS apps/clone 0 Y system/clone ALLORACLE clone
 FNDCPASS apps/clone 0 Y system/clone ORACLE "OWAPUB" clone
 FNDCPASS apps/clone 0 Y system/clone ORACLE "ODM" clone
 FNDCPASS apps/clone 0 Y system/clone ORACLE "CTXSYS" clone
 FNDCPASS apps/clone 0 Y system/clone SYSTEM APPLSYS clone
Each run of "FNDCPASS" will generate output an output/log file in the current working directory, you should review these log files (example "L2763902.log") for errors.
NOTE: If your version of  the "FNDCPASS" utility does not support the "ALLORACLE" mode, see "Q5" in the "Discussion" section below.

To verify that you have assigned passwords to all the database users, run the following query and ensure that it does not return any rows:
SQL> select USERNAME,PASSWORD from DBA_USERS where PASSWORD='0000000000000000';

This concludes the clearing and re-establishment of account credentials from a cloned database. Please see the following 2 steps "Additional Steps" and "Running Autoconfig" before attempting to use the system.
Additional Steps

What remains to be done is to set new passwords for additional applications users or the creation of new test users, depending on your needs. Changing passwords for applications users can be done using the "Define User" form (logged on as "SYSADMIN/CLONE") or by running "FNDCPASS" with the below syntax from an "applmgr" applications shell environment.

applmgr$ FNDCPASS apps/clone 0 Y system/clone USER
You may also wish to change the passwords to something other than "clone". You can use modified versions of the scripts in this note and you should reference the security best practices document for advice on changing passwords for an E-Business Suite system, see the References section below.

Running AutoConfig

Before you can actually start and access the cloned EBS system from the Application, a number of other configuration items, such as system Profile Options, most likely need to be changed in the cloned environment. Items to change typically include:

IP addresses, hostnames and port numbers
Profiles containing hostnames and port numbers
Web interface URLs
Hostnames of external services (mail, print, SSO)
The cloning notes, listed in the "Reference" section below, will provide you with information on how to run AutoConfig. Running AutoConfig is a requirement and it must be run on all tiers of the cloned system to propagate password changes and other changed settings into Autoconfig-managed files.

Prior to running AutoConfig ensure that the AutoConfig Context file contains the new "GUEST" password (Context variable "s_guest_pass") and the new password for "APPLSYSPUB" (Context variable "s_gwyuid_pass").
Password for Context Variable New Value
APPLSYSPUB s_gwyuid_pass CLONE
GUEST s_guest_pass CLONE

Index rebuilding

SELECT * FROM dba_objects WHERE object_id ='696249'; -- this query will give us the corrupt index id

select TABLE_NAME,INDEX_NAME,COLUMN_NAME,COLUMN_POSITION from dba_ind_columns
where TABLE_NAME in ('FND_CONCURRENT_REQUESTS')
order by 1,2,COLUMN_POSITION;

=====================================================================================================================
Analyze Index APPLSYS.FND_CONCURRENT_REQUESTS_N1 validate structure online; -- Idex Analyzed


select dbms_metadata.get_ddl('INDEX','FND_CONCURRENT_REQUESTS_N1','APPLSYS') from dual;

--- double click the output

CREATE INDEX "APPLSYS"."FND_CONCURRENT_REQUESTS_N1" ON "APPLSYS"."FND_CONCURRENT_REQUESTS" ("REQUESTED_BY",

"ACTUAL_COMPLETION_DATE")
  PCTFREE 0 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)
  TABLESPACE "APPS_TS_TX_IDX"


drop index APPLSYS.FND_CONCURRENT_REQUESTS_N1;  -- type commit once


CREATE INDEX "APPLSYS"."FND_CONCURRENT_REQUESTS_N1" ON "APPLSYS"."FND_CONCURRENT_REQUESTS" ("REQUESTED_BY",

"ACTUAL_COMPLETION_DATE")
  PCTFREE 0 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)
  TABLESPACE "APPS_TS_TX_IDX"
 
=====================================================================================================================



Analyze Index APPLSYS.FND_CONCURRENT_REQUESTS_N1 validate structure online; -- Idex Analyzed

select dbms_metadata.get_ddl('INDEX','FND_CONCURRENT_REQUESTS_N2','APPLSYS') from dual;

CREATE INDEX "APPLSYS"."FND_CONCURRENT_REQUESTS_N2" ON "APPLSYS"."FND_CONCURRENT_REQUESTS" ("STATUS_CODE")
  PCTFREE 0 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)
  TABLESPACE "APPS_TS_TX_IDX"

---------------------------------------------------------------------------------
Error - ORA-00054: resource busy and acquire with NOWAIT specified.
conn as sysdba
show parameter DDL_LOCK_TIMEOUT

SQL>alter system set ddl_lock_timeout = 100 ;

Session altered.

Now in the first session issue commit.
SQL> commit;

--------------------------------------------------------------------------------
Step 3

drop index APPLSYS.FND_CONCURRENT_REQUESTS_N2;

Step 4

CREATE INDEX "APPLSYS"."FND_CONCURRENT_REQUESTS_N2" ON "APPLSYS"."FND_CONCURRENT_REQUESTS" ("STATUS_CODE")
  PCTFREE 0 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)
  TABLESPACE "APPS_TS_TX_IDX"

========================================================================================================================


Analyze Index APPLSYS.FND_CONCURRENT_REQUESTS_N3 validate structure online;

select dbms_metadata.get_ddl('INDEX','FND_CONCURRENT_REQUESTS_N3','APPLSYS') from dual;

drop index APPLSYS.FND_CONCURRENT_REQUESTS_N3;

CREATE INDEX "APPLSYS"."FND_CONCURRENT_REQUESTS_N3" ON "APPLSYS"."FND_CONCURRENT_REQUESTS" ("PARENT_REQUEST_ID")
  PCTFREE 0 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)
  TABLESPACE "APPS_TS_TX_IDX"

========================================================================================================================

Analyze Index APPLSYS.FND_CONCURRENT_REQUESTS_N4 validate structure online;

select dbms_metadata.get_ddl('INDEX','FND_CONCURRENT_REQUESTS_N4','APPLSYS') from dual;

drop index APPLSYS.FND_CONCURRENT_REQUESTS_N4;

CREATE INDEX "APPLSYS"."FND_CONCURRENT_REQUESTS_N4" ON "APPLSYS"."FND_CONCURRENT_REQUESTS" ("PRIORITY_REQUEST_ID")
  PCTFREE 0 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)
  TABLESPACE "APPS_TS_TX_IDX" ;

========================================================================================================================

Analyze Index APPLSYS.FND_CONCURRENT_REQUESTS_N5 validate structure online;

select dbms_metadata.get_ddl('INDEX','FND_CONCURRENT_REQUESTS_N5','APPLSYS') from dual;

drop index APPLSYS.FND_CONCURRENT_REQUESTS_N5;

CREATE INDEX "APPLSYS"."FND_CONCURRENT_REQUESTS_N5" ON "APPLSYS"."FND_CONCURRENT_REQUESTS" ("DESCRIPTION")
  PCTFREE 0 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)
  TABLESPACE "APPS_TS_TX_IDX"

========================================================================================================================

Analyze Index APPLSYS.FND_CONCURRENT_REQUESTS_N6 validate structure online;

select dbms_metadata.get_ddl('INDEX','FND_CONCURRENT_REQUESTS_N6','APPLSYS') from dual;

drop index APPLSYS.FND_CONCURRENT_REQUESTS_N6;

  CREATE INDEX "APPLSYS"."FND_CONCURRENT_REQUESTS_N6" ON "APPLSYS"."FND_CONCURRENT_REQUESTS" ("CONCURRENT_PROGRAM_ID",

"PROGRAM_APPLICATION_ID")
  PCTFREE 10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)
  TABLESPACE "APPS_TS_TX_IDX"

========================================================================================================================

Analyze Index APPLSYS.FND_CONCURRENT_REQUESTS_N7 validate structure online;

select dbms_metadata.get_ddl('INDEX','FND_CONCURRENT_REQUESTS_N7','APPLSYS') from dual;

drop index APPLSYS.FND_CONCURRENT_REQUESTS_N7;

CREATE INDEX "APPLSYS"."FND_CONCURRENT_REQUESTS_N7" ON "APPLSYS"."FND_CONCURRENT_REQUESTS" ("PHASE_CODE", "STATUS_CODE")
  PCTFREE 10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)
  TABLESPACE "APPS_TS_TX_IDX"

========================================================================================================================

Analyze Index APPLSYS.FND_CONCURRENT_REQUESTS_N8 validate structure online;

select dbms_metadata.get_ddl('INDEX','FND_CONCURRENT_REQUESTS_N8','APPLSYS') from dual;


drop index APPLSYS.FND_CONCURRENT_REQUESTS_N8;


CREATE INDEX "APPLSYS"."FND_CONCURRENT_REQUESTS_N8" ON "APPLSYS"."FND_CONCURRENT_REQUESTS"

("RESPONSIBILITY_APPLICATION_ID", "RESPONSIBILITY_ID")
  PCTFREE 10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)
  TABLESPACE "APPS_TS_TX_IDX"

========================================================================================================================


Analyze Index APPLSYS.FND_CONCURRENT_REQUESTS_N9 validate structure online;

select dbms_metadata.get_ddl('INDEX','FND_CONCURRENT_REQUESTS_N9','APPLSYS') from dual;


drop index APPLSYS.FND_CONCURRENT_REQUESTS_N9;

CREATE INDEX "APPLSYS"."FND_CONCURRENT_REQUESTS_N9" ON "APPLSYS"."FND_CONCURRENT_REQUESTS" ("OPS_INSTANCE", "STATUS_CODE")
  PCTFREE 0 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)
  TABLESPACE "APPS_TS_TX_IDX"

========================================================================================================================


Analyze Index APPLSYS.FND_CONCURRENT_REQUESTS_N10 validate structure online;

select dbms_metadata.get_ddl('INDEX','FND_CONCURRENT_REQUESTS_N10','APPLSYS') from dual;


drop index APPLSYS.FND_CONCURRENT_REQUESTS_N10;


  CREATE INDEX "APPLSYS"."FND_CONCURRENT_REQUESTS_N10" ON "APPLSYS"."FND_CONCURRENT_REQUESTS" ("CD_ID")
  PCTFREE 10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)
  TABLESPACE "APPS_TS_TX_IDX"

========================================================================================================================


Analyze Index APPLSYS.FND_CONCURRENT_REQUESTS_N11 validate structure online;

select dbms_metadata.get_ddl('INDEX','FND_CONCURRENT_REQUESTS_N11','APPLSYS') from dual;


drop index APPLSYS.FND_CONCURRENT_REQUESTS_N11;


CREATE INDEX "APPLSYS"."FND_CONCURRENT_REQUESTS_N11" ON "APPLSYS"."FND_CONCURRENT_REQUESTS" ("REQUEST_DATE")
  PCTFREE 0 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)
  TABLESPACE "APPS_TS_TX_IDX"

========================================================================================================================


Analyze Index APPLSYS.FND_CONCURRENT_REQUESTS_U1 validate structure online;

select dbms_metadata.get_ddl('INDEX','FND_CONCURRENT_REQUESTS_U1','APPLSYS') from dual;



drop index APPLSYS.FND_CONCURRENT_REQUESTS_U1;


CREATE UNIQUE INDEX "APPLSYS"."FND_CONCURRENT_REQUESTS_U1" ON "APPLSYS"."FND_CONCURRENT_REQUESTS" ("REQUEST_ID")
  PCTFREE 0 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)
  TABLESPACE "APPS_TS_TX_IDX"

========================================================================================================================

ERROR at line 1:
ORA-08102: index key not found, obj# 696249, file 406, block 290081 (2)

Concurrent Request Scripts

Concurrent Request Scripts

****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