Query To Check Concurrent Program Run History

 

Query To Check Concurrent Program Run History

REM +======================================================================+
REM                    
REM File Name: reqhistory.sql
REM 
REM Description:
REM   Query To Check Concurrent Program Run History
REM   
REM Notes:
REM   Usage: sqlplus <apps_user/apps_passwd> @reqhistory.sql 
REM 
REM Input Required :
REM    Number of days and User Concurrent Program Name  
REM   
REM +======================================================================+

ACCEPT NO_DAYS PROMPT "Enter Number of Days for History: ";
ACCEPT USER_CONC_PROG_NAME PROMPT "Enter User Concurrent Program Name: ";

        clear columns

 set lines 180
 set pages 100
 col Parameters for a20 WORD_WRAPPED
 set pages 100
 col "Conc Program Name" for a30 WORD_WRAPPED
 col "Started at" for a20
 col "Completed at" for a20
 col "Username" for a10 WORD_WRAPPED
 SELECT distinct t.user_concurrent_program_name "Conc Program Name",
 r.REQUEST_ID "Request ID",
 to_char(r.ACTUAL_START_DATE,'dd-MON-yy hh24:mi:ss') "Started at",
 to_char(r.ACTUAL_COMPLETION_DATE,'dd-MON-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",
 u.user_name "Username",
 --ROUND ((v.actual_completion_date - v.actual_start_date) * 1440,
 --              2
  --            ) "Runtime (in Minutes)" 
 round(((nvl(v.actual_completion_date,sysdate)-v.actual_start_date)*24*60),2) "ElapsedTime(Mins)"
 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 - &NO_DAYS)
 --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 '&USER_CONC_PROG_NAME'
 order by to_char(r.ACTUAL_COMPLETION_DATE,'dd-MON-yy hh24:mi:ss') desc;

undef NO_DAYS
undef USER_CONC_PROG_NAME

auto_indexes.sql

This feature in 19c  

Automatic Indexing autocreates indexes as invisible first and then tests the index impact against SQL statements. If the impact is positive—if the SQL statements perform better with an index—the index is made visible, as  if not, that index is marked unusable.

-- -----------------------------------------------------------------------------------
-- File Name    : auto_indexes.sql
-- Author       : Amir DBA
-- Description  : Displays auto indexes for the specified schema or all schemas.
-- Requirements : Access to the DBA views.
-- Call Syntax  : @auto_indexes (schema-name or all)
-- -----------------------------------------------------------------------------------
SET VERIFY OFF LINESIZE 200

COLUMN owner FORMAT A30
COLUMN index_name FORMAT A30
COLUMN table_owner FORMAT A30
COLUMN table_name FORMAT A30

SELECT owner,
       index_name,
       index_type,
       table_owner,
       table_name
       table_type
FROM   dba_indexes
WHERE  owner = DECODE(UPPER('&1'), 'ALL', owner, UPPER('&1'))
AND    auto = 'YES'
ORDER BY owner, index_name;

session_rollback.sql

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

-- File Name    : session_rollback.sql
-- Author       : Amir DBA
-- Description  : Displays rollback information on relevant database sessions.
-- Requirements : Access to the V$ views.
-- Call Syntax  : @session_rollback
-- -----------------------------------------------------------------------------------
SET LINESIZE 200

COLUMN username FORMAT A15

SELECT s.username,
       s.sid,
       s.serial#,
       t.used_ublk,
       t.used_urec,
       rs.segment_name,
       r.rssize,
       r.status
FROM   v$transaction t,
       v$session s,
       v$rollstat r,
       dba_rollback_segs rs
WHERE  s.saddr = t.ses_addr
AND    t.xidusn = r.usn
AND   rs.segment_id = t.xidusn
ORDER BY t.used_ublk DESC;

session_pga.sql

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

-- File Name    : session_pga.sql
-- Author       : Amir DBA
-- Description  : Displays information about PGA usage for each session.
-- Requirements : Access to the V$ views.
-- Call Syntax  : @session_pga
-- -----------------------------------------------------------------------------------
SET LINESIZE 500
SET PAGESIZE 1000

COLUMN username FORMAT A30
COLUMN osuser FORMAT A20
COLUMN spid FORMAT A10
COLUMN service_name FORMAT A15
COLUMN module FORMAT A45
COLUMN machine FORMAT A30
COLUMN logon_time FORMAT A20
COLUMN pga_used_mem_mb FORMAT 99990.00
COLUMN pga_alloc_mem_mb FORMAT 99990.00
COLUMN pga_freeable_mem_mb FORMAT 99990.00
COLUMN pga_max_mem_mb FORMAT 99990.00

SELECT NVL(s.username, '(oracle)') AS username,
       s.osuser,
       s.sid,
       s.serial#,
       p.spid,
       ROUND(p.pga_used_mem/1024/1024,2) AS pga_used_mem_mb,
       ROUND(p.pga_alloc_mem/1024/1024,2) AS pga_alloc_mem_mb,
       ROUND(p.pga_freeable_mem/1024/1024,2) AS pga_freeable_mem_mb,
       ROUND(p.pga_max_mem/1024/1024,2) AS pga_max_mem_mb,
       s.lockwait,
       s.status,
       s.service_name,
       s.module,
       s.machine,
       s.program,
       TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time,
       s.last_call_et AS last_call_et_secs
FROM   v$session s,
       v$process p
WHERE  s.paddr = p.addr
ORDER BY s.username, s.osuser;

SET PAGESIZE 14

longops.sql

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

-- File Name    : longops.sql
-- Author       : Amir DBA
-- Description  : Displays information on all long operations.
-- Requirements : Access to the V$ views.
-- Call Syntax  : @longops
-- -----------------------------------------------------------------------------------

COLUMN sid FORMAT 999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A30
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10

SELECT s.sid,
       s.serial#,
       s.machine,
       ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
       ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
       ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM   v$session s,
       v$session_longops sl
WHERE  s.sid     = sl.sid
AND    s.serial# = sl.serial#;

part_tables.sql

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

-- File Name    : part_tables.sql
-- Author       : AMIR DBA
-- Description  : Displays information about all partitioned tables.
-- Requirements : Access to the DBA views.
-- Call Syntax  : @part_tables
-- -----------------------------------------------------------------------------------

SELECT owner, table_name, partitioning_type, partition_count
FROM   dba_part_tables
WHERE  owner NOT IN ('SYS', 'SYSTEM')
ORDER BY owner, table_name;

longops.sql

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

-- File Name    : longops.sql
-- Author       : AMIR DBA 
-- Description  : Displays information on all long operations.
-- Requirements : Access to the V$ views.
-- Call Syntax  : @longops
-- -----------------------------------------------------------------------------------

COLUMN sid FORMAT 999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A30
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10

SELECT s.sid,
       s.serial#,
       s.machine,
       ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
       ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
       ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM   v$session s,
       v$session_longops sl
WHERE  s.sid     = sl.sid
AND    s.serial# = sl.serial#;

locked_objects.sql

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

-- File Name    : locked_objects.sql
-- Author       : AMIR DBA
-- Description  : Lists all locked objects.
-- Requirements : Access to the V$ views.
-- Call Syntax  : @locked_objects
-- -----------------------------------------------------------------------------------
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF

COLUMN owner FORMAT A20
COLUMN username FORMAT A20
COLUMN object_owner FORMAT A20
COLUMN object_name FORMAT A30
COLUMN locked_mode FORMAT A15

SELECT lo.session_id AS sid,
       s.serial#,
       NVL(lo.oracle_username, '(oracle)') AS username,
       o.owner AS object_owner,
       o.object_name,
       Decode(lo.locked_mode, 0, 'None',
                             1, 'Null (NULL)',
                             2, 'Row-S (SS)',
                             3, 'Row-X (SX)',
                             4, 'Share (S)',
                             5, 'S/Row-X (SSX)',
                             6, 'Exclusive (X)',
                             lo.locked_mode) locked_mode,
       lo.os_user_name
FROM   v$locked_object lo
       JOIN dba_objects o ON o.object_id = lo.object_id
       JOIN v$session s ON lo.session_id = s.sid
ORDER BY 1, 2, 3, 4;

SET PAGESIZE 14
SET VERIFY ON

jobs.sql

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

-- File Name    : jobs.sql
-- Author       : AMIR DBA
-- Description  : Displays information about all scheduled jobs.
-- Requirements : Access to the DBA views.
-- Call Syntax  : @jobs
-- -----------------------------------------------------------------------------------
SET LINESIZE 1000 PAGESIZE 1000

COLUMN log_user FORMAT A15
COLUMN priv_user FORMAT A15
COLUMN schema_user FORMAT A15
COLUMN interval FORMAT A40
COLUMN what FORMAT A50
COLUMN nls_env FORMAT A50
COLUMN misc_env FORMAT A50

SELECT a.job,            
       a.log_user,       
       a.priv_user,     
       a.schema_user,    
       To_Char(a.last_date,'DD-MON-YYYY HH24:MI:SS') AS last_date,      
       --To_Char(a.this_date,'DD-MON-YYYY HH24:MI:SS') AS this_date,      
       To_Char(a.next_date,'DD-MON-YYYY HH24:MI:SS') AS next_date,      
       a.broken,         
       a.interval,       
       a.failures,       
       a.what,
       a.total_time,     
       a.nls_env,        
       a.misc_env          
FROM   dba_jobs a;

SET LINESIZE 80 PAGESIZE 14

index_usage.sql

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

-- File Name    : index_usage.sql
-- Author       : AMIR DBA
-- Description  : Shows the usage for the specified table indexes.
-- Call Syntax  : @index_usage (table-name) (index-name or all)
-- -----------------------------------------------------------------------------------
SET VERIFY OFF
SET LINESIZE 200

SELECT table_name,
       index_name,
       used,
       start_monitoring,
       end_monitoring
FROM   v$object_usage
WHERE  table_name = UPPER('&1')
AND    index_name = DECODE(UPPER('&2'), 'ALL', index_name, UPPER('&2'));

high_water_mark.sql

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

-- File Name    : high_water_mark.sql
-- Author       : AMIR DBA
-- Description  : Displays the High Water Mark for the specified table, or all tables.
-- Requirements : Access to the Dbms_Space.
-- Call Syntax  : @high_water_mark (table_name or all) (schema-name)
-- -----------------------------------------------------------------------------------
SET SERVEROUTPUT ON
SET VERIFY OFF

DECLARE
  CURSOR cu_tables IS
    SELECT a.owner,
           a.table_name
    FROM   all_tables a
    WHERE  a.table_name = Decode(Upper('&&1'),'ALL',a.table_name,Upper('&&1'))
    AND    a.owner      = Upper('&&2');

  op1  NUMBER;
  op2  NUMBER;
  op3  NUMBER;
  op4  NUMBER;
  op5  NUMBER;
  op6  NUMBER;
  op7  NUMBER;
BEGIN

  Dbms_Output.Disable;
  Dbms_Output.Enable(1000000);
  Dbms_Output.Put_Line('TABLE                             UNUSED BLOCKS     TOTAL BLOCKS  HIGH WATER MARK');
  Dbms_Output.Put_Line('------------------------------  ---------------  ---------------  ---------------');
  FOR cur_rec IN cu_tables LOOP
    Dbms_Space.Unused_Space(cur_rec.owner,cur_rec.table_name,'TABLE',op1,op2,op3,op4,op5,op6,op7);
    Dbms_Output.Put_Line(RPad(cur_rec.table_name,30,' ') ||
                         LPad(op3,15,' ')                ||
                         LPad(op1,15,' ')                ||
                         LPad(Trunc(op1-op3-1),15,' ')); 
  END LOOP;

END;
/

SET VERIFY ON

directory_permissions.sql

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

-- File Name    : directory_permissions.sql
-- Author       : AMIR DBA
-- Description  : Displays permission information about all directories.
-- Requirements : Access to the DBA views.
-- Call Syntax  : @directory_permissions (directory_name)
-- -----------------------------------------------------------------------------------
SET LINESIZE 200

COLUMN grantee   FORMAT A20
COLUMN owner     FORMAT A10
COLUMN grantor   FORMAT A20
COLUMN privilege FORMAT A20

COLUMN 
SELECT * 
FROM   dba_tab_privs 
WHERE  table_name = UPPER('&1');

db_info.sql

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

-- File Name    : db_info.sql
-- Author       : AMIR DBA
-- Description  : Displays general information about the database.
-- Requirements : Access to the v$ views.
-- Call Syntax  : @db_info
-- -----------------------------------------------------------------------------------
SET PAGESIZE 1000
SET LINESIZE 100
SET FEEDBACK OFF

SELECT *
FROM   v$database;

SELECT *
FROM   v$instance;

SELECT *
FROM   v$version;

SELECT a.name,
       a.value
FROM   v$sga a;

SELECT Substr(c.name,1,60) "Controlfile",
       NVL(c.status,'UNKNOWN') "Status"
FROM   v$controlfile c
ORDER BY 1;

SELECT Substr(d.name,1,60) "Datafile",
       NVL(d.status,'UNKNOWN') "Status",
       d.enabled "Enabled",
       LPad(To_Char(Round(d.bytes/1024000,2),'9999990.00'),10,' ') "Size (M)"
FROM   v$datafile d
ORDER BY 1;

SELECT l.group# "Group",
       Substr(l.member,1,60) "Logfile",
       NVL(l.status,'UNKNOWN') "Status"
FROM   v$logfile l
ORDER BY 1,2;

PROMPT
SET PAGESIZE 14
SET FEEDBACK ON

datafiles.sql

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

-- File Name    : datafiles.sql
-- Author       : AMIR DBA
-- Description  : Displays information about datafiles.
-- Requirements : Access to the V$ views.
-- Call Syntax  : @datafiles
-- -----------------------------------------------------------------------------------

SET LINESIZE 200
COLUMN file_name FORMAT A70

SELECT file_id,
       file_name,
       ROUND(bytes/1024/1024/1024) AS size_gb,
       ROUND(maxbytes/1024/1024/1024) AS max_size_gb,
       autoextensible,
       increment_by,
       status
FROM   dba_data_files
ORDER BY file_name;

obj_lock.sql

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

-- File Name    : obj_lock.sql
-- Author       : AMIR DBA
-- Description  : Displays a list of locked objects.
-- Requirements : Access to the V$ views.
-- Call Syntax  : @obj_lock
-- -----------------------------------------------------------------------------------
SELECT a.type,
       Substr(a.owner,1,30) owner,
       a.sid,
       Substr(a.object,1,30) object
FROM   v$access a
WHERE  a.owner NOT IN ('SYS','PUBLIC')
ORDER BY 1,2,3,4
/

cache_hit_ratio.sql

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

-- File Name    : cache_hit_ratio.sql
-- Author       : AMIR DBA
-- Description  : Displays cache hit ratio for the database.
-- Comments     : The minimum figure of 89% is often quoted, but depending on the type of system this may not be possible.
-- Requirements : Access to the v$ views.
-- Call Syntax  : @cache_hit_ratio

-- -----------------------------------------------------------------------------------
PROMPT
PROMPT Hit ratio should exceed 89%

SELECT Sum(Decode(a.name, 'consistent gets', a.value, 0)) "Consistent Gets",
       Sum(Decode(a.name, 'db block gets', a.value, 0)) "DB Block Gets",
       Sum(Decode(a.name, 'physical reads', a.value, 0)) "Physical Reads",
       Round(((Sum(Decode(a.name, 'consistent gets', a.value, 0)) +
         Sum(Decode(a.name, 'db block gets', a.value, 0)) -
         Sum(Decode(a.name, 'physical reads', a.value, 0))  )/
           (Sum(Decode(a.name, 'consistent gets', a.value, 0)) +
             Sum(Decode(a.name, 'db block gets', a.value, 0))))
             *100,2) "Hit Ratio %"
FROM   v$sysstat a;

monitor.sql

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

-- File Name    : monitor.sql
-- Author       : AMIR DBA
-- Description  : Displays SQL statements for the current database sessions.
-- Requirements : Access to the V$ views.
-- Call Syntax  : @monitor
-- -----------------------------------------------------------------------------------
SET VERIFY OFF
SET LINESIZE 255
COL SID FORMAT 999
COL STATUS FORMAT A8
COL PROCESS FORMAT A10
COL SCHEMANAME FORMAT A16
COL OSUSER  FORMAT A16
COL SQL_TEXT FORMAT A120 HEADING 'SQL QUERY'
COL PROGRAM	FORMAT A30

SELECT s.sid,
       s.status,
       s.process,
       s.schemaname,
       s.osuser,
       a.sql_text,
       p.program
FROM   v$session s,
       v$sqlarea a,
       v$process p
WHERE  s.SQL_HASH_VALUE = a.HASH_VALUE
AND    s.SQL_ADDRESS = a.ADDRESS
AND    s.PADDR = p.ADDR
/

SET VERIFY ON
SET LINESIZE 255

active_sessions.sql

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

-- File Name    : active_sessions.sql
-- Author       : AMIR DBA
-- Description  : Displays information on all active database sessions.
-- Requirements : Access to the V$ views.
-- Call Syntax  : @active_sessions

-- -----------------------------------------------------------------------------------
SET LINESIZE 500
SET PAGESIZE 1000

COLUMN username FORMAT A30
COLUMN osuser FORMAT A20
COLUMN spid FORMAT A10
COLUMN service_name FORMAT A15
COLUMN module FORMAT A45
COLUMN machine FORMAT A30
COLUMN logon_time FORMAT A20

SELECT NVL(s.username, '(oracle)') AS username,
       s.osuser,
       s.sid,
       s.serial#,
       p.spid,
       s.lockwait,
       s.status,
       s.machine,
       s.program,
       TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time,
       s.last_call_et AS last_call_et_secs,
       s.module,
       s.action,
       s.client_info,
       s.client_identifier
FROM   v$session s,
       v$process p
WHERE  s.paddr  = p.addr
AND    s.status = 'ACTIVE'
ORDER BY s.username, s.osuser;

SET PAGESIZE 14