Useful apps DBA scripts

• Who is connected, when

sql> select user_name,to_char(last_logon_date,’DD-MON-YYYY HH24:MI:SS’)
from apps.fnd_user
where to_char(last_logon_date,’DD-MON-YYYY’)=to_char(sysdate,’DD-MON-YYYY’);

• Which resposibility is used

sql> select distinct fu.user_name User_Name,fr.RESPONSIBILITY_KEY Responsibility from fnd_user fu,
fnd_responsibility fr, icx_sessions ic
where fu.user_id = ic.user_id AND
fr.responsibility_id = ic.responsibility_id AND
ic.disabled_flag=’N’ AND
ic.responsibility_id is not null AND
ic.last_connect like sysdate;

• Number of user connected to EBS

sql> SELECT COUNT (DISTINCT d.user_name)
FROM apps.fnd_logins a, v$session b, v$process c, apps.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)

• EBS gather stats

sql> execute fnd_stats.gather_table_stats(‘AR’,'RA_CUSTOMER_TRX_ALL’);

• CUSTOM.pll compile: (copy CUSTOM pll under $AU_TOP/resource and compile)

f60gen CUSTOM.pll apps/apps_password module_type=LIBRARY compile_all=YES

• How to change apps password on 11i

• Shutdown all apps services on all nodes.

• Backup these tables                             

                         sql>    create table xxx_fnd_user as select * from FND_USER

                         sql>    create table xxx_FND_ORACLE_USERID asselect * from FND_ORACLE_USERID

• Connect as apps user on sqlplus and run:

                          $ FNDCPASS apps/apps_old 0 Y system/password SYSTEM APPLSYS apps_new

• Edit passwords in these files on all nodes:

                         $IAS_ORACLE_HOME/Apache/modplsql/cfg/wdbsvr.app

                        $ORACLE_HOME/reports60/server/CGIcmd.dat

• How to change module passwords on 11i

$ FNDCPASS apps/apps_password 0 Y system/system_password ORACLE AR new_password