-- check_ebs_trace_profs.sql
-- Author: John Piwowar
-- Purpose: Identify E-Business Suite system profile option settings that may
-- be related to performance-degrading debug/trace activity
-- Notes: Prompts for a cutoff date for when profile options were set
-- May need additional tweaking for multi-language installations
set pagesize 9999
set linesize 120
set verify off
col "Profile Option" for a25
col "Option Level" for a13
col "set for" for a20
col "Value" for a20
col "Set On" for a11
col "Blame" for a20
PROMPT Enter date value in form DD-MON-YYYY for check_since
select tl.user_profile_option_name "Profile Option"
, decode( val.level_id
, 10001, 'Site'
, 10002, 'Application'
, 10003, 'Responsibility'
, 10004, 'User'
, 10005, 'Server'
, 10006, 'Organization'
, 10007, 'Server+Resp'
, 'No idea, boss') "Option Level"
, decode( val.level_id
, 10001
, 'EVERYWHERE!'
, 10002
, (select application_name
from fnd_application_tl
where application_id = val.level_value)
, 10003
, (select responsibility_name
from fnd_responsibility_tl
where responsibility_id = val.level_value
and application_id = val.level_value_application_id)
, 10004
, (select user_name
from fnd_user
where user_id = val.level_value)
, 10005
, (select host || '.' || domain
from fnd_nodes
where node_id = val.level_value)
, 10006
, (select name
from hr_all_organization_units
where organization_id = val.level_value)
, 10007
, 'Look it up' --per specification El-Ay-Zed-why
, '''Tis a mystery') "Set for"
, val.profile_option_value "Value"
, val.last_update_date "Set on"
, usr.user_name "Set By"
from fnd_profile_options opt,
fnd_profile_option_values val,
fnd_profile_options_tl tl,
fnd_user usr
where opt.profile_option_id = val.profile_option_id
and opt.profile_option_name = tl.profile_option_name
and regexp_like( tl.user_profile_option_name
, '(trace|log|debug|audit|diag|sql)'
, 'i'
)
and not(regexp_like( tl.user_profile_option_name
, '(catalog|file|login|utilities)'
, 'i'
)
)
and usr.user_id = val.last_updated_by
and usr.user_name not in ( 'AUTOINSTALL'
, 'INITIAL SETUP'
, 'ANONYMOUS')
and val.last_update_date > '&check_since'
order by val.last_update_date desc
;
-- Author: John Piwowar
-- Purpose: Identify E-Business Suite system profile option settings that may
-- be related to performance-degrading debug/trace activity
-- Notes: Prompts for a cutoff date for when profile options were set
-- May need additional tweaking for multi-language installations
set pagesize 9999
set linesize 120
set verify off
col "Profile Option" for a25
col "Option Level" for a13
col "set for" for a20
col "Value" for a20
col "Set On" for a11
col "Blame" for a20
PROMPT Enter date value in form DD-MON-YYYY for check_since
select tl.user_profile_option_name "Profile Option"
, decode( val.level_id
, 10001, 'Site'
, 10002, 'Application'
, 10003, 'Responsibility'
, 10004, 'User'
, 10005, 'Server'
, 10006, 'Organization'
, 10007, 'Server+Resp'
, 'No idea, boss') "Option Level"
, decode( val.level_id
, 10001
, 'EVERYWHERE!'
, 10002
, (select application_name
from fnd_application_tl
where application_id = val.level_value)
, 10003
, (select responsibility_name
from fnd_responsibility_tl
where responsibility_id = val.level_value
and application_id = val.level_value_application_id)
, 10004
, (select user_name
from fnd_user
where user_id = val.level_value)
, 10005
, (select host || '.' || domain
from fnd_nodes
where node_id = val.level_value)
, 10006
, (select name
from hr_all_organization_units
where organization_id = val.level_value)
, 10007
, 'Look it up' --per specification El-Ay-Zed-why
, '''Tis a mystery') "Set for"
, val.profile_option_value "Value"
, val.last_update_date "Set on"
, usr.user_name "Set By"
from fnd_profile_options opt,
fnd_profile_option_values val,
fnd_profile_options_tl tl,
fnd_user usr
where opt.profile_option_id = val.profile_option_id
and opt.profile_option_name = tl.profile_option_name
and regexp_like( tl.user_profile_option_name
, '(trace|log|debug|audit|diag|sql)'
, 'i'
)
and not(regexp_like( tl.user_profile_option_name
, '(catalog|file|login|utilities)'
, 'i'
)
)
and usr.user_id = val.last_updated_by
and usr.user_name not in ( 'AUTOINSTALL'
, 'INITIAL SETUP'
, 'ANONYMOUS')
and val.last_update_date > '&check_since'
order by val.last_update_date desc
;
No comments:
Post a Comment