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 b.data_object_id
         , d.text 
      from all_objects b
         , all_source d 
     where b.object_name = d.name 
       and b.object_name = 'BOB_HOPE_JOKE_PACKAGE' 
  order by b.last_ddl_time desc;

	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; 

Session Monitor

I’m not pretending to be a DBA, these are just some SQLs I used to run when I was working somewhere when there were problems with discoverer jobs running slowly.

-- ##############################################################################
--      SESSION MONITOR 1
--      http://stackoverflow.com/questions/55899/how-to-see-the-actual-oracle-sql-statement-that-is-being-executed
-- ############################################################################*/

 SELECT module,
		action,
		disco_sched,
		disco_submitted,
		disco_user,
		username, 
		disk_reads_per_exec, 
		sql_text,
		buffer_gets, 
		disk_reads, 
		parse_calls, 
		sorts, 
		executions, 
		rows_processed, 
		hit_ratio, 
		first_load_time, 
		last_load_time,
		last_active_time,
		sharable_mem, 
		persistent_mem, 
		runtime_mem, 
		cpu_time, 
		elapsed_time, 
		address, 
		hash_value 
   FROM (SELECT module,
				action,
				CASE WHEN (INSTR(sql_text, 'EUL') > 1)then 'Y' end disco_sched,
				CASE WHEN module LIKE 'Disco%' then 'Y' end disco_submitted,
				CASE WHEN module LIKE 'Disco10,%:%' THEN
				(SELECT description || ' (' || SUBSTR(module, INSTR(module, ':')+1, 200) || ')' from applsys.fnd_user fu WHERE user_name = (SUBSTR(module, INSTR(module, ',') + 2, (INSTR(module, ':') - INSTR(module, ',')-2))))
				END disco_user,
				u.username , 
				round((s.disk_reads/decode(s.executions,0,1, s.executions)),2)  disk_reads_per_exec, 
				sql_text,
				s.disk_reads , 
				s.buffer_gets , 
				s.parse_calls , 
				s.sorts , 
				s.executions , 
				s.rows_processed , 
				100 - round(100 *  s.disk_reads/greatest(s.buffer_gets,1),2) hit_ratio, 
				s.first_load_time , 
				s.last_load_time,
				s.last_active_time,
				sharable_mem , 
				persistent_mem , 
				runtime_mem, 
				cpu_time, 
				elapsed_time, 
				address, 
				hash_value 
		   FROM sys.v_$sql s, 
		        sys.all_users u 
		  WHERE s.parsing_user_id=u.user_id 
--			and UPPER(u.username) not in ('SYS','SYSTEM') 
--			AND module LIKE 'Tv_$sqltextOAD%'
--			AND sql_text NOT LIKE '%EUL%'
		ORDER BY 7 desc) 
  WHERE rownum <= 20;

-- ##############################################################################
--      SESSION MONITOR 2
-- ############################################################################*/

SELECT nvl(ses.username,'ORACLE PROC')||' ('||ses.sid||')' USERNAME,
       SID,   
       MACHINE, 
       MODULE,
       PROGRAM,
--       REPLACE(SQL.SQL_TEXT,CHR(10),'') STMT, 
      ltrim(to_char(floor(SES.LAST_CALL_ET/3600), '09')) || ':'
       || ltrim(to_char(floor(mod(SES.LAST_CALL_ET, 3600)/60), '09')) || ':'
       || ltrim(to_char(mod(SES.LAST_CALL_ET, 60), '09'))    RUNT 
  FROM V$SESSION SES,   
       V$SQLtext_with_newlines SQL 
 where SES.STATUS = 'ACTIVE'
   and SES.USERNAME is not null
   and SES.SQL_ADDRESS    = SQL.ADDRESS 
   and SES.SQL_HASH_VALUE = SQL.HASH_VALUE 
   and Ses.AUDSID <> userenv('SESSIONID') 
 order by runt desc, 1,sql.piece;