Below are some of the useful scripts related to Oracle Applications database
–FIND DATABASE SIZE
select 'DATA_n_INDEX: ' || sum(bytes)/1024/1024/1024 || ' GBytes' DATABASE_SIZE from dba_data_files union select 'TEMP: ' || sum(bytes)/1024/1024/1024 || ' GBytes' from dba_temp_files union select 'REDO LOGS: ' || sum(bytes)/1024/1024/1024 || ' GBytes' from v$log union select 'CONTROLFILE: ' || sum(FILE_SIZE_BLKS*BLOCK_SIZE)/1024/1024 || ' MBytes' from v$controlfile;
–CREATE NEW DATABASE USER
create user <username> identified by <password> default tablespace <tablespace_name> temporary tablespace <tablespace_name> profile <profile_name>; grant create session to <username>; alter user <username> password expire;
–CREATE DYNAMIC SQL SCRIPTS TO COMPILE INVALID OBJECTS
spool recompile_list.sql; select 'ALTER '|| decode(substr(object_type,1,4),'PACK','PACKAGE ',object_Type||' ')|| owner || '.' || decode(object_type,'JAVA CLASS','"') || object_name || decode(object_type,'JAVA CLASS','" RESOLVE ','INDEX','REBUILD',' COMPILE ') || DECODE(object_type,'PACKAGE BODY','BODY', NULL)||';' OBJECTS_TO_COMPILE from dba_objects where object_type in ('PACKAGE','PROCEDURE','PACKAGE BODY','FUNCTION', 'TRIGGER','VIEW','JAVA CLASS','INDEX') and status = 'INVALID' order by object_name, object_type; spool off;
–FIND ALL ACTIVE DATABASE SESSIONS
select event, program, module, sid, SECONDS_IN_WAIT from v$session where status = 'ACTIVE' and module is not null order by 2 desc;
OR
select sid, serial#, username, osuser, logon_time, program, module from v$session where username is not null and status = 'ACTIVE'
–FIND EBS USER NAME FROM DATABASE SESSION ID
column "User Name" format a10 column "OS_PID" format a8 select d.user_name "User Name", b.sid SID,b.serial# "Serial#", c.spid "srvPID", a.SPID "OS_PID", to_char(START_TIME,'DD-MON-YY HH:MM:SS') "STime" from fnd_logins a, v$session b, v$process c, fnd_user d where b.paddr = c.addr and a.pid=c.pid and a.spid = b.process and d.user_id = a.user_id and (d.user_name = 'USER_NAME' OR 1=1) and b.sid = &sid;
–FIND ORPHAN PROCESSES
select spid from v$process where not exists (select 1 from v$session where paddr=addr)
–FIND DETAILS WHEN CONCURRENT PROGRAM WAS RUN IN LAST 10 DAYS
select REQUEST_ID, REQUEST_DATE, REQUESTED_START_DATE, RESUBMIT_END_DATE, ACTUAL_START_DATE, ACTUAL_COMPLETION_DATE from fnd_concurrent_requests where CONCURRENT_PROGRAM_ID = &Concurrent_Prog_ID and ACTUAL_START_DATE between trunc(sysdate) - 10 and sysdate order by REQUESTED_START_DATE;
–FIND ALL FORMS RELATED SESSIONS
col CLIENT_IDENTIFIER format a10 col MODULE format a25 col MACHINE format a10 select sid, serial#, logon_time, client_identifier, module, status, machine, seconds_in_wait from gv$session where program like 'frmweb%' order by logon_time;
–FIND USER_ID FROM USER_NAME
select USER_ID, USER_NAME from apps.FND_USER where USER_NAME = upper('&1');
–FIND EBS USER CONNECTION RELATED DETAILS FROM USER_ID
select SESSION_ID, CREATION_DATE, TIME_OUT, LIMIT_TIME, LIMIT_CONNECTS, COUNTER, FIRST_CONNECT, LAST_CONNECT from apps.ICX_SESSIONS where USER_ID = &user_id order by CREATION_DATE;
–FIND EBS USER CONNECTION RELATED DETAILS FROM SESSION_ID
SELECT SESSION_ID, XSID, TIME_OUT, LIMIT_TIME, LIMIT_CONNECTS, COUNTER, TO_CHAR(SYSDATE,'DD-MM-YYYY HH24:MI:SS'), TO_CHAR(FIRST_CONNECT,'DD-MM-YYYY HH24:MI:SS'),TO_CHAR(LAST_CONNECT,'DD-MM-YYYY HH24:MI:SS'), DISABLED_FLAG FROM apps.ICX_SESSIONS WHERE SESSION_ID = '&session_id';
–FIND PID,SPID FROM SID
select p.PID,p.SPID,s.SID from v$process p,v$session s where s.paddr = p.addr and s.sid = &sid;
–FIND WORKFLOW MAIL PREFRENCES FROM DISLAY_NAME
col DISPLAY_NAMEformata30 col name format a30 set lines 132 select NAME, DISPLAY_NAME, NOTIFICATION_PREFERENCE from wf_users where DISPLAY_NAME like upper('%&NAME%');
–FIND ORACLE DATABASE HIDDEN PARAMETER VALUE
set lines 132 column ksppinm format a50 column ksppstvl format a30 select a.ksppinm, b.ksppstvl from x$ksppi a,x$ksppsv b where a.indx=b.indx and substr(a.ksppinm,1,1) = '_' and lower(a.ksppinm) like '%&hidden_parameter%' order by ksppinm;
–FIND WORKFLOW NAME AND DISPLAY_NAME FROM EMAIL ADDRESS
select name, display_name from apps.WF_LOCAL_ROLES where EMAIL_ADDRESS = '<email_address>';
–FIND EBS APPLICATION USER DETAILS FROM SID
rem to_char(max(l.start_time),'mm/dd/yy hh:mi:ssAM') startedat, set lines 132 set verify off col user_name head "Apps Signon" format a12 trunc col description head "Real Name" format a25 trunc rem col user_form_name head "Form Name" format a30 trunc col forminfo head "Form Name" format a40 trunc col time head "Elapsed Time" format a10 col zoom_level head "Zoom Level" col startedat head "Logon At" format a19 col lastcallet format a11 accept trgtsid number prompt 'What is the SID : ' select /*+ rule */ to_char(s.logon_time,'mm/dd/yy hh:mi:ssAM') startedat, a.time, floor(s.last_call_et/3600)||':'|| floor(mod(s.last_call_et,3600)/60)||':'|| mod(mod(s.last_call_et,3600),60) "LastCallET", u.user_name, u.description , s.module || ' - ' || a.user_form_name forminfo from applsys.fnd_logins l, applsys.fnd_user u, apps.fnd_signon_audit_view a, v$process p, v$session s where s.sid = &trgtsid and s.paddr = p.addr and p.pid = l.pid and l.end_time is null and l.spid = s.process and l.start_time is not null -- and l.start_time = u.last_logon_date -- and l.session_number = u.session_number and l.user_id = u.user_id and u.user_id = a.user_id and p.pid = a.pid and l.start_time = (select max(l2.start_time) from applsys.fnd_logins l2 where l2.pid = l.pid) group by to_char(s.logon_time,'mm/dd/yy hh:mi:ssAM'), floor(s.last_call_et/3600)||':'|| floor(mod(s.last_call_et,3600)/60)||':'|| mod(mod(s.last_call_et,3600),60), u.user_name, u.description,a.time,s.module || ' - ' || a.user_form_name order by to_char(s.logon_time,'mm/dd/yy hh:mi:ssAM'),a.time;
–FIND VALUE OF PROFILE OPTION FROM DATABASE
set serveroutput on set echo on set timing on set feedback on set long 10000 set linesize 120 set pagesize 132 column SHORT_NAME format A30 column NAME format A40 column LEVEL_SET format a15 column CONTEXT format a30 column VALUE format A60 wrap 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', 10007, 'SERVRESP', 'UnDef') LEVEL_SET, decode(to_char(v.level_id), '10001', '', '10002', app.application_short_name, '10003', rsp.responsibility_key, '10005', svr.node_name, '10006', org.name, '10004', usr.user_name, '10007', 'Serv/resp', 'UnDef') "CONTEXT", v.profile_option_value VALUE from fnd_profile_options p, fnd_profile_option_values v, fnd_profile_options_tl n, fnd_user usr, fnd_application app, fnd_responsibility rsp, fnd_nodes svr, hr_operating_units org where p.profile_option_id = v.profile_option_id (+) and p.profile_option_name = n.profile_option_name and (upper(n.user_profile_option_name) like upper('&user_rofile_name') or upper(n.user_profile_option_name) like upper('&user_profle_name')) and usr.user_id (+) = v.level_value and rsp.application_id (+) = v.level_value_application_id and rsp.responsibility_id (+) = v.level_value and app.application_id (+) = v.level_value and svr.node_id (+) = v.level_value and org.organization_id (+) = v.level_value order by short_name, level_set;
–FIND USER DETAILS FROM USER_NAME
select USER_GUID,START_DATE,END_DATE,LAST_LOGON_DATE,EMAIL_ADDRESS from fnd_user where user_name = '&USER_NAME';
–FIND ALL RUNNING CONCURRENT REQUESTS WHICH ARE CURRENTLY RUNNING
set linesize 120 col program for a34 col requestor for a9 SELECT REQUEST_ID "Req. ID", to_char(REQUESTED_START_DATE,'YYYY-MM-DD HH24:MI:SS') "REQ START DATE", to_char(ACTUAL_START_DATE,'YYYY-MM-DD HH24:MI:SS') "ACT START DATE", to_char(ACTUAL_COMPLETION_DATE,'YYYY-MM-DD HH24:MI:SS') "ACT COMP DATE", PHASE_CODE "P-CODE",STATUS_CODE "S-CODE", PROGRAM, REQUESTOR FROM APPS.FND_CONC_REQ_SUMMARY_V WHERE PHASE_CODE = 'R' order by actual_start_date DESC;
– UPDATE PROFILE OPTION FROM BACKEND
DECLARE stat boolean; BEGIN dbms_output.disable; dbms_output.enable(100000); stat := FND_PROFILE.SAVE('XX_PROFILE_NAME', 'XX_NEW_VALUE', 'SITE'); IF stat THEN dbms_output.put_line( 'Stat = TRUE - profile updated' ); ELSE dbms_output.put_line( 'Stat = FALSE - profile NOT updated' ); END IF; commit; END;
— APPLICATION USER DEFINITION
rem fnduser.sql rem set linesize 132 rem ttitle 'Application User Definitions' rem col user_name format a15 heading 'USER NAME' col user_id format 99999 heading 'USER|ID' col employee_flag format a1 heading 'E' col description format a35 heading 'DESCRIPTION' col start_date format a9 heading 'START|DATE' col end_date format a9 heading 'END|DATE' col last_logon_date format a9 heading 'LAST|LOGON|DATE' col last_logon_time format a17 heading 'LAST LOGON|TIMESTAMP' col last_logon_days format 9999 heading 'LAST|LOGON|DAYS' col f format a1 heading 'F' col p format a1 heading 'P' col password_lifespan_days format 9999 heading 'PW|EXPIRE|DAYS' col password_date format a9 heading 'PASSWORD|DATE' col password_change_days format 9999 heading 'PW|CHANGE|DAYS' rem break on report compute count of start_date end_date last_logon_time last_logon_days on report rem 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, greatest( 30, trunc(sysdate-last_logon_date) ), null, '*' ) ) p, password_lifespan_days, password_date, decode( end_date, null, sysdate - password_date ) password_change_days from applsys.fnd_user u where user_name like upper('%&userid%') order by description, user_name; rem set linesize 80
–LONG RUNNING EBS FORM SESSION
rem long_run_form.sql rem set linesize 132 set pagesize 60 set tab off rem ttitle 'Long Running Active Form Sessions' rem col email_address format a26 heading 'EMAIL ADDRESS' col user_name format a10 heading 'USER NAME' col pid format 9999 heading 'PID' col spid format a6 heading 'SERVER|PID' col inst_id format 9 heading 'I' col sid format 9999 heading 'SID' col serial# format 99999 heading 'SERIAL' col process format a6 heading 'CLIENT|PID' col osuser format a8 heading 'OS|USERNAME' col log_per_sec format 99999 heading 'LOG|PER|SEC' col logical format b999999999 heading 'LOGICAL|READS' col phy_per_sec format b9999 heading 'PHY|PER|SEC' col physical_reads format b99999999 heading 'PHYSICAL|READS' col audsid format b9999999 heading 'AUDIT|SESSION' col program format a30 heading 'PROGRAM NAME' col module format a12 heading 'FORM NAME' col logon_time format a8 heading 'LOGON|TIME' col duration format a8 heading 'DURATION' col last_call_min format 999 heading 'LAST|CALL|MIN' col orcl_user format a6 heading 'ORACLE|USERID' col status format a1 heading 'S' col enqueue format a1 heading 'E' rem select distinct user_name, decode( s.status, 'ACTIVE', '*', 'INACTIVE', null, 'KILLED', 'K', '?' ) status, decode( s.lockwait, null, null, 'E' ) enqueue, s.last_call_et/60 last_call_min, s.module, s.inst_id, s.sid, s.serial#, -- s.username orcl_usr, -- s.osuser osuser, s.process, p.spid, to_char( trunc(sysdate) + ( sysdate - s.logon_time ), 'hh24:mi:ss' ) duration, ( i.block_gets + i.consistent_gets ) / ( ( sysdate - s.logon_time ) * 86400 ) log_per_sec, i.block_gets + i.consistent_gets logical, physical_reads / ( ( sysdate - s.logon_time ) * 86400 ) phy_per_sec, i.physical_reads, -- s.action, email_address from applsys.fnd_logins l, applsys.fnd_user u, gv$session s, gv$sess_io i, gv$process p where l.user_id = u.user_id and s.sid = i.sid and s.inst_id = i.inst_id and p.pid = l.pid -- and s.process = l.spid and p.spid = l.process_spid(+) and l.end_time is null and s.paddr = p.addr(+) and s.inst_id = p.inst_id(+) and ( s.module in ( 'FNDATTCH', 'FNDSCSGN' ) or substr( s.action, 1, 5 ) = 'FRM::' ) and s.last_call_et >= 300 and s.status in ( 'ACTIVE', 'KILLED' ) order by last_call_min desc; rem set linesize 80 set pagesize 24 set tab on
—FIND WHICH RESPONSIBILITIES CAN RUN SPECIFIC PROGRAM
SELECT fcp.concurrent_program_name ,fr.responsibility_name ,frg.request_group_name ,fcp.user_concurrent_program_name FROM fnd_request_group_units frgup ,fnd_concurrent_programs_vl fcp ,fnd_request_groups frg ,fnd_responsibility_vl fr WHERE 1=1 AND fr.request_group_id = frg.request_group_id AND frg.request_group_id = frgup.request_group_id AND frgup.request_unit_type = 'P' AND frgup.request_unit_id = fcp.concurrent_program_id AND fcp.concurrent_program_name LIKE '%&concurrent_program_name%';
– FIND WHICH SID IS BLOCKING OTHER SID
select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status from v$lock l1, v$session s1, v$lock l2, v$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 ;
–FIND DETAILS OF A SID
rem vusersid.sql rem set linesize 132 rem ttitle 'User Session Statistics' rem col pid format 9999 heading 'PID' col spid format a6 heading 'SERVER|PID' col sid format 9999 heading 'SID' col serial# format 99999 heading 'SERIAL' col process format a6 heading 'CLIENT|PID' col osuser format a8 heading 'OS|USERNAME' col username format a10 heading 'ORACLE|USERNAME' col log_per_sec format 999999 heading 'LOG|PER|SEC' col logical format b9999999999 heading 'LOGICAL|READS' col phy_per_sec format b9999 heading 'PHY|PER|SEC' col physical_reads format b99999999 heading 'PHYSICAL|READS' col audsid format b9999999 heading 'AUDIT|SESSION' col program format a32 heading 'PROGRAM NAME' col module format a32 heading 'MODULE' col logon_time format a8 heading 'LOGON|TIME' col duration format a8 heading 'DURATION' col last_call_min format 9999 heading 'LAST|CALL|MIN' col status format a1 heading 'S' rem select s.process, p.spid, /* p.pid, */ s.sid, s.serial#, s.osuser, s.username, ( i.block_gets + i.consistent_gets ) / ( ( sysdate - s.logon_time ) * 86400 ) log_per_sec, i.block_gets + i.consistent_gets logical, physical_reads / ( ( sysdate - s.logon_time ) * 86400 ) phy_per_sec, i.physical_reads, /* s.audsid, */ to_char( trunc(sysdate) + ( sysdate - s.logon_time ), 'hh24:mi:ss' ) duration, s.last_call_et/60 last_call_min, decode( s.status, 'ACTIVE', '*', 'INACTIVE', null, 'KILLED', 'K', '?' ) status, decode( s.action, 'Concurrent Request', 'CM/' || s.module, s.module ) module from v$process p, v$session s, v$sess_io i where i.sid = s.sid and s.paddr = p.addr and s.sid = &sid; rem set linesize 80
–FIND DETAILS OF SQL OF A SID
rem vusersidsql.sql rem set linesize 132 rem ttitle 'User Session Statistics' rem col pid format 9999 heading 'PID' col spid format a6 heading 'SERVER|PID' col sid format 9999 heading 'SID' col serial# format 99999 heading 'SERIAL' col process format a6 heading 'CLIENT|PID' col osuser format a8 heading 'OS|USERNAME' col username format a10 heading 'ORACLE|USERNAME' col log_per_sec format 999999 heading 'LOG|PER|SEC' col logical format b9999999999 heading 'LOGICAL|READS' col phy_per_sec format b9999 heading 'PHY|PER|SEC' col physical_reads format b99999999 heading 'PHYSICAL|READS' col audsid format b9999999 heading 'AUDIT|SESSION' col program format a32 heading 'PROGRAM NAME' col module format a32 heading 'MODULE' col logon_time format a8 heading 'LOGON|TIME' col duration format a8 heading 'DURATION' col last_call_min format 9999 heading 'LAST|CALL|MIN' col status format a1 heading 'S' col hide_sql_hash_value noprint new_value _sql_hash_value rem select s.process, p.spid, /* p.pid, */ s.sid, s.serial#, s.osuser, s.username, ( i.block_gets + i.consistent_gets ) / ( ( sysdate - s.logon_time ) * 86400 ) log_per_sec, i.block_gets + i.consistent_gets logical, physical_reads / ( ( sysdate - s.logon_time ) * 86400 ) phy_per_sec, i.physical_reads, /* s.audsid, */ to_char( trunc(sysdate) + ( sysdate - s.logon_time ), 'hh24:mi:ss' ) duration, s.last_call_et/60 last_call_min, decode( s.status, 'ACTIVE', '*', 'INACTIVE', null, 'KILLED', 'K', '?' ) status, decode( s.action, 'Concurrent Request', 'CM/' || s.module, s.module ) module, s.sql_hash_value hide_sql_hash_value from v$process p, v$session s, v$sess_io i where i.sid = s.sid and s.paddr = p.addr and s.sid = &sid; rem set linesize 80 rem ttitle 'SQL Text' rem col sql_text format a64 heading 'SQL TEXT' rem select sql_text from v$sqltext where hash_value = &_sql_hash_value and &_sql_hash_value <> 0 order by piece;
–SCRIPT TO CHECK WHEN EBS USER LOG IN AND THE RESPONSIBILITY ACCESSED BY THE USER
NOTE: ‘Sign-On:Audit Level’ profile must be enabled in the EBS instance for the data to be collected. Choose the scope of your audit and who to audit by setting the user profile level at the user, responsibility, application, or site profile levels. Also remember, you should consider the additional system overhead required to precisely monitor and audit your users as they access Oracle Applications. The more users you audit and the higher the level of auditing, the greater the likelihood of incurring additional system overhead.
SELECT UNIQUE fu.user_id, fu.user_name user_name, fr.responsibility_key responsibility, fg.START_DATE respo_start_date, flr.start_time respo_last_access_date FROM fnd_user fu, fnd_user_resp_groups_all fg, fnd_application_tl fa, fnd_responsibility fr, fnd_login_responsibilities flr, fnd_logins fl WHERE fg.user_id = fu.user_id AND fg.responsibility_application_id = fa.application_id AND fa.application_id = fr.application_id AND fg.responsibility_id = fr.responsibility_id AND fa.LANGUAGE='US' AND fl.login_id = flr.login_id AND fu.user_id = fl.user_id AND fg.responsibility_id = flr.responsibility_id AND fg.end_date is Null and flr.start_time in (select max(start_time) from fnd_login_responsibilities group by RESPONSIBILITY_ID) ORDER BY fu.user_id, fr.responsibility_key, flr.start_time desc;
- Oracle Multitenant DB 4 : Parameters/SGA/PGA management in CDB-PDB - July 18, 2020
- Details of running concurrent requests in Oracle EBS - May 26, 2020
- Oracle Multitenant DB 3 : Data Dictionary Architecture in CDB-PDB - March 20, 2020
This blog always helps me a lot.
Sanjeev – Thanks. Hope you are doing great in your Career.
Very Handy