Query To Find Complete Database Size SELECT ‘DATA_n_INDEX:’|| SUM(bytes)/1024/1024/1024|| ‘GBytes’ FULL_DATABASE_SIZEFROM dba_data_filesUNIONSELECT ‘TEMP:’|| SUM(bytes)/1024/1024/1024|| ‘GBytes’FROM dba_temp_filesUNIONSELECT ‘REDO LOGS:’|| SUM(bytes)/1024/1024/1024|| ‘GBytes’FROM v$logUNIONSELECT ‘CONTROLFILE:’|| SUM(file_size_blks*block_size)/1024/1024|| ‘MBytes’FROM v$controlfile; … Read more
Query To Find Application User Details SELECT user_name,user_id,Decode(employee_id, NULL, NULL,‘E’) employee_flag,description,start_date,end_date,Decode(end_date, Least(end_date, SYSDATE), NULL,‘+’) f,To_char(last_logon_date, ‘mm/dd/yy hh24:mi:ss’) last_logon_time,Decode(end_date, NULL, SYSDATE – last_logon_date) last_logon_days,Decode(end_date, NULL, Decode(30, … Read more
Query To Find EBS Profile Options For All Values SELECT p.profile_option_name SHORT_NAME,n.user_profile_option_name NAME,Decode(v.level_id, 10001, ‘Site’,10002, ‘Application’,10003, ‘Responsibility’,10004, ‘User’,10005, ‘Server’,10006, ‘Org’,10007, Decode(To_char(v.level_value2), ‘-1’,‘Responsibility’,Decode(To_char(v.level_value), ‘-1’, ‘Server’,‘Server+Resp’)),‘UnDef’) LEVEL_SET,Decode(To_char(v.level_id), … Read more
Query To Find Number Of Users Connected To EBS SELECT Count (DISTINCT d.user_name)FROM apps.fnd_logins a,v$session b,v$process c,apps.fnd_user dWHERE b.paddr = c.addrAND a.pid = c.pidAND a.spid … Read more
Query To Find Who is Connected When to Which Responsibilities SELECT DISTINCT fu.user_name User_Name,fr.responsibility_key ResponsibilityFROM fnd_user fu,fnd_responsibility fr,icx_sessions icWHERE fu.user_id = ic.user_idAND fr.responsibility_id = ic.responsibility_idAND … Read more
Query For Workflow Mailer Configurations SELECT p.parameter_id,p.parameter_name,v.parameter_value valueFROM apps.fnd_svc_comp_param_vals_v v,apps.fnd_svc_comp_params_b p,apps.fnd_svc_components cWHERE c.component_type = ‘WF_MAILER’AND v.component_id = c.component_idAND v.parameter_id = p.parameter_idAND p.parameter_name IN (‘OUTBOUND_SERVER’,’INBOUND_SERVER’,’ACCOUNT’,‘FROM’,‘NODENAME’,’REPLYTO’,’DISCARD’,’PROCESS’,‘INBOX’)ORDER BY … Read more
Query To Find All Responsibilities Assigned To a User SELECT UNIQUE u.user_id,Substr (u.user_name, 1, 30) user_name,Substr (r.responsibility_name, 1, 60) responsiblity,Substr (a.application_name, 1, 50) applicationFROM fnd_user … Read more
Query To Get All Users Assigned To Given Responsibility SELECT UNIQUE u.user_id,Substr(u.user_name, 1, 30) user_name,Substr(r.responsibility_name, 1, 60) responsibility,Substr(a.application_name, 1, 50) applicationFROM fnd_user u,find_user_resp_groups g,find_application_tl a,find_responsibility_tl … Read more
Query To Find All Reports Registered Within Given Application SELECT fa.application_short_name,fcpv.user_concurrent_program_name,description,Decode (fcpv.execution_method_code, ‘B’, ‘Request Set Stage Function’,‘Q’, ‘SQLPLUS’, ‘H’, ‘Host’, ‘L’, ‘SQLLOADER’,‘A’, ‘Spawned’,‘I’, ‘PL/SQL Stored … Read more
Query To Find Responsibility Name from a Concurrent Program SELECT DISTINCT FCPL.user_concurrent_program_name,FCP.concurrent_program_name,FAPP.application_name,FRG.request_group_name,FNRTL.responsibility_nameFROM apps.fnd_request_groups FRG,apps.fnd_application_tl FAPP,apps.fnd_request_group_units FRGU,apps.fnd_concurrent_programs FCP,apps.fnd_concurrent_programs_tl FCPL,apps.fnd_responsibility FNR,apps.fnd_responsibility_tl FNRTLWHERE FRG.application_id=fapp.application_idAND FRG.application_id=FRGU.application_idAND FRG.request_group_id=FRGU.request_group_idAND FRG.request_group_id=FNR.request_group_idAND FRG.application_id=FNR.application_idAND … Read more