Find Concurrent Program Run History from backend

Query to find concurrent program run history and status

SELECT f.request_id,

         pt.user_concurrent_program_name

             user_conc_program_name,

         f.actual_start_date

             start_on,

         f.actual_completion_date

             end_on,

         p.concurrent_program_name

             concurrent_program_name,

         DECODE (f.phase_code,

                 'R', 'RUNNING',

                 'C', 'COMPLETED',

                 f.phase_code)

             phase,

         DECODE (f.status_code,  'C', 'NORMAL',  'E', 'ERROR',  f.status_code)

             Status,

         f.requested_by,

         fu.user_id,

         fu.user_name

    FROM apps.fnd_concurrent_programs   p,

         apps.fnd_concurrent_programs_tl pt,

         apps.fnd_concurrent_requests   f,

         apps.fnd_user                  fu

   WHERE     f.concurrent_program_id = p.concurrent_program_id

         AND f.program_application_id = p.application_id

         AND f.concurrent_program_id = pt.concurrent_program_id

         AND f.program_application_id = pt.application_id

         AND pt.language = USERENV ('lang')

         AND f.actual_start_date IS NOT NULL

         AND f.actual_start_date >

             TO_DATE ('15-DEC-2021 00:00:00', 'DD-MON-YYYY HH24:MI:SS')

         AND f.actual_completion_date <

             TO_DATE ('17-DEC-2021 23:59:59', 'DD-MON-YYYY HH24:MI:SS')

         AND f.requested_by = fu.user_id

         AND pt.USER_CONCURRENT_PROGRAM_NAME = '&User_concurrent_program_name'

--AND fu.user_name = '&user_name'

ORDER BY f.actual_start_date ASC; 

##Change the date accordingly.

Temp Tablespace Usage

Oracle Temp Tablespace usage

Check for Temp tablespace usage

set lin 200;

col file_name for a75;

col autoextensible for a15;

select file_name,tablespace_name,sum(bytes)/1024/1024 as FILE_SIZE,sum(maxbytes)/1024/1024 as MAX_SIZE,autoextensible from dba_temp_files

where tablespace_name ='TEMP' group by file_name,tablespace_name,autoextensible order by file_name;

Data file usage of a tablespace

Oracle Tablespaces 

Check Data File Size in any given Tablespace (Dynamic) :

set lin 200;

col file_name for a75;

col autoextensible for a15;

select file_name,tablespace_name,sum(bytes)/1024/1024 "FILE_SIZE(MB)",sum(maxbytes)/1024/1024 as MAX_SIZE,autoextensible from dba_data_files

where tablespace_name ='&tablespace_name' group by file_name,tablespace_name,autoextensible order by file_name;

Query to find Tablespace utilization in Oracle Database

Oracle Tablespace Utilization

Find the tablespace utilization on Oracle Database (10g,11g,12c,19c)

column file_name format a45

column tablespace_name format a10

col tablespace_name for a40

set verify off

set pages 3000

set line 3000

 

SELECT  dts.tablespace_name, 

NVL(ddf.bytes / 1024 / 1024, 0) avail,

NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024 used,

NVL(dfs.bytes / 1024 / 1024, 0) free,

TO_CHAR(NVL((ddf.bytes - NVL(dfs.bytes, 0)) / ddf.bytes * 100, 0), '990.00') "Used %" ,

TO_CHAR(NVL((ddf.bytes - NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)) / ddf.bytes* 100, 0), '990.00') free_pct,

decode(sign((NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024)/0.90 - NVL(ddf.bytes/1024/1024, 0)),-1,0,

(NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024)/0.90 - NVL(ddf.bytes / 1024 / 1024, 0))  "Required MB" FROM

sys.dba_tablespaces dts,

(select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) ddf,

(select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) dfs

WHERE

dts.tablespace_name = ddf.tablespace_name(+)

AND dts.tablespace_name = dfs.tablespace_name(+)

order by free_pct;

Best Wishes!!