grant select to APPS object on different schema without causing invalids in database.

Query to grant select privs on all apps objects to a given schema XYZ without causing invalid objects


select 'exec ad_zd.grant_privs(''SELECT'||''''||','||''''||object_name||''''||','||''''||'XYZ'||''''||')'||';'
from dba_objects where object_type in ('TABLE','VIEW') and owner ='APPS';

find Indexes created on table

Query to find the list of all indexes created on a table.

SELECT index_name, column_name, column_position
FROM dba_ind_columns
WHERE table_name like upper('&table_name')
ORDER BY index_name, column_position;

find number of users connected on each application node

Query to find the number of active users connected to each oracle ebusiness suite Server

Script to determine "active users" for OACoreGroup: 

REM 
REM SQL to count number of Application users 11i/R12 
REM Run as APPS user 
REM 
select icx.node_id,fnd.node_name,'Number of user sessions : ' || count( distinct icx.session_id) How_many_user_sessions 
from icx_sessions icx, fnd_nodes fnd where icx.disabled_flag != 'Y' 
and icx.PSEUDO_FLAG = 'N' 
and icx.node_id=fnd.node_id 
and (icx.last_connect + decode(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'), NULL,icx.limit_time, 0, 
icx.limit_time,FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT')/60)/24) > sysdate and counter < icx.limit_connects 
group by icx.node_id,fnd.node_name; 
REM 
REM END OF SQL 
REM 

check last email sent from oracle EBusiness Suite

Query to check the last email sent from oracle Ebusiness suite


select to_char(max(begin_date),'DD-MON-YY HH24:MI:SS')
 from apps.wf_notifications
 where mail_status = 'SENT';