Thursday, November 25, 2010

Some Valuable SQL for Oracle Apps DBA R12

select * from dba_data_files;

select * from dba_temp_files;

SELECT SUM(BYTES) FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME='&UNDOTBS';


Investigating the locks
SELECT DISTINCT dbw.holding_session, fu.user_name apps_user, fu.email_address "Email", vs.username, vs.process "Client Process",
vs.sid "DB SID", vs.serial# "DB Serial #", vs.MODULE "Module", vs.action "Action", vs.logon_time "Login Time", vp.spid "Server Process"
FROM v$session vs, v$process vp, fnd_logins fl, fnd_user fu, dba_waiters dbw
WHERE vp.addr(+) = vs.paddr
AND fl.spid(+) = vs.process
AND fl.end_time(+) IS NULL
AND fu.user_id(+) = fl.user_id
AND vs.sid = dbw.holding_session;

SELECT ab.object_id,
aa.object_name,
ab.session_id,
ab.process,
ac.sid,
ac.serial#,
ac.blocking_session
FROM v$locked_object ab, dba_objects aa, v$session ac
WHERE ab.object_id = aa.object_id AND ab.session_id = ac.sid;


NLS parameter
select * from v$parameter where name like '%nls%';

select * from dba_users where username= 'CTXSYS';

select l1.sid, ' IS BLOCKING ', l2.sid from v$lock l1, v$lock l2 where l1.block =1 and l2.request > 0 and l1.id1=l2.id1 and l1.id2=l2.id2;

SELECT DISTINCT STATUS, SUM(BYTES), COUNT(*)
FROM DBA_UNDO_EXTENTS group by DISTINCT;

select * from v$segment_statistics;

select * from v$undostat;

select * from v$locked_object;

select * from v$temp_histogram;

select * from v$transaction;

select max(maxquerylen) from v$undostat;

select * from v$unusable_backupfile_details;

select node_name, node_mode, support_cp,
support_web, support_admin, support_forms
from FND_NODES;

select t.request_id, v.program, v.user_concurrent_program_name
from FND_CONCURRENT_REQUESTS t, FND_CONC_REQ_SUMMARY_V v where t.status_code=v.execution_method_code(+);

select * from ad_applied_patches;
SELECT application_id, patch_level, status
FROM fnd_product_installations
WHERE application_id in (101, 200, 222, 7002, 7003, 7004, 600, 275,1000);
select * from fnd_product_installations;
select process_phase, transaction_type, wip_entity_id, entity_type,
resource_id, resource_type, usage_rate_or_amount,
standard_rate_flag, actual_resource_rate, transaction_quantity,
po_header_id, po_line_id
from wip_cost_txn_interface
where wip_entity_id in (select wip_entity_id
from wip_entities
where wip_entity_name = '6159')
and process_phase 3;

Select count(*)
into JobNameCnt
from wip_entities
where upper(wip_entity_name) like UPPER(p_param);


 select * from all_objects where owner='APPS' and object_type='TABLE' and object_name='T' order by object_name desc;
select * from fnd_user;
select * from mtl_transaction_details_v;
select * from fnd_log_messages;
select max(log_sequence) from fnd_log_messages;
select * from fnd_application_all_view where application_name like '%In%';
select * from fnd_product_installations;

/* material transaction related issues */
select * from mtl_material_transactions where costed_flag='N';

select * from mtl_material_transactions_temp;

For profile options from back end 

Using profile option 'Initialization SQL Statement - Custom' [ID 135389.1] */
SELECT val.level_id, val.level_value, val.profile_option_id,
val.profile_option_value
FROM fnd_profile_options opt, fnd_profile_option_values val
WHERE opt.profile_option_name = 'FND_INIT_SQL' AND
opt.profile_option_id = val.profile_option_id;
select * from fnd_profile_options;
select * from fnd_profile_option_values;


You can correct the syntax error or can set profile_option_value
to NULL and then define it in the Applications again (preferred way):


UPDATE fnd_profile_option_values
SET profile_option_value = NULL
WHERE profile_option_id = AND
level_id = AND
level_value = ; 



Investigating the trigger related to JA
select * from all_triggers where trigger_name like 'JA%';


I hope both the sql’s will help you. If you want to restrict some users then you need to know their IP address and add them in Apache conf file.

You can also monitor the users from OAM and Monitor form (You need to have sign-on audit on for this).


How Logged to Forms
column “User_Name” format a10;
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;


How logged to Apps
SELECT user_name username,
description name,
to_char(b.first_connect,’MM/DD/RR HH24:MI’) firstconnect,
to_char(b.last_connect,’MM/DD/RR HH24:MI’) lastconnect
FROM apps.fnd_user a,
(SELECT MIN (first_connect) first_connect,
MAX (last_connect) last_connect, last_updated_by user_id
FROM apps.icx_sessions
GROUP BY last_updated_by) b
WHERE a.user_id = b.user_id
AND last_connect>SYSDATE-3/12
ORDER BY 4 DESC


You can also use the following SQL statement to count concurrent users connected to Oracle Apps

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

And the following SQL to show username(s)

SQL> select 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);

Update FND_USER bu setting END_DATE to the value.

update FND_USER set END_DATE = ;

If i create new custom_top i use below grants;
CREATE USER XX
IDENTIFIED BY XX
DEFAULT TABLESPACE XX
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
GRANT CONNECT TO XX;
ALTER USER XX DEFAULT ROLE ALL;
GRANT CREATE VIEW TO XX;
GRANT CREATE TABLE TO XX;
GRANT CREATE SESSION TO XX;
GRANT CREATE SYNONYM TO XX;
GRANT CREATE TRIGGER TO XX;
GRANT CREATE SEQUENCE TO XX;
GRANT CREATE PROCEDURE TO XX;
GRANT CREATE PUBLIC SYNONYM TO XX;
ALTER USER XX QUOTA UNLIMITED ON XX;
ALTER USER XX QUOTA UNLIMITED ON XX_INDEX;


Checking tablespace
SELECT F.TABLESPACE_NAME pTablespaceName
,TO_CHAR ((T.TOTAL_SPACE - F.FREE_SPACE),'999,999') pUsedSpace
,TO_CHAR (F.FREE_SPACE, '999,999') pFreeSpace
,TO_CHAR (T.TOTAL_SPACE, '999,999') pTotalSpace
,TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),'999')||' %' pPercentFree
FROM
( SELECT TABLESPACE_NAME,
ROUND (SUM (BLOCKS*(SELECT VALUE/1024
FROM V$PARAMETER
WHERE NAME = 'db_block_size')/1024)) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME ) F,
( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES/1048576)) TOTAL_SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME ) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME;

some more script
select count(*) from fnd_concurrent_processes;

select concurrent_queue_id from fnd_concurrent_queues
where concurrent_queue_name='FNDICM';

select a.concurrent_queue_name
, substr(b.os_process_id,0,10) "OS Proc"
, b.oracle_process_id "Oracle ID"
, b.process_status_code
from fnd_concurrent_queues a
, fnd_concurrent_processes b
where a.concurrent_queue_id=b.concurrent_queue_id
and a.concurrent_queue_name='FNDICM'
and b.process_status_code='A'
order by b.process_status_code;

select a.concurrent_queue_name
, substr(b.os_process_id,0,10) "OS Proc"
, b.oracle_process_id "Oracle ID"
, b.process_status_code
from fnd_concurrent_queues a
, fnd_concurrent_processes b
where a.concurrent_queue_id=b.concurrent_queue_id
and a.concurrent_queue_name='STANDARD'
and b.process_status_code='A'
order by b.process_status_code;

select MAX_PROCESSES,RUNNING_PROCESSES
from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME='FNDICM';

select MAX_PROCESSES,RUNNING_PROCESSES
from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME='STANDARD';

select MAX_PROCESSES,RUNNING_PROCESSES
from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME='FNDCRM';

select count (*) from fnd_concurrent_requests
where status_code='T';

select count(*) from fnd_concurrent_requests
where status_code='P';

select count (*) from fnd_concurrent_requests
where status_code='R';

select profile_option_id , profile_option_value
from FND_PROFILE_OPTION_VALUES
where profile_option_id= (select profile_option_id
from FND_PROFILE_OPTIONS
where profile_option_name='CONC_PMON_METHOD');

select r.request_id, p.os_process_id
from FND_CONCURRENT_REQUESTS r,FND_CONCURRENT_PROCESSES p
where r.controlling_manager = p.concurrent_process_id
and request_id=&request_id;

Workflow mailer troubleshooting

select target_node
from fnd_concurrent_queues where concurrent_queue_name like 'WFMLRSVC%'; 


SELECT b.component_name,
c.parameter_name,
a.parameter_value
FROM fnd_svc_comp_param_vals a,
fnd_svc_components b,
fnd_svc_comp_params_b c
WHERE b.component_id = a.component_id
AND b.component_type = c.component_type
AND c.parameter_id = a.parameter_id
AND c.encrypted_flag = 'N'
AND b.component_name like '%Mailer%'
AND c.parameter_name in ('OUTBOUND_SERVER', 'REPLYTO')
ORDER BY c.parameter_name;



Warm Regards,
Sukhwinder Singh

.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.