Database Info

-- ##############################################################################
--     DBA SYSTEM / SESSION INFO
-- ##############################################################################

SELECT * FROM v$version;
SELECT * FROM v$database;
SELECT * FROM v$instance;
SELECT * FROM v$parameters;
SELECT * FROM global_name;
SELECT * FROM v$parameter ORDER BY NAME;
SELECT * FROM v$session s WHERE s.audsid = USERENV ('sessionid');

-- https://stackoverflow.com/questions/16565829/is-there-a-way-to-get-information-about-a-server-using-sql
-- https://oracle-base.com/articles/misc/identifying-host-names-and-addresses

-- #############################################################################
--        DATABASE DETAILS
-- #############################################################################

-- database version
SELECT * FROM v$version;

-- operating system
select rtrim(substr(replace(banner,'TNS for ',''),1,instr(replace(banner,'TNS for ',''),':')-1)) os
  from v$version
 where banner like 'TNS for %';

-- product details
SELECT * FROM product_component_version;

-- #############################################################################
--        INSTANCE DETAILS
-- #############################################################################

-- instance info (instance name, host name, version, startup time etc.)
SELECT * FROM v$instance;

-- about license limits of the current instance.
SELECT * FROM v$license;

-- #############################################################################
--        DATABASE NAME
-- #############################################################################

-- database name
SELECT * FROM GLOBAL_NAME;

-- database ip address
SELECT UTL_INADDR.get_host_address FROM dual;

--Db Host Name.
SELECT UTL_INADDR.GET_HOST_NAME('80.75.69.150') FROM dual;

-- #############################################################################
--        CLIENT INFO
-- #############################################################################

-- ip address of the client machine
SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') FROM dual;

-- operating system identifier for the current session. This is often the client machine name
SELECT SYS_CONTEXT('USERENV','TERMINAL') FROM dual;

-- host name of the client machine
SELECT SYS_CONTEXT('USERENV','HOST') FROM dual;

-- host name of the server running the database instance
SELECT SYS_CONTEXT('USERENV','SERVER_HOST') FROM dual;

DBA Nodes

-- ##############################################################################
--      DBA NODES
-- ##############################################################################

select
NODE_NAME,
to_char(CREATION_DATE, 'DD-MON-RR HH24:MI') creation_date,
PLATFORM_CODE,
decode(STATUS,'Y','ACTIVE','INACTIVE') Status,
decode(SUPPORT_CP,'Y', 'ConcMgr','No') ConcMgr,
decode(SUPPORT_FORMS,'Y','Forms', 'No') Forms,
decode(SUPPORT_WEB,'Y','Web', 'No') WebServer,
decode(SUPPORT_ADMIN, 'Y','Admin', 'No') Admin,
decode(SUPPORT_DB, 'Y','Rdbms', 'No') Database,
to_char(LAST_MONITORED_TIME, 'DD-MON-RR HH24:MI:SS') last_monitored,
NODE_MODE,
SERVER_ADDRESS,
HOST,
DOMAIN,
WEBHOST,
VIRTUAL_IP,
SERVER_ID
from fnd_nodes;

DBA Jobs and Errors and Stuff

-- ##############################################################################
--      DBA JOBS, ERRORS, STUFF
-- ##############################################################################

-- jobs
select * from sys.dba_jobs;
select * from sys.dba_jobs_running;
select * from sys.dba_scheduler_jobs;
select * from sys.dba_scheduler_running_jobs;
select * from applsys.fnd_tables where table_name = 'DBA_JOBS';

-- objects
SELECT * FROM all_objects WHERE object_name = 'AP_INVOICES_UTILITY_PKG';
SELECT object_type,owner||'.'||object_name FROM dba_objects WHERE status='INVALID';
SELECT text FROM dba_source WHERE name = 'AP_INVOICES_UTILITY_PKG' AND line = 2;

-- errors
SELECT line, text FROM dba_errors WHERE name = 'AP_INVOICES_UTILITY_PKG';
SELECT * FROM user_errors;
SELECT * FROM dba_errors;

User Session Info

-- ##############################################################################
--       USER SESSION INFO
--       http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/functions122a.htm
-- ##############################################################################

SELECT 'AUDITED_CURSORID', SYS_CONTEXT('USERENV', 'AUDITED_CURSORID') FROM DUAL UNION ALL
SELECT 'AUTHENTICATION_DATA', SYS_CONTEXT('USERENV', 'AUTHENTICATION_DATA') FROM DUAL UNION ALL
SELECT 'AUTHENTICATION_TYPE', SYS_CONTEXT('USERENV', 'AUTHENTICATION_TYPE') FROM DUAL UNION ALL
SELECT 'BG_JOB_ID', SYS_CONTEXT('USERENV', 'BG_JOB_ID') FROM DUAL UNION ALL
SELECT 'CLIENT_IDENTIFIER', SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER') FROM DUAL UNION ALL
SELECT 'CLIENT_INFO', SYS_CONTEXT('USERENV', 'CLIENT_INFO') FROM DUAL UNION ALL
SELECT 'CURRENT_SCHEMA', SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') FROM DUAL UNION ALL
SELECT 'CURRENT_SCHEMAID', SYS_CONTEXT('USERENV', 'CURRENT_SCHEMAID') FROM DUAL UNION ALL
SELECT 'CURRENT_SQL', SYS_CONTEXT('USERENV', 'CURRENT_SQL') FROM DUAL UNION ALL
SELECT 'CURRENT_USER', SYS_CONTEXT('USERENV', 'CURRENT_USER') FROM DUAL UNION ALL
SELECT 'CURRENT_USERID', SYS_CONTEXT('USERENV', 'CURRENT_USERID') FROM DUAL UNION ALL
SELECT 'DB_DOMAIN', SYS_CONTEXT('USERENV', 'DB_DOMAIN') FROM DUAL UNION ALL
SELECT 'DB_NAME', SYS_CONTEXT('USERENV', 'DB_NAME') FROM DUAL UNION ALL
SELECT 'EXTERNAL_NAME', SYS_CONTEXT('USERENV', 'EXTERNAL_NAME') FROM DUAL UNION ALL
SELECT 'FG_JOB_ID', SYS_CONTEXT('USERENV', 'FG_JOB_ID') FROM DUAL UNION ALL
SELECT 'GLOBAL_CONTEXT_MEMORY', SYS_CONTEXT('USERENV', 'GLOBAL_CONTEXT_MEMORY') FROM DUAL UNION ALL
SELECT 'HOST', SYS_CONTEXT('USERENV', 'HOST') FROM DUAL UNION ALL
SELECT 'INSTANCE', SYS_CONTEXT('USERENV', 'INSTANCE') FROM DUAL UNION ALL
SELECT 'IP_ADDRESS', SYS_CONTEXT('USERENV', 'IP_ADDRESS') FROM DUAL UNION ALL
SELECT 'ISDBA', SYS_CONTEXT('USERENV', 'ISDBA') FROM DUAL UNION ALL
SELECT 'LANG', SYS_CONTEXT('USERENV', 'LANG') FROM DUAL UNION ALL
SELECT 'LANGUAGE', SYS_CONTEXT('USERENV', 'LANGUAGE') FROM DUAL UNION ALL
SELECT 'NETWORK_PROTOCOL', SYS_CONTEXT('USERENV', 'NETWORK_PROTOCOL') FROM DUAL UNION ALL
SELECT 'NLS_CALENDAR', SYS_CONTEXT('USERENV', 'NLS_CALENDAR') FROM DUAL UNION ALL
SELECT 'NLS_CURRENCY', SYS_CONTEXT('USERENV', 'NLS_CURRENCY') FROM DUAL UNION ALL
SELECT 'NLS_DATE_FORMAT', SYS_CONTEXT('USERENV', 'NLS_DATE_FORMAT') FROM DUAL UNION ALL
SELECT 'NLS_DATE_LANGUAGE', SYS_CONTEXT('USERENV', 'NLS_DATE_LANGUAGE') FROM DUAL UNION ALL
SELECT 'NLS_SORT', SYS_CONTEXT('USERENV', 'NLS_SORT') FROM DUAL UNION ALL
SELECT 'NLS_TERRITORY', SYS_CONTEXT('USERENV', 'NLS_TERRITORY') FROM DUAL UNION ALL
SELECT 'OS_USER', SYS_CONTEXT('USERENV', 'OS_USER') FROM DUAL UNION ALL
SELECT 'PROXY_USER', SYS_CONTEXT('USERENV', 'PROXY_USER') FROM DUAL UNION ALL
SELECT 'PROXY_USERID', SYS_CONTEXT('USERENV', 'PROXY_USERID') FROM DUAL UNION ALL
SELECT 'SESSION_USER', SYS_CONTEXT('USERENV', 'SESSION_USER') FROM DUAL UNION ALL
SELECT 'SESSION_USERID', SYS_CONTEXT('USERENV', 'SESSION_USERID') FROM DUAL UNION ALL
SELECT 'SESSIONID', SYS_CONTEXT('USERENV', 'SESSIONID') FROM DUAL UNION ALL
SELECT 'TERMINAL', SYS_CONTEXT('USERENV', 'TERMINAL') FROM DUAL;

Invalid Objects

-- ##############################################################################
--       INVALID OBJECTS
--       http://www.oracle-base.com/articles/misc/recompiling-invalid-schema-objects.php
-- ##############################################################################

  SELECT owner
       , object_type
       , object_name
       , status
    FROM dba_objects
   WHERE status = 'INVALID'
ORDER BY owner
       , object_type
       , object_name;

Get EBS Version and Module Patch Levels

-- ##############################################################################
--     GET CURRENT EBS RELEASE VERSION
-- ##############################################################################

SELECT * FROM apps.fnd_product_groups;

-- ##############################################################################
--       DATABASE VERSION
-- ##############################################################################

SELECT SUBSTR (release_name, 1, 7) version
     , SUBSTR (RPAD (multi_org_flag, 2, ' '), 1, 2) multi_org_flag
     , SUBSTR (RPAD (multi_currency_flag, 3, ' '), 1, 3) multi_currency_flag
  FROM apps.fnd_product_groups;

-- ##############################################################################
--       PATCHSET LEVEL FOR MODULES
-- ##############################################################################

  SELECT app_short_name
       , MAX (patch_level)
    FROM applsys.ad_patch_driver_minipks
   WHERE app_short_name = 'AR'
GROUP BY app_short_name
ORDER BY app_short_name;

  SELECT *
    FROM applsys.ad_patch_driver_minipks
   WHERE app_short_name = 'SQLAP'
ORDER BY app_short_name
       , creation_date DESC; 

-- ##############################################################################
--         PRODUCT PATCH LEVEL 
-- ##############################################################################

  SELECT fa.application_short_name app
       , fat.application_name
       , fat.creation_date
       , fat.description
       , fpi.creation_date
       , fpi.product_version
       , fpi.status
       , fpi.patch_level
    FROM applsys.fnd_product_installations fpi
    JOIN applsys.fnd_application_tl fat ON fpi.application_id = fat.application_id 
    JOIN applsys.fnd_application fa     ON fa.application_id =  fat.application_id
   WHERE 1 = 1
     AND fa.application_short_name = 'CE'
--     AND fat.application_name = 'Projects'
ORDER BY 1;

-- applications / modules list and whether they are installed

SELECT  fa.application_id
     ,  fa.application_short_name
     ,  fa.creation_date
     ,  fa.basepath
     ,  fpi.patch_level
     ,  fa.product_code
     ,  fat.application_name
     ,  DECODE(fpi.status,'I','Licensed','S','Shared','N','Not Licensed') status
  FROM  applsys.fnd_application fa
  JOIN  applsys.fnd_application_tl fat        ON fa.application_id = fat.application_id
  JOIN  applsys.fnd_product_installations fpi ON fa.application_id = fpi.application_id
ORDER BY fat.application_name;

DBA Source

-- ##############################################################################
-- DBA SOURCE ETC
-- ##############################################################################

  SELECT owner,
         name,
         line,
         text
    FROM dba_source
   WHERE type = 'PACKAGE'
     AND owner = 'APPS'
     AND name LIKE 'XX%'
     AND lower(text) LIKE '%connect%'
ORDER BY line;

	select owner
	     , name
		 , type
		 , line
		 , (replace(replace(text,chr(10),''),chr(13),' ')) text 
	  from dba_source 
	 where (substrb(name,1,3) = 'PA_' OR dubstrb(name,1,4) = 'PJI_')
	   and line=2 and type in ('PACKAGE','PACKAGE BODY')
	order by name; 

Patch Installs

-- ##############################################################################
--     PATCH INSTALLS
-- ##############################################################################
	
-- simple summary
-- useful for pasting into an SR in plain-text format

    SELECT DISTINCT
           e.patch_name || '______' || d.patch_abstract 
      FROM applsys.ad_bugs a
      JOIN applsys.ad_patch_run_bugs b ON a.bug_id = b.bug_id
      JOIN applsys.ad_patch_runs c ON b.patch_run_id = c.patch_run_id
      JOIN applsys.ad_patch_drivers d ON c.patch_driver_id = d.patch_driver_id
      JOIN applsys.ad_applied_patches e ON d.applied_patch_id = e.applied_patch_id
     WHERE c.end_date > '10-JUN-2016';

 -- How to check if a patch has been applied with adpatch [ID 472820.1]
	 
  SELECT DISTINCT
         aap.patch_name patch
--       , ab.bug_number
--       , aprb.applied_flag
       , apr.end_date
       , aap.creation_date
       , apd.patch_abstract
    FROM applsys.ad_bugs ab
    JOIN applsys.ad_patch_run_bugs aprb ON ab.bug_id =            aprb.bug_id
    JOIN applsys.ad_patch_runs apr      ON apr.patch_run_id =     aprb.patch_run_id
    JOIN applsys.ad_patch_drivers apd   ON apd.patch_driver_id =  apr.patch_driver_id
    JOIN applsys.ad_applied_patches aap ON aap.applied_patch_id = apd.applied_patch_id
   WHERE 1 = 1
     AND ab.bug_number = '15874569'
--     AND aap.patch_name = '15874569'
--     AND aap.creation_date > '01-APR-2016'
     AND 1 = 1;

-- ##############################################################################
--      PATCH COUNTING
-- ##############################################################################
	 
-- count of bug fixes applied per month
	
	select to_char(creation_date, 'YYYY-MM') dd
	     , count(*) ct 
	  from ad_bugs
	 where creation_date > '01-JAN-2015'
  group by to_char(creation_date, 'YYYY-MM')
  order by to_char(creation_date, 'YYYY-MM') desc;

-- count of patches applied per month
	
	select to_char(creation_date, 'YYYY-MM') dd
	     , count(*) ct 
	  from ad_applied_patches
	 where creation_date > '01-JAN-2015'
  group by to_char(creation_date, 'YYYY-MM')
  order by to_char(creation_date, 'YYYY-MM') desc;

-- count of bugs included per patch install, since a patch can include many bug fixes
	
    SELECT e.patch_name
         , e.creation_date patch_created
         , count(*) bug_count
         , d.patch_abstract 
      FROM applsys.ad_bugs a
      JOIN applsys.ad_patch_run_bugs b ON a.bug_id = b.bug_id
      JOIN applsys.ad_patch_runs c ON b.patch_run_id = c.patch_run_id
      JOIN applsys.ad_patch_drivers d ON c.patch_driver_id = d.patch_driver_id
      JOIN applsys.ad_applied_patches e ON d.applied_patch_id = e.applied_patch_id
     WHERE c.end_date > '01-APR-2016' 
  GROUP BY e.patch_name
         , e.creation_date
         , d.patch_abstract 
  ORDER BY e.creation_date DESC;

-- ##############################################################################
--      RPC LEVEL
-- ##############################################################################

select distinct patch_name PatchNum,
decode(patch_name,
		'7303029','Oracle E-Business Suite Consolidated Upgrade Patch 1 (CUP1) for R12.1.1',
		'16791553','Oracle E-Business Suite Consolidated Upgrade Patch 2 (CUP2) for R12.1.1',
		'20203366','Oracle E-Business Suite Release 12.1.3+ Recommended Patch Collection 3 [RPC3]',
		'21236633','Oracle E-Business Suite Release 12.1.3+ Recommended Patch Collection 4 [RPC4]',
		'22644544','Oracle E-Business Suite Release 12.1.3+ Recommended Patch Collection 5 [RPC5]') PatchName
from (select patch_name from ad_applied_patches union
select bug_number from ad_bugs)
where patch_name in ('7303029', '16791553', '20203366', '21236633', '22644544')
order by patchnum desc;

File Versions

-- ##############################################################################
--     FILE VERSIONS 1
-- ##############################################################################

-- basic file version

    SELECT f.app_short_name app
         , f.filename
         , afv.version
         , afv.creation_date
      FROM applsys.ad_file_versions afv
      JOIN applsys.ad_files f ON afv.file_id = f.file_id
     WHERE 1 = 1
--       AND afv.version = '120.194.12010000.98'
       AND f.filename LIKE 'IGIRMINS%'
--       AND afv.creation_date > '10-MAY-2016'
       AND 1 = 1;

-- ##############################################################################
--     FILE VERSIONS 2
-- ##############################################################################

      SELECT f.filename
           , DECODE(f.app_short_name, 'DUMMY', NULL, f.app_short_name) product
           , DECODE(f.subdir, 'DUMMY', NULL, f.subdir) directory
           , at.name appltop
           , afv.version file_version
           , to_char(afv.translation_level) trans_level
           , aap.patch_name patch_id
           , pr.end_date run_date
        FROM applsys.ad_appl_tops at
   LEFT JOIN applsys.ad_patch_runs pr              ON at.appl_top_id =             pr.appl_top_id
   LEFT JOIN applsys.ad_patch_drivers pd           ON pr.patch_driver_id =         pd.patch_driver_id
   LEFT JOIN applsys.ad_applied_patches aap        ON pd.applied_patch_id =        aap.applied_patch_id
   LEFT JOIN applsys.ad_patch_run_bugs prb         ON prb.patch_run_id =           pr.patch_run_id
   LEFT JOIN applsys.ad_patch_run_bug_actions prba ON prba.patch_run_bug_id =      prb.patch_run_bug_id
        JOIN applsys.ad_file_versions afv          ON prba.patch_file_version_id = afv.file_version_id
        JOIN applsys.ad_files f                    ON f.file_id =                  prba.file_id
       WHERE 1 = 1
    --     AND prba.executed_flag = 'Y'
         AND f.filename LIKE 'CEXCABMR%'
         AND 1 = 1;

-- ##############################################################################
--     FILE VERSIONS 3
-- ##############################################################################

-- http://apurva-oracleappscrm.blogspot.com/2013/04/checking-file-versions-with-sql-query.html

    SELECT af.app_short_name
         , fat.application_name
         , af.subdir
         , af.filename
         , afv.version
         , afv.creation_date
      FROM applsys.ad_files af
      JOIN applsys.ad_file_versions afv     ON af.file_id =         afv.file_id
 LEFT JOIN applsys.fnd_application fa       ON af.app_short_name =  fa.application_short_name
 LEFT JOIN applsys.fnd_application_tl fat   ON fa.application_id =  fat.application_id
     WHERE 1 = 1
       AND afv.creation_date = (SELECT MAX (creation_date) FROM apps.ad_file_versions ver WHERE ver.file_id = af.file_id)
       AND af.FILENAME like 'CSTPPCCB%' 
--       AND af.app_short_name = 'HZ'  
       AND 1 = 1;

-- ##############################################################################
--     FILE VERSIONS 4
-- ##############################################################################

    SELECT f.filename
         , v.version
         , v.creation_date
         , f.subdir 
      FROM applsys.ad_files f
         , applsys.ad_file_versions v 
     WHERE f.file_id = v.file_id 
       AND f.filename LIKE 'partra%' 
--       AND version like '120.%' 
  ORDER BY v.creation_date
         , v.version; 

Concurrent Requests – Diagnostics

These are some hacky-bodgy and general all round heath-robinson type SQLs I messed about with to try and find the SQL linked to concurrent requests.

-- ##############################################################################
--     SQL ID & CURRENT SQL FOR RUNNING REQUESTS
-- ##############################################################################

    SELECT   fcr.request_id id
           , fcr.ORACLE_PROCESS_ID
           , fcp.concurrent_program_name job
           , NVL(fcr.description, fcpt.user_concurrent_program_name) job
           , DECODE (fcr.phase_code , 'P', 'Pending' , 'R', 'Running' , 'C', 'Complete' , 'I', 'Inactive') phase
           , DECODE (fcr.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
           , TO_CHAR(fcr.actual_start_date, 'Dy') day
           , TRUNC(fcr.actual_start_date) run_date
           , TO_CHAR((fcr.actual_start_date), 'HH24:MI:SS') start_
           , TO_CHAR((fcr.actual_completion_date), 'HH24:MI:SS') end_
           , CASE WHEN fcr.phase_code = 'R' AND fcr.actual_completion_date IS NULL THEN TRIM(REPLACE(REPLACE(TO_CHAR(numtodsinterval((SYSDATE-fcr.actual_start_date),'day')),'+000000000',''),'.000000000','')) ELSE TRIM(REPLACE(REPLACE(TO_CHAR(numtodsinterval((fcr.actual_completion_date-fcr.actual_start_date),'day')),'+000000000',''),'.000000000','')) END duration
           , (select sql_id || '____' || sql_text from v$sqlarea where sql_id = (select b.sql_id FROM v$process a, v$session b WHERE a.addr = b.paddr and a.spid = (select ORACLE_PROCESS_ID from applsys.fnd_concurrent_requests where REQUEST_ID = fcr.request_id))) sql_info
           , (select b.sid FROM v$process a, v$session b WHERE a.addr = b.paddr and a.spid = (select ORACLE_PROCESS_ID from applsys.fnd_concurrent_requests where REQUEST_ID = fcr.request_id)) sid          
        FROM applsys.fnd_concurrent_requests fcr 
        JOIN applsys.fnd_concurrent_programs_tl fcpt ON fcr.concurrent_program_id =  fcpt.concurrent_program_id
        JOIN applsys.fnd_concurrent_programs fcp     ON fcp.concurrent_program_id =  fcpt.concurrent_program_id
       WHERE 1 = 1
         AND fcr.phase_code = 'R'  -- running
         AND fcr.request_id = 21454094
    ORDER BY fcr.actual_start_date DESC;

-- ##############################################################################
--     RECENT V$SQLAREA ACTIVITY
-- ##############################################################################

    SELECT * 
      FROM v$sqlarea 
     WHERE last_active_time BETWEEN TO_DATE('09-MAY-2016 08:21:50', 'DD-MON-YYYY HH24:MI:SS') AND TO_DATE('09-MAY-2016 08:22:54', 'DD-MON-YYYY HH24:MI:SS')
       AND sql_text like '%GL_BALANC%'
  ORDER BY last_active_time DESC;

-- ##############################################################################
--     GET SQL DETAILS FOR A CONCURRENT REQUEST ID
-- ##############################################################################

select *
from v$sqlarea
where sql_id = (SELECT Ses.sql_id 
                  FROM v$session ses
                     , v$process pro 
                 WHERE ses.paddr = pro.addr 
                   AND pro.spid IN (SELECT oracle_process_id 
                                      FROM applsys.fnd_concurrent_requests 
                                     WHERE request_id IN (20527487)));