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.

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

Query for Concurrent List in Oracle R12

Hi,

Today, i want to post about concurrent listing query. It will be used to find concurrent name, with some additional field about executable name, executable path name. And also RTP template name, while the concurrent have related with XML publisher.