Size UNDO tablespace for Automatic Undo Management

 

Sizing an UNDO tablespace requires three pieces of data. (Note:Overall consideration for peak/heavy vs normal system activity should be taken into account when peforming the calculations.) (UR) UNDO_RETENTION in seconds (UPS) Number of undo data blocks generated per second (DBS) Overhead varies based on extent and file size (db_block_size) UndoSpace = [UR * (UPS * DBS)] + (DBS * 24) Two can be obtained from the initialization file: UNDO_RETENTION and DB_BLOCK_SIZE. The third piece of the formula requires a query against the database. The number of undo blocks generated per second can be acquired from V$UNDOSTAT. The following formula calculates the total number of blocks generated and divides it by the amount of time monitored, in seconds: SQL>SELECT (SUM(undoblks))/ SUM ((end_time - begin_time) * 86400) FROM v$undostat; Column END_TIME and BEGIN_TIME are DATE data types. When DATE data types are subtracted, the result is in days. To convert days to seconds, you multiply by 86400, the number of seconds in a day. The result of the query returns the number of undo blocks per second. This value needs to be multiplied by the size of an undo block, which is the same size as the database block defined in DB_BLOCK_SIZE. The following query calculates the number of bytes needed: SQL> SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes" FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'), (SELECT (SUM(undoblks)/SUM(((end_time - begin_time)*86400))) AS UPS FROM v$undostat), (select block_size as DBS from dba_tablespaces where tablespace_name= (select upper(value) from v$parameter where name = 'undo_tablespace'));

New Background Processes In 11g

 

Here are the new background processes in 11g :- *  ACMS (atomic control file to memory service) per-instance process is an agent that contributes to ensuring a distributed SGA memory update is either globally committed on success or globally aborted in the event of a failure in an Oracle RAC environment. *  DBRM (database resource manager) process is responsible for setting resource plans and other resource manager related tasks. * DIA0 (diagnosability process 0) (only 0 is currently being used) is responsible for hang detection and deadlock resolution. *  DIAG (diagnosability) process performs diagnostic dumps and executes global oradebug commands. *  EMNC (event monitor coordinator) is the background server process used for database event management and notifications. *  FBDA (flashback data archiver process) archives the historical rows of tracked tables into flashback data archives. Tracked tables are tables which are enabled for flashback archive. When a transaction containing DML on a tracked table commits, this process stores the pre-image of the rows into the flashback archive. It also keeps metadata on the current rows. FBDA is also responsible for automatically managing the flashback data archive for space, organization, and retention and keeps track of how far the archiving of tracked transactions has occurred. *  GTX0-j (global transaction) processes provide transparent support for XA global transactions in an Oracle RAC environment. The database autotunes the number of these processes based on the workload of XA global transactions. Global transaction processes are only seen in an Oracle RAC environment. *  KATE performs proxy I/O to an ASM metafile when a disk goes offline. *  MARK marks ASM allocation units as stale following a missed write to an offline disk. *  SMCO (space management coordinator) process coordinates the execution of various space management related tasks, such as proactive space allocation and space reclamation. It dynamically spawns slave processes (Wnnn) to implement the task. *  VKTM (virtual keeper of time) is responsible for providing a wall-clock time (updated every second) and reference-time counter (updated every 20 ms and available only when running at elevated priority).

DataPump - How to Specify a Query

 

The examples below are based on the following demo schema's: user SCOTT created with script:                              $ORACLE_HOME/rdbms/admin/scott.sql user HR created with script: $ORACLE_HOME/demo/schema/human_resources/hr_main.sql The Export Data Pump and Import Data Pump examples that are mentioned below are based on the directory my_dir. This directory object needs to refer to an existing directory on the server where the Oracle RDBMS is installed. Example: -- for Windows platforms: CONNECT system/manager CREATE OR REPLACE DIRECTORY my_dir AS 'D:\export'; GRANT read,write ON DIRECTORY my_dir TO public; -- for Unix platforms: CONNECT system/manager  CREATE OR REPLACE DIRECTORY my_dir AS '/home/users/export';  GRANT read,write ON DIRECTORY my_dir TO public; 1. QUERY in Parameter file. Using the QUERY parameter in a parameter file is the preferred method. Put double quotes around the text of the WHERE clause. Example to export the following data with the Export Data Pump client: from table scott.emp all employees whose job is analyst or whose salary is 3000 or more; and from from table hr.departments all deparments of the employees whose job is analyst or whose salary is 3000 or more. File: expdp_q.par ----------------- DIRECTORY =&nb p;my_dir DUMPFILE  = exp_query.dmp LOGFILE   = exp_query.log SCHEMAS  &nbs ;= hr, scott INCLUDE   = TABLE:"IN ('EMP', 'DEPARTMENTS')" QUERY &n sp;   = scott.emp:"WHERE job = 'ANALYST' OR sal >= 3000" place following 3 lines on one single line: QUERY     = hr.departments:"WHERE department_id IN (SELECT DISTINCT department_id FROM hr.employees e, hr.jobs j WHERE e.job_id=j.job_id AND UPPER(j.job_title) = 'ANALYST' OR e.salary >= 3000)" -- Run Export DataPump job: %expdp system/manager parfile=expdp_q.par Note that in this example the TABLES parameter cannot be used, because all table names that are specified at the TABLES parameter should reside in the same schema. 2. QUERY on Command line. The QUERY parameter can also be used on the command line. Again, put double quotes around the text of the WHERE clause. Example to export the following data with the Export Data Pump client: table scott.dept; and from table scott.emp all employees whose name starts with an 'A' -- Example Windows platforms: -- Note that the double quote character needs to be 'escaped' -- Place following statement on one single line: D:\> expdp scott/tiger DIRECTORY=my_dir DUMPFILE=expdp_q.dmp LOGFILE=expdp_q.log TABLES=emp,dept QUERY=emp:\"WHERE ename LIKE 'A%'\" -- Example Unix platforms: -- Note that all special characters need to be 'escaped' % expdp scott/tiger DIRECTORY=my_dir \ DUMPFILE=expdp_q.dmp LOGFILE=expdp_q.log TABLES=emp,dept \ QUERY=emp:\"WHERE ename LIKE \'A\%\'\" Note that with the original export client two jobs were required: -- Example Windows platforms: -- Place following statement on one single line: D:\> exp scott/tiger FILE=exp_q1.dmp LOG=exp_q1.log TABLES=emp QUERY=\"WHERE ename LIKE 'A%'\" D:\> exp scott/tiger FILE=exp_q2.dmp LOG=exp_q2.log TABLES=dept -- Example Unix platforms: > exp scott/tiger FILE=exp_q1.dmp LOG=exp_q1.log TABLES=emp \ QUERY=\"WHERE ename LIKE \'A\%\'\" > exp scott/tiger FILE=exp_q2.dmp LOG=exp_q2.log TABLES=dept 3. QUERY in Oracle Enterprise Manager Database Console. The QUERY can also be specified in the Oracle Enterprise Manager Database Console. E.g.: Login to the Oracle Enterprise Manager 10g Database Console, e.g.: http://my_node_name:5500/em Click on link 'Maintenance' Under 'Utilities', click on link 'Export to Files' Answer questions on the following pages. At 'step 2 of 5' (the page with the Options), click on link 'Show Advanced Options' At the end of the page, under the QUERY option, click on button 'Add' At the next page, choose the table name (SCOTT.EMP) And specify the SELECT statement predicate clause to be applied to tables being exported, e.g.: WHERE ename LIKE 'A%' Continue with the remaining options, and submit the job. 4. Import Data Pump parameter QUERY. Similar to previous examples with Export Data Pump, the QUERY parameter can also be used during the import. An example of how to use the QUERY parameter with Import Data Pump: -- In source database: -- Export the schema SCOTT: %expdp scott/tiger DIRECTORY=my_dir DUMPFILE=expdp_s.dmp \ LOGFILE=expdp_s.log SCHEMAS=scott -- In target database: -- Import all employees of department 10: %impdp scott/tiger DIRECTORY=my_dir DUMPFILE=expdp_s.dmp \ LOGFILE=impdp_s.log TABLES=emp TABLE_EXISTS_ACTION=append \ QUERY=\"WHERE deptno = 10\" CONTENT=data_only Note that this feature was not available with the original import client (imp). Also note that the parameter TABLE_EXISTS_ACTION=append is used to allow the import into an existing table and that CONTENT=data_only is used to skip importing statistics, indexes, etc.

Copy Database Schemas To A New Database With Same Login Password ?

 

How to copy database users from one database to another new database and keep the

login password and granted roles, privileges ?

 

1. Oracle10g and above: Use Data Pump. In Oracle10g you can use the Export DataPump and Import DataPump utilities. Example: -- Step 1: In source database, run a schema level Export Data Pump -- job and connect with a user who has the EXP-FULL_DATABASE role: % expdp system/manager DIRECTORY=my_dir DUMPFILE=exp_scott.dmp \ LOGFILE=exp_scott.log SCHEMAS=scott -- Step 2: In target database, run a schema level Import Data Pump -- job which will also create the user in the target database: % impdp system/manager DIRECTORY=my_dir DUMPFILE=exp_scott.dmp \ LOGFILE=imp_scott.log SCHEMAS=scott Or you can create a logfile (SQLFILE) with all relevant statements. E.g.: impdp system/manager directory=my_dir dumpfile=exp_scott.dmp logfile=imp_scott.log schemas=scott sqlfile=imp_user.sql or: 2. In Oracle9i and above: query the Data Dictionary in the source database to obtain the required information to pre-create the user in the target database. Example: 2.1. Obtain the CREATE USER statement in the source database. E.g.: SET long 200000000 SELECT dbms_metadata.get_ddl('USER','SCOTT') FROM dual; 2.2. Run other queries in the source database to determine which privileges, grants, roles, and tablespace quotas are granted to the users. E.g.: SET lines 120 pages 100 SELECT * FROM dba_sys_privs WHERE grantee='SCOTT'; SELECT * FROM dba_role_privs WHERE grantee='SCOTT'; SELECT * FROM dba_tab_privs WHERE grantee='SCOTT'; SELECT * FROM dba_ts_quotas WHERE username='SCOTT'; 2.3. Create a script file that contains the CREATE USER, CREATE ROLE statements, the GRANT statements, and the ALTER USER statements for tablespace quotas. 2.4. Pre-create the tablespaces for this user with SQL*Plus in the target database. Note that the original CREATE TABLESPACE statement can be obtained in the source database with DBMS_METADATA.GET_DDL. E.g.: SET long 200000000 SELECT dbms_metadata.get_ddl('TABLESPACE','USERS') FROM dual; 2.5. Run the script of step 2.3. to create the user in the target database. 2.6. Run a user level export. E.g.: exp system/manager file=exp_scott.dmp log=exp_scott.log owner=scott 2.7. Import this export dumpfile into the target database. E.g.: imp system/manager file=exp_scott.dmp log=imp_scott.log fromuser=scott touser=scott or: 3. If all users need to be copied into the new target database: use a full database export from the source database and a full database into the target database. Example: 3.1. Run a full database export. E.g.: exp system/manager file=exp_f.dmp log=exp_f.log full=y or in Oracle10g: expdp system/manager directory=my_dir dumpfile=expdp_f.dmp logfile=expdp_f.log full=y 3.2. Pre-create the tablespaces with SQL*Plus in that target database if they have a different directory structure on the target server. If the directory structure on the target is the same as on the source, ensure that this directory structure is in place (import does not create directories when creating tablespaces). Note that the original CREATE TABLESPACE statement can be obtained with dbms_metadata.get_ddl (see the example in step 2.4. above). 3.3. Import the data into the target database with: imp system/manager file=exp_f.dmp log=imp_f.log full=y or in Oracle10g: impdp system/manager directory=my_dir dumpfile=expdp_f.dmp logfile=impdp_f.log full=y

SQL*Plus command line history completion

 

The rlwrap (readline wrapper) utility provides a command history and editing of keyboard input

for any other command. This is a really handy addition to SQL*Plus and RMAN .

Download the latest rlwrap software from the following URL.
http://utopia.knoware.nl/~hlub/uck/rlwrap/

Unzip and install the software using the following commands.

gunzip rlwrap*.gz
tar -xvf rlwrap*.tar
cd rlwrap*
./configure
make
make check
make install

Run the following commands, or better still append then to the ".bash_profile" of the

oracle software owner.

alias sqlplus='rlwrap ${ORACLE_HOME}/bin/sqlplus'
alias rman='rlwrap ${ORACLE_HOME}/bin/rman'
alias expdp='rlwrap ${ORACLE_HOME}/bin/expdp'

You can now start SQL*Plus or RMAN using "sqlplus" and "rman" respectively, and you will have

a basic command history and the current line will be editable using the arrow and delete keys.

Analyze your Oracle Statspack Report

 

StatspackAnalyzer is a true expert system that seeks to codify expert DBA knowledge and advice against any

STATSPACK or AWR report. The well-structured decision rules of expert Oracle tuning specialists were collected,

quantified and then generalized and validated against real-world STATSPACK and AWR reports.

While no automated tool can fully replicate the decision processes of a human DBA tuning expert, this tool

makes observations about exceptional conditions within the STATSPACK or AWR report. This tool was never

intended to replace the human intuition of an Oracle performance expert, and all observations from

statspackanalyzer should be validated with a human expert.

 

Script to Monitor Concurrent Jobs and Hanging Sessions

 

Here is a monitoring system to monitor all concurrent jobs, concurrent managers and hung sessions every

hour proactively and take appropriate action immediately. It gives the following reports

1. List of Concurrent Jobs that completed with error in last one hour.
2. List of Concurrent Jobs running for more then 1 hour.
3. List of concurrent Jobs completed with Warning in last one hour
4. List of Jobs that are Pending Normal for more than 10 Minutes.
5. List of Hung sessions or Orphan sessions.
6. List of Concurrent managers with Pending Normal jobs.
7. Critical Jobs completed in last one hour with completion time.

 

SELECT A FROM

(

select 'CONCURRENT PROGRAMS COMPLETED WITH ERROR STATUS BETWEEN '||to_char(sysdate - (1/24),

'dd-mon-yyyy hh24:mi:ss') || ' AND '|| to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') A, 'A' B,1 SRT from dual

UNION

select RPAD('-',125,'-') A, 'A' B,1.1 SRT from dual

UNION

SELECT to_char( rpad('REQUEST_ID',10) ||' '||rpad('ACTUAL START DATE',20)|| ' ' ||

rpad('CONCURRENT PROGRAM NAME',65)||' '||rpad('REQUESTOR',10)||' '||'P REQ ID'), 'A' B,1.2 FROM DUAL

UNION

select to_char( rpad(to_char(Request_ID),10) ||' '|| RPAD(NVL(to_char(actual_start_date,

'dd-mon-yyyy hh24:mi:ss'),' '),20) || ' ' || rpad(substr(Program,1,65),65)||' '||rpad(substr(requestor,1,10),

10)||' '||to_char(Parent_Request_ID) ) A, 'A' B, 1.4 SRT from fnd_conc_req_summary_v conc

where actual_completion_date > sysdate - (1/24) and phase_code = 'C' and status_code = 'E'

UNION

select RPAD('-',125,'-') A, 'A' B,1.6 SRT from dual

UNION

SELECT ' ', 'A', 1.8 FROM DUAL

UNION

SELECT ' ', 'A', 1.86 FROM DUAL

-----------------------------------------------------------

UNION

select 'CONCURRENT PROGRAMS COMPLETED WITH WARNING STATUS BETWEEN '||to_char(sysdate - (1/24),

'dd-mon-yyyy hh24:mi:ss') || ' AND '|| to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') A, 'D' B,1 SRT from dual

UNION

select RPAD('-',125,'-') A, 'D' B, 1.1 SRT from dual

UNION

SELECT to_char( rpad('REQUEST_ID',10) ||' '||rpad('ACTUAL START DATE',20)|| ' ' ||

rpad('CONCURRENT PROGRAM NAME',65)||' '||rpad('REQUESTOR',10)||' '||'P REQ ID'), 'D' B, 1.2 FROM DUAL

UNION

select to_char( rpad(to_char(Request_ID),10) ||' '|| RPAD(NVL(to_char(actual_start_date,

'dd-mon-yyyy hh24:mi:ss'),' '),20) || ' ' || rpad(substr(Program,1,65),65)||' '||rpad(substr(requestor,1,10),10)

||' '||to_char(Parent_Request_ID) ) A, 'D' B, 1.4 SRT from fnd_conc_req_summary_v conc where

actual_completion_date > sysdate - (1/24) and phase_code = 'C' and status_code = 'G'

and concurrent_program_id not in (47654,31881,47737)

UNION

select RPAD('-',125,'-') A, 'D' B, 1.8 SRT from dual

-----------------------------------------------------------

UNION

SELECT ' ', 'D', 1.86 FROM DUAL

UNION

SELECT ' ', 'D', 1.88 FROM DUAL

UNION

select 'CONCURRENT PROGRAMS THAT ARE PENDING NORMAL FOR THE PAST 10 MINUTES ' A, 'E' B,1 SRT

from dual

UNION

select RPAD('-',125,'-') A, 'E' B, 1.1 SRT from dual

UNION

SELECT to_char( rpad('REQUEST_ID',10) ||' '||rpad('ACTUAL START DATE',20)|| ' ' ||

rpad('CONCURRENT PROGRAM NAME',65)||' '||rpad('REQUESTOR',10)||' '||'P REQ ID'), 'E' B, 1.2 FROM DUAL

UNION

select to_char( rpad(to_char(Request_ID),10) ||' '|| RPAD(NVL(to_char(actual_start_date,

'dd-mon-yyyy hh24:mi:ss'),' '),20) || ' ' || rpad(substr(Program,1,65),65)||' '||rpad(substr(requestor,1,10),10)

||' '||to_char(Parent_Request_ID) ) A, 'E' B, 2 SRT FROM FND_CONC_REQ_SUMMARY_V CONC

WHERE SYSDATE - REQUEST_DATE > 0.00694444444444444 AND REQUESTED_START_DATE < SYSDATE

AND PHASE_CODE = 'P' AND STATUS_CODE = 'Q'

UNION

select RPAD('-',125,'-') A, 'E' B, 3 SRT from dual

UNION

SELECT chr(10)||chr(10) A, 'E' B, 4.4 SRT FROM DUAL

UNION

select 'CONCURRENT PROGRAMS THAT STARTED BEFORE '||to_char(sysdate - (1/24),'dd-mon-yyyy hh24:mi:ss')

||' AND ARE STILL RUNNING ' A, 'B' B,4.6 SRT FROM DUAL

UNION

SELECT RPAD('-',125,'-') A, 'B' B, 4.8 SRT FROM DUAL

UNION

SELECT to_char( rpad('REQUEST_ID',10) ||' '||rpad('ACTUAL START DATE',20)|| ' ' ||

rpad('CONCURRENT PROGRAM NAME',65)||' '||rpad('REQUESTOR',10)||' '||'P REQ ID'), 'B' B, 4.84 SRT FROM DUAL

UNION

SELECT to_char( rpad(to_char(Request_ID),10) ||' '|| RPAD(NVL(to_char(actual_start_date,

'dd-mon-yyyy hh24:mi:ss'),'-'),20) || ' ' || rpad(substr(Program,1,65),65)||' '||rpad(substr(requestor,1,10),

10)||' '||to_char(Parent_Request_ID) ) A, 'B' B, 4.86 SRT FROM FND_CONC_REQ_SUMMARY_V CONC

WHERE SYSDATE - ACTUAL_START_DATE > 0.0416666666666667 AND PHASE_CODE = 'R' AND STATUS_CODE = 'R'

-----------------------------------------------------------------------

UNION

SELECT RPAD('-',125,'-') A, 'C' B, 1.1 SRT FROM DUAL

UNION

SELECT ' ', 'C', 1.2 FROM DUAL

UNION

SELECT ' ', 'C', 5.8 FROM DUAL

UNION

select ' FOLLOWING ARE THE DETAILS OF HUNG OR ORPHAN SESSIONS AS OF '||to_char(sysdate ,

'dd-mon-yyyy hh24:mi:ss') A, 'C' B,1.5 SRT from dual

UNION

select RPAD('-',125,'-') A, 'C' B, 1.6 SRT from dual

UNION

SELECT to_char(rpad(to_char('SID'),5) ||' '||rpad('PROCESS',12)|| ' ' ||rpad('MODULE',10)||' '||rpad('ACTION',

25)||' '||rpad('USERNAME',15)||' '||rpad('PROGRAM',20)||' '||rpad('EVENT',25)) A, 'C' B, 5.2 FROM DUAL

UNION

select to_char(rpad(nvl(to_char(a.sid), ' '),7,' ')||' '||rpad(nvl(a.process, ' '),19,' ')||' '||rpad(nvl(a.module,

' '),10)||' '||rpad(nvl(a.action, ' '),20)||' '||rpad(nvl(a.username, ' '),15)||' '||rpad(nvl(a.program, ' '),20)||' '||

rpad(c.event,25)) A,'C' B, 5.4 SRT from gv$session a, gv$process b, gv$session_Wait c where c.event not

like 'SQL%' and c.event not in ('pmon timer','rdbms ipc message','pipe get','queue messages','smon timer',

'wakeup time manager','PL/SQL lock timer','jobq slave wait','ges remote message','async disk IO','gcs remote

message','PX Deq: reap credit','PX Deq: Execute Reply') and a.paddr=b.addr and a.sid=c.sid and a.inst_id=

c.inst_id and a.inst_id=b.inst_id and a.last_call_et >1800

UNION

select RPAD('-',125,'-') A, 'C' B, 5.6 SRT from dual

UNION

-----------------------------------------------------------------------

SELECT ' ', 'F', 1.01 FROM DUAL

UNION

select 'PENDING NORMAL MANAGERS IN LAST ONE HOUR '|| ' '|| to_char(sysdate - (1/24),

'dd-mon-yyyy hh24:mi:ss') || ' AND '|| to_char(sysdate,'dd-mon-yyyy hh24:mi:ss' ) A, 'F' B,1 SRT from dual

UNION

select RPAD('-',125,'-') A, 'F' B, 1.1 SRT from dual

UNION

SELECT to_char( rpad('CONCURRENT MANAGER NAME',35) || rpad('ACTUAL',25)|| ' ' ||rpad('TARGET',

20)||' '||rpad('RUNNING',25)||' '||'PENDING'), 'F' B, 1.2 FROM DUAL

UNION

select to_char (

decode (

fcq.USER_CONCURRENT_QUEUE_NAME,

'XXXXXX: High Workload',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,53),

'XXXXXX: Standard Manager',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,50),

'XXXXXX: MRP Manager',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,50),

'XXXXXX: Payroll Manager',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,51),

'XXXXXX: Fast Jobs',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,56),

'XXXXXX: Workflow',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,56),

'XXXXXX: Critical Jobs',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,56),

'Inventory Manager',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,56),

'Conflict Resolution Manager',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,51),

null)

||' '||rpad(TO_CHAR(NVL(FCQ.RUNNING_PROCESSES,0)),30)||' '||

rpad(to_char(nvl(FCQ.MAX_PROCESSES,0)),30) ||' '||rpad(to_char(NVL(running,0)),30) || ' '||

to_char(NVL(PENDING,0))) A, 'F' B, 1.3 SRT

from

apps.fnd_concurrent_queues_vl FCQ,

(SELECT nvl(count(*),0) Running, fcwr.concurrent_queue_id

FROM fnd_concurrent_worker_requests fcwr

WHERE fcwr.concurrent_queue_id IN (1755,1756,1757,1758,1759,1760,1754,10,4)

AND (fcwr.phase_code = 'R')

AND fcwr.hold_flag != 'Y'

AND SYSDATE - fcwr.requested_start_date >= 0.00694444444444444

group by fcwr.concurrent_queue_id ) RUNNING ,

( SELECT nvl(count(*),0) Pending, fcwp.concurrent_queue_id

FROM fnd_concurrent_worker_requests fcwp

WHERE fcwp.concurrent_queue_id IN (1755,1756,1757,1758,1759,1760,1754,10,4)

AND (fcwp.phase_code = 'P')

AND fcwp.hold_flag != 'Y'

AND sysdate-fcwp.requested_start_date >= 0.00694444444444444

group by fcwp.concurrent_queue_id ) PENDING

WHERE FCQ.concurrent_queue_id=RUNNING.concurrent_queue_id(+)

AND FCQ.concurrent_queue_id=PENDING.concurrent_queue_id(+)

AND fcQ.concurrent_queue_id IN (1755,1756,1757,1758,1759,1760,1754,10,4)

UNION

select RPAD('-',125,'-') A, 'F' B, 1.4 SRT from dual

UNION

SELECT chr(10)||chr(10) A, 'F' B, 1.5 SRT FROM DUAL

UNION

-----------------------------------------------------------------------

SELECT ' ', 'G', 1.01 FROM DUAL

UNION

select 'CRITICAL PROGRAMS STATUS IN LAST ONE HOUR '|| ' '|| to_char(sysdate - (1/24),

'dd-mon-yyyy hh24:mi:ss') || ' AND '|| to_char(sysdate,'dd-mon-yyyy hh24:mi:ss' ) A , 'G' B,1 SRT from dual

UNION

select RPAD('-',125,'-') A, 'G' B, 1.1 SRT from dual

UNION

SELECT to_char( rpad('CONCURRENT PROGRAM NAME',55) || rpad('AVG TIME',20)|| ' ' ||rpad

('CURR MAX TIME',16)||' '|| 'REQUEST_ID'), 'G' B, 1.1 FROM DUAL

UNION

SELECT to_char (

decode (PROGRAM_NAME,

'AutoCreate Configuration Items',RPAD(PROGRAM_NAME,70),

'Memory-based Snapshot',RPAD(PROGRAM_NAME,71),

'Order Import',RPAD(PROGRAM_NAME,80),

'Workflow Background Process',RPAD(PROGRAM_NAME,69),

PROGRAM_NAME) ||' '||

rpad(TO_CHAR(STATIC.AVG_TIME),25) || ' '||

rpad(TO_CHAR(DYNAMIC.CURR_MAX_TIME),25) || ' '||

to_char(NVL(REQUEST_ID,NULL))) A, 'G' B, 1.2 SRT

FROM

(SELECT

CONCURRENT_PROGRAM_ID,

USER_CONCURRENT_PROGRAM_NAME,

REQUEST_ID,

ROUND((ACTUAL_COMPLETION_DATE-ACTUAL_START_DATE)*24*60,0) CURR_MAX_TIME

FROM APPS.FND_CONC_REQ_SUMMARY_V fcr

WHERE CONCURRENT_PROGRAM_ID IN (36888,

48681,39442,33137,47730,47731,47712,47729,31881)

and phase_code='C'

AND STATUS_CODE='C'

AND ACTUAL_COMPLETION_DATE>=(sysdate - (1/24))

AND REQUEST_ID IN (

SELECT MAX(REQUEST_ID) FROM APPS.FND_CONC_REQ_SUMMARY_V fcr WHERE CONCURRENT_PROGRAM_ID

IN (36888,48681,39442,33137,47730,47731,47712,47729,31881)

and phase_code='C'

AND STATUS_CODE='C'

AND ACTUAL_COMPLETION_DATE>=(sysdate - (1/24))

GROUP BY CONCURRENT_PROGRAM_ID) ) DYNAMIC ,

(select distinct CONCURRENT_PROGRAM_ID "CONCURRENT_PROGRAM_ID",

USER_CONCURRENT_PROGRAM_NAME "PROGRAM_NAME",

DECODE ( CONCURRENT_PROGRAM_ID,36888,39442,33137,31881,10,NULL) AVG_TIME

FROM APPS.FND_CONCURRENT_PROGRAMS_TL fcr

WHERE CONCURRENT_PROGRAM_ID IN

(36888,39442,33137,31881)

AND LANGUAGE='US' ) STATIC

WHERE DYNAMIC.CONCURRENT_PROGRAM_ID(+)=STATIC.CONCURRENT_PROGRAM_ID

UNION

select RPAD('-',125,'-') A, 'G' B, 1.4 SRT from dual

UNION

SELECT chr(10)||chr(10) A, 'G' B, 1.5 SRT FROM DUAL

-----------------------------------------------------------------------

) TEMP

ORDER BY B, SRT, A