Oracle Applications Database frequently used scripts

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;

• Author
• Recent Posts

Brijesh Gogia

I’m an experienced Cloud/Oracle Applications/DBA Architect with more than 15 years of full-time DBA/Architect experience. I have gained wide knowledge on Oracle and Non-Oracle software stack running on-prem and on Cloud and have worked on several big projects for multi-national companies. I enjoy working with leading-edge technology and have a passion for Cloud architecture, automation, database performance, and stability. Thankfully my work allows me time for researching new technologies (and to write about them).

Latest posts by Brijesh Gogia (see all)

• Oracle Multitenant DB 4 : Parameters/SGA/PGA management in CDB-PDB - July 18, 2021
• Oracle Multitenant DB 3 : Data Dictionary Architecture in CDB-PDB - March 20, 2021
• Oracle Multitenant DB 2 : Benefits of the Multitenant Architecture - March 19, 2021