Query List of Profile Options Oracle R12

/* Formatted on 2/26/2016 9:38:27 AM (QP5 v5.163.1008.3004) */
  SELECT b.user_profile_option_name "Long Name",
         a.profile_option_name "Short Name",
         DECODE (TO_CHAR (c.level_id),
                 '10001', 'Site',
                 '10002', 'Application',
                 '10003', 'Responsibility',
                 '10004', 'User',
                 'Unknown')
            "Level",
         DECODE (
            TO_CHAR (c.level_id),
            '10001', 'Site',
            '10002', NVL (h.application_short_name, TO_CHAR (c.level_value)),
            '10003', NVL (g.responsibility_name, TO_CHAR (c.level_value)),
            '10004', NVL (e.user_name, TO_CHAR (c.level_value)),
            'Unknown')
            "Level Value",
         c.PROFILE_OPTION_VALUE "Profile Value",
         c.profile_option_id "Profile ID",
         TO_CHAR (c.LAST_UPDATE_DATE, 'DD-MON-YYYY HH24:MI') "Updated Date",
         NVL (d.user_name, TO_CHAR (c.last_updated_by)) "Updated By"
    FROM apps.fnd_profile_options a,
         apps.FND_PROFILE_OPTIONS_VL b,
         apps.FND_PROFILE_OPTION_VALUES c,
         apps.FND_USER d,
         apps.FND_USER e,
         apps.FND_RESPONSIBILITY_VL g,
         apps.FND_APPLICATION h
   WHERE     1 = 1
         AND a.profile_option_name = b.profile_option_name
         AND a.profile_option_id = c.profile_option_id
         AND a.application_id = c.application_id
         AND c.last_updated_by = d.user_id(+)
         AND c.level_value = e.user_id(+)
         AND c.level_value = g.responsibility_id(+)
         AND c.level_value = h.application_id(+)
         AND UPPER(b.user_profile_option_name) LIKE '%'||:P_PROFILE_NAME||'%'
ORDER BY b.user_profile_option_name,
         c.level_id,
         DECODE (
            TO_CHAR (c.level_id),
            '10001', 'Site',
            '10002', NVL (h.application_short_name, TO_CHAR (c.level_value)),
            '10003', NVL (g.responsibility_name, TO_CHAR (c.level_value)),
            '10004', NVL (e.user_name, TO_CHAR (c.level_value)),
            'Unknown');
Advertisement

Query List Applied Patches – Oracle EBS

Hi,

This query can be used to provide datalist of applied patches in Oracle E-Business Suite.
Check this out.

Applied patches 2

select aap.applied_patch_id,aap.patch_name,
aprb.application_short_name as AFFECTED_MODULE,
apd.driver_file_name,apd.patch_abstract,
apr.patch_top,
apr.patch_action_options
from ad_applied_patches aap,
ad_patch_drivers apd,
ad_patch_runs apr,
ad_patch_run_bugs aprb
where aap.applied_patch_id = apd.applied_patch_id
and apd.patch_driver_id = apr.patch_driver_id
and apr.patch_run_id = aprb.patch_run_id
and aprb.applied_flag = 'Y'
order by aap.applied_patch_id desc;

Reference : Get Oracle patches details, oracle-apps-dba.blogspot.co.id

Monitoring Query for User, Session and Object

--Query for User Monitoring, with information Employee name, User name, Module Accessed
select 
vs.logon_time,
vs.module,
vs.sid,vs.serial#,
vs.client_identifier,
(
select last_name from per_people_f
where person_id in (
    select employee_id from fnd_user 
    where user_name = vs.client_identifier
    )
    and rownum=1
) employee_last_name,
vs.PLSQL_ENTRY_OBJECT_ID,vs.ROW_WAIT_BLOCK#,
vs.BLOCKING_SESSION_STATUS,vs.STATE,
vs.time_remaining_micro,
vs.sql_exec_start,
vs.*
 from v$session vs
where 1=1
and module like '%WMS%' --FNDSCSGN%'
order by vs.logon_time desc
;

Based on that query, we can kill session with the script below :
–ALTER SYSTEM KILL SESSION ‘sid,serial#’;
ALTER SYSTEM KILL SESSION ‘7518,25661’ IMMEDIATE;

And for more information, there are some script to do object monitoring:

--Query for Object Monitoring
select * from dba_objects
where status = 'INVALID';

--Query for Check Employee Id
select employee_id from fnd_user 
where user_name = :p_user_name;

--Query for Check Employee Name based on User Name
select last_name from per_people_f
where person_id in (
    select employee_id from fnd_user 
    where user_name = :p_user_name
);

--Query for Monitoring Session I/O and its user
select 
vs.sid,
vs.serial#,
vs.*
 from v$sess_io vi,v$session vs
where 1=1
and vi.sid = vs.sid
and vs.module like '%WMS%';

--Query for Locked Object Monitoring
select 
ao.object_name,
vo.* 
from V$LOCKED_OBJECT vo,
dba_objects ao
where vo.object_id = ao.object_id
;

Reference : PSOUG.ORG – View Locked Object in Oracle