Important day to day scripts

 

–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;