SQL Query to get Oracle Value Set definition

Query 1:

select b.flex_value_set_name,a.additional_where_clause from FND_FLEX_VALIDATION_TABLES a, fnd_flex_value_sets b
where a.application_table_name = 'MTL_SECONDARY_INVENTORIES'
and a.value_column_name ='SECONDARY_INVENTORY_NAME'
and a.flex_value_set_id = b.flex_value_set_id



Query 2:

SELECT ffvs.flex_value_set_id ,
       ffvs.flex_value_set_name ,
       ffvs.description set_description ,
       ffvs.validation_type,
       ffvt.value_column_name ,
       ffvt.meaning_column_name ,
       ffvt.id_column_name ,
       ffvt.application_table_name ,
       ffvt.additional_where_clause
FROM   APPS.fND_FLEX_VALUE_SETS FFVS ,
       APPS.FND_FLEX_VALIDATION_TABLES FFVT
WHERE  ffvs.flex_value_set_id = ffvt.flex_value_set_id
   AND ffvs.flex_value_set_name IN ('VALUE SET NAME')

Generate Spool for multiple db objects / How to generate Spool for multiple DB objects in oracle

SET PAGES 0

SET TIMING   OFF
SET ECHO     OFF
SET FEEDBACK OFF

SET TERMOUT  OFF

SPOOL C:\Emirates\Assignments\views\manyspools_8.sql


SELECT 'SPOOL C:\Emirates\Assignments\objects\' || object_name || '.sql' || CHR (10) ||
       'SELECT text from dba_source WHERE name = ''' || object_name || ''';' || CHR (10) ||
       'SPOOL OFF'
  from xxtemp_hr_stg where object_type in ('PROCEDURE','PACKAGE','PACKAGE BODY','FUNCTION');

SPOOL OFF

@ C:\Emirates\Assignments\objects\manyspools_8.sql

SET TERMOUT ON

--Note:

-- This script can run from SQL Developer / TOAD / PLSQL Developer
-- In above script, instead of table "dba_source", need to use dba_views for views

--My stage table: where my list of objects stored in stage table

--xxtemp_hr_stg

--Table Script:

create table xxtemp_hr_stg

(

 SNO NUMBER,

 OWNER VARCHAR2(10),

 OBJECT_NAME VARCHAR2(100),

 OBJECT_TYPE VARCHAR2(20),

 REMARKS VARCHAR2(2000),

 MODIFY_YN VARCHAR2(2000),

 ADDITIONAL_COMMENTS VARCHAR2(4000)

);

Generate Spool file for Single (particular DB Object) / How to generate the spool for db objects in oracle

set feedback off
set heading off
set timing off
set termout off
set linesize 1000
set trimspool on
set verify off
spool C:\YADUL\FILES_220519\XX_DB_OBJECT_NAME.sql
prompt set define off
select text
    from dba_source
    where name = upper('XX_DB_OBJECT_NAME')
    order by type, line;
prompt /
prompt set define on
spool off
set feedback on
set heading on
set termout on
set linesize 100
set timing on

--Note:
-- This script can run from SQL Developer / TOAD / PLSQL Developer
-- In above script, instead of this table "dba_source", need to user dba_views for views

SQL Query to find Responsibility for given Request group / Find Request group attached responsibilities

SELECT responsibility_name ,
  request_group_name        ,
  frg.description
   FROM fnd_request_groups frg,
  fnd_responsibility_vl frv
  WHERE frv.request_group_id = frg.request_group_id
AND request_group_name    LIKE 'Request group name'
ORDER BY responsibility_name;

SQL Query to find Concurrent program details with Parameters, status, submitted date and responsibility

SELECT
    cpt.user_concurrent_program_name   "Concurrent Program Name",
    decode(rgu.request_unit_type, 'P', 'Program', 'S', 'Set',
           rgu.request_unit_type) "Unit Type",
    fcr.status_code                    status,
    fcr.request_date                   prog_submitted_date,
    fnr.responsibility_name,
    fcr.argument1
    || '-'
    || fcr.argument2
    || '-'
    || fcr.argument3
    || '-'
    || fcr.argument4
    || '-'
    || fcr.argument5
    || '-'
    || fcr.argument6
    || '-'
    || fcr.argument7
    || '-'
    || fcr.argument8
    || '-'
    || fcr.argument9
    || '-'
    || fcr.argument10
    || '-'
    || fcr.argument11
    || '-'
    || fcr.argument12
    || '-'
    || fcr.argument13
    || '-'
    || fcr.argument14
    || '-'
    || fcr.argument15 parameters,
    cp.concurrent_program_name         "Concurrent Program Short Name",
    rg.application_id                  "Application ID",
    rg.request_group_name              "Request Group Name",
    fat.application_name               "Application Name",
    fa.application_short_name          "Application Short Name",
    fa.basepath                        "Basepath",
    cpt.concurrent_program_id
FROM
    fnd_request_groups           rg,
    fnd_request_group_units      rgu,
    fnd_concurrent_programs      cp,
    fnd_concurrent_programs_tl   cpt,
    fnd_application              fa,
    fnd_application_tl           fat--,
    ,
    fnd_concurrent_requests      fcr,
    fnd_responsibility_tl        fnr
WHERE
    rg.request_group_id = rgu.request_group_id
    AND rgu.request_unit_id = cp.concurrent_program_id
    AND cp.concurrent_program_id = cpt.concurrent_program_id
    AND rg.application_id = fat.application_id
    AND fa.application_id = fat.application_id
    AND cpt.language = userenv('LANG')
    AND fat.language = userenv('LANG')
    AND cpt.concurrent_program_id = fcr.concurrent_program_id
    AND fcr.responsibility_id = fnr.responsibility_id
    AND cpt.user_concurrent_program_name = 'Concurrent program name'
ORDER BY
    fcr.request_date DESC

How to find password of a User in Oracle Apps R12

--Package Specification

CREATE OR REPLACE PACKAGE get_pwd

AS

   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)

      RETURN VARCHAR2;

END get_pwd;

/

--Package Body
CREATE OR REPLACE PACKAGE BODY get_pwd
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2
   AS
      LANGUAGE JAVA
      NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';

END get_pwd;
/

--Query to execute
SELECT usr.user_name,
       get_pwd.decrypt
          ((SELECT (SELECT get_pwd.decrypt
                              (fnd_web_sec.get_guest_username_pwd,
                               usertable.encrypted_foundation_password
                              )
                      FROM DUAL) AS apps_password
              FROM fnd_user usertable
             WHERE usertable.user_name =
                      (SELECT SUBSTR
                                  (fnd_web_sec.get_guest_username_pwd,
                                   1,
                                     INSTR
                                          (fnd_web_sec.get_guest_username_pwd,
                                           '/'
                                          )
                                   - 1
                                  )
                         FROM DUAL)),
           usr.encrypted_user_password
          ) PASSWORD
  FROM fnd_user usr
 WHERE usr.user_name = '&USER_NAME';

How to find patches applied/installed on Oracle database

SELECT
e.patch_name,
c.end_date,
a.bug_number,
b.applied_flag
FROM
ad_bugs a,
ad_patch_run_bugs b,
ad_patch_runs c,
ad_patch_drivers d ,
ad_applied_patches e
WHERE
AND a.bug_id = b.bug_id
AND b.patch_run_id = c.patch_run_id
AND c.patch_driver_id = d.patch_driver_id

AND d.applied_patch_id = e.applied_patch_id

For Oracle 11g Release:

1. using Opatch utility

cd $ORACLE_HOME/OPatch

opatch lsinventory

2.  select * from sys.registry$history;



For Oracle 12c Release:

1. Opatch utility

cd $ORACLE_HOME/OPatch

opatch lsinventory

2. using dba_registry_sqlpatch view:

SQL> select * from dba_registry_sqlpatch;

3. using package dbms_qopatch

SQL> set serverout on

SQL> exec dbms_qopatch.get_sqlpatch_status;