Session Related Queries

1. Show all connected users
set lines 100 pages 999
col ID format a15
select username
, sid ',' serial# "ID"
, status
, last_call_et "Last Activity"
from v$session
where username is not null
order by status desc
, last_call_et desc

2. Time since last user activity
set lines 100 pages 999
select username, floor(last_call_et / 60) "Minutes", statusfrom v$sessionwhere username is not nullorder by last_call_et

3. Sessions sorted by logon time
set lines 100 pages 999
col ID format a15col osuser format a15
col login_time format a14
select username, osuser, sid ',' serial# "ID", status, to_char(logon_time, 'hh24:mi dd/mm/yy') login_time, last_call_etfrom v$sessionwhere username is not nullorder by login_time

4. Show user info including os pid
col "SID/SERIAL" format a10
col username format a15col
osuser format a15col
program format a40
select s.sid ',' s.serial# "SID/SERIAL", s.username, s.osuser, p.spid "OS PID", s.programfrom v$session s, v$process pWhere s.paddr = p.addrorder by to_number(p.spid)

5. Show a users current sql
Select sql_textfrom v$sqlareawhere (address, hash_value) in(select sql_address, sql_hash_value from v$session where username like '&username')

6. Session status associated with the specified os process id
select s.username, s.sid, s.serial#, p.spid, last_call_et, statusfrom V$SESSION s, V$PROCESS pwhere s.PADDR = p.ADDRand p.spid='&pid'

7. All active sql
set feedback offset
serveroutput on size 9999
column username format a20
column sql_text format a55 word_wrapped
begin
for x in
(select username'('sid','serial#') ospid = ' process ' program = ' program username,
to_char(LOGON_TIME,' Day HH24:MI') logon_time,
to_char(sysdate,' Day HH24:MI') current_time,
sql_address,
sql_hash_value from v$session
where status = 'ACTIVE' and rawtohex(sql_address) <> '00'
and username is not null ) loop
for y in
(select sql_text from v$sqlarea where address = x.sql_address ) loop
if ( y.sql_text not like '%listener.get_cmd%' and
y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%' )
then dbms_output.put_line( '--------------------' );
dbms_output.put_line( x.username );
dbms_output.put_line( x.logon_time ' ' x.current_time ' SQL#=' x.sql_hash_value); dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
end if;
end loop;
end loop;
end;
/
8. Display any long operations
set lines 100 pages 999
col username format a15
col message format a40
col remaining format 9999
select username, to_char(start_time, 'hh24:mi:ss dd/mm/yy') started, time_remaining remaining, messagefrom v$session_longops
where time_remaining = 0
order by time_remaining desc

9. List open cursors per user
set pages 999
select sess.username, sess.sid, sess.serial#, stat.value cursors
from v$sesstat stat, v$statname sn, v$session sess
where sess.username is not null
and sess.sid = stat.sid
and stat.statistic# = sn.statistic#
and sn.name = 'opened cursors current'
order by value

Copied from http://www.shutdownabort.com/

Steps to create Readonly/View Only User in OEM

To create a new account with View Only privileges:

1. Logon to Grid Control as a super admin user (SYSMAN or equivalent)











2. Click on the links: Setup -> Administrators -> Click on Create


























3. Remove the 'PUBLIC ROLE' that is listed by default










4. Under Create Administrator: System Privileges select 'VIEW ANY TARGET'.









5. Under Create Administrator: Targets, choose all the targets this new admin should be able to view

6. Under Create Administrator: Target Privileges , BATCH ASSIGN Choose 'View'











7. Click Apply














Reference - How To Create Administrator Accounts (Super Admin, Admin, View Only, etc) In Grid Control [ID 377310.1]

Steps to Remove/De-register Agent from OEM:

1. Stop the Agent on the target machine:

cd /bin
emctl stop agent

2. Login to the Repository Ddatabase (via sqlplus) as user SYSMAN and execute the following command:
SQL> exec mgmt_admin.cleanup_agent(':');

For example:

SQL> exec mgmt_admin.cleanup_agent('ebsqa-mt.oracle_mt.com:3872');

PL/SQL procedure successfully completed.



3. De-install the Agent on the target machine.


Reference: Metalink Note - 438158.1 -- How to Cleanly De-Install the EM 10g Agent on Windows and Unix

Default Application users in 11i EBS

We can find out the list of default users in 11i EBS with sql available 3904641 patch:

Below are list of Default Application users in EBS:

ANONYMOUS
AUTOINSTALL
CONCURRENT_MANAGER
FEEDER SYSTEM
INITIAL SETUP
STANDALONE_BATCH_PROCESS
MOBILEADM
ASGADM
WIZARD
ASGUEST
IEXADMIN
IBE_GUEST
IBE_ADMIN
IBEGUEST
OP_SYSADMIN
OP_CUST_CARE_ADMIN
IRC_EMP_GUEST
IRC_EXT_GUEST

How to Check Incompatibilities for Current Program From Applications

How to Check Incompatibilities for Current Program From Applications:

Navigation:

Application Developer --> Program --> Incompatibilities.

Imp Queries on Troubleshooting concurrent request / manager issues

Query to check the scheduled concurrent Requests/Programs:

SELECT cr.request_id,
DECODE (cp.user_concurrent_program_name,
'Report Set', 'Report Set:' || cr.description,
cp.user_concurrent_program_name
) NAME,
argument_text, cr.resubmit_interval,
NVL2 (cr.resubmit_interval,
'PERIODICALLY',
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')
) schedule_type,
DECODE (NVL2 (cr.resubmit_interval,
'PERIODICALLY',
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')
),
'PERIODICALLY', 'EVERY '
|| cr.resubmit_interval
|| ' '
|| cr.resubmit_interval_unit_code
|| ' FROM '
|| cr.resubmit_interval_type_code
|| ' OF PREV RUN',
'ONCE', 'AT :'
|| TO_CHAR (cr.requested_start_date, 'DD-MON-RR HH24:MI'),
'EVERY: ' || fcr.class_info
) schedule,
fu.user_name, requested_start_date
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_requests cr,
apps.fnd_user fu,
apps.fnd_conc_release_classes fcr
WHERE cp.application_id = cr.program_application_id
AND cp.concurrent_program_id = cr.concurrent_program_id
AND cr.requested_by = fu.user_id
AND cr.phase_code = 'P'
AND cr.requested_start_date > SYSDATE
AND cp.LANGUAGE = 'US'
AND fcr.release_class_id(+) = cr.release_class_id
AND fcr.application_id(+) = cr.release_class_app_id;


Query to check the duplicated schedules of the same program with the same arguments


SELECT request_id, NAME, argument_text, user_name
FROM (SELECT cr.request_id,
DECODE (cp.user_concurrent_program_name,
'Report Set', 'Report Set:' || cr.description,
cp.user_concurrent_program_name
) NAME,
argument_text, fu.user_name
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_requests cr,
apps.fnd_user fu
WHERE cp.application_id = cr.program_application_id
AND cp.concurrent_program_id = cr.concurrent_program_id
AND cr.requested_by = fu.user_id
AND cr.phase_code = 'P'
AND cr.requested_start_date > SYSDATE
AND cp.LANGUAGE = 'US'
AND fu.user_name NOT LIKE 'PPG%') t1
WHERE EXISTS (
SELECT 1
FROM (SELECT cr.request_id,
DECODE (cp.user_concurrent_program_name,
'Report Set', 'Report Set:'
|| cr.description,
cp.user_concurrent_program_name
) NAME,
argument_text, fu.user_name
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_requests cr,
apps.fnd_user fu
WHERE cp.application_id = cr.program_application_id
AND cp.concurrent_program_id =
cr.concurrent_program_id
AND cr.requested_by = fu.user_id
AND cr.phase_code = 'P'
AND cr.requested_start_date > SYSDATE
AND cp.LANGUAGE = 'US'
AND fu.user_name NOT LIKE 'PPG%') t2
WHERE t1.NAME = t2.NAME
AND t1.argument_text = t2.argument_text
AND t1.user_name = t2.user_name
GROUP BY NAME, argument_text, user_name
HAVING COUNT (*) > 1)
ORDER BY user_name, NAME



Query to check average pending time per request:

Note: Average pending time for a request is calculated like below:
("Highest of Requested_start_date or Date_submitted" - Actual_start_date ) / Total requests

A Request can be in Pending state for variety of reasons like conflict with other requests, improperly tuned managers (sleep seconds / cache size / number of managers etc)

We can schedule this script to gather data regularly for historical analysis as we normally purge the concurrent requests regularly.
SELECT TO_CHAR (actual_start_date, 'DD-MON-YYYY') DAY,
concurrent_queue_name,
(SUM ( ( actual_start_date
- (CASE
WHEN requested_start_date > request_date
THEN requested_start_date
ELSE request_date
END
)
)
* 24
* 60
* 60
)
)
/ COUNT (*) "Wait_Time_per_Req_in_Secs"
FROM apps.fnd_concurrent_requests cr,
apps.fnd_concurrent_processes fcp,
apps.fnd_concurrent_queues fcq
WHERE cr.phase_code = 'C'
AND cr.actual_start_date IS NOT NULL
AND cr.requested_start_date IS NOT NULL
AND cr.controlling_manager = fcp.concurrent_process_id
AND fcp.queue_application_id = fcq.application_id
AND fcp.concurrent_queue_id = fcq.concurrent_queue_id
GROUP BY TO_CHAR (actual_start_date, 'DD-MON-YYYY'), concurrent_queue_name
ORDER BY 2


Query to checking which manager is going to execute a program:

SELECT user_concurrent_program_name, user_concurrent_queue_name
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_queue_content cqc,
apps.fnd_concurrent_queues_tl cq
WHERE cqc.type_application_id(+) = cp.application_id
AND cqc.type_id(+) = cp.concurrent_program_id
AND cqc.type_code(+) = 'P'
AND cqc.include_flag(+) = 'I'
AND cp.LANGUAGE = 'US'
AND cp.user_concurrent_program_name = '&USER_CONCURRENT_PROGRAM_NAME' AND NVL (cqc.concurrent_queue_id, 0) = cq.concurrent_queue_id
AND NVL (cqc.queue_application_id, 0) = cq.application_id
AND cq.LANGUAGE = 'US'



Query to see all the pending / Running requests per each manager wise :



SELECT request_id, phase_code, status_code, user_name,
user_concurrent_queue_name
FROM apps.fnd_concurrent_worker_requests cwr,
apps.fnd_concurrent_queues_tl cq,
apps.fnd_user fu
WHERE (cwr.phase_code = 'P' OR cwr.phase_code = 'R')
AND cwr.hold_flag != 'Y'
AND cwr.requested_start_date <= SYSDATE
AND cwr.concurrent_queue_id = cq.concurrent_queue_id
AND cwr.queue_application_id = cq.application_id
AND cq.LANGUAGE = 'US'
AND cwr.requested_by = fu.user_id
ORDER BY 5


Query to checking the incompatibilities between the programs:


SELECT a2.application_name, a1.user_concurrent_program_name,
DECODE (running_type,
'P', 'Program',
'S', 'Request set',
'UNKNOWN'
) "Type",
b2.application_name "Incompatible App",
b1.user_concurrent_program_name "Incompatible_Prog",
DECODE (to_run_type,
'P', 'Program',
'S', 'Request set',
'UNKNOWN'
) incompatible_type
FROM apps.fnd_concurrent_program_serial cps,
apps.fnd_concurrent_programs_tl a1,
apps.fnd_concurrent_programs_tl b1,
apps.fnd_application_tl a2,
apps.fnd_application_tl b2
WHERE a1.application_id = cps.running_application_id
AND a1.concurrent_program_id = cps.running_concurrent_program_id
AND a2.application_id = cps.running_application_id
AND b1.application_id = cps.to_run_application_id
AND b1.concurrent_program_id = cps.to_run_concurrent_program_id
AND b2.application_id = cps.to_run_application_id
AND a1.language = 'US'
AND a2.language = 'US'
AND b1.language = 'US'
AND b2.language = 'US'

Forms URL in R12

Forms URL in R12

When using Forms Servlet Mode:

http://.:/forms/frmservlet
Note: Modify the context variable parameter (s_appserverid_authentication) from SECURE to OFF and run autoconfig, then forms will startup without that authorization error.
When using Forms Socket Mode:
http://.:/OA_HTML/frmservlet
Query to get URL in Applications:

1. select profile_option_value from fnd_profile_option_values where profile_option_value like '%http%';

2. select home_url from icx_parameters;