Important useful Apps DBA sql's and some linux commands

To find the session from Os process id:

select * from v$session where paddr in (select addr from v$process where spid='1773')

To find the concurrent request from session id:

select * from fnd_concurrent_requests where oracle_process_id in (select spid from v$process where addr in (select paddr from v$session where sid='191'));

To find sid serial# from request id

SELECT ses.sid,
ses.serial#
FROM v$session ses,
v$process pro
WHERE ses.paddr = pro.addr
AND pro.spid IN (SELECT oracle_process_id
FROM fnd_concurrent_requests
WHERE request_id = &request_id);

Note : oracle_process_id is Unix PID and request_id is running concurrent program's request ID. If "sid" and "serial#" value is returning then it means that process is running at database level. If you are canceling a request from "Submit Request Submission" form then it should release associated database process and session but it doesn't mean that it will kill database process immediately. Database process will take their own time to validate concurrent program execution process that has been cancelled and then it will kill database process. So ideally if you are canceling a request from "Submit Request Submission" then you should wait for some time and then check associated database process.

SELECT SID
FROM v$session
WHERE paddr in
(SELECT addr FROM v$process
WHERE spid in (SELECT oracle_process_id
FROM fnd_concurrent_requests
WHERE request_id = '11865131'));

High CPU Consuming Sessions:

select ss.sid,ss.value CPU , se.username,se.program from v$sesstat ss,v$session se
where ss.statistic# in (select statistic# from v$statname where name='CPU user by this session')
and se.sid=ss.sid
order by CPU;

To find the sessions which are locked a particular table:

SELECT *
FROM v$session
WHERE SID IN (SELECT session_id
FROM v$locked_object
WHERE object_id IN (SELECT object_id
FROM dba_objects
WHERE object_name LIKE 'MTL_SYSTEM_ITEMS%'));

SELECT sid, serial#,status,module,username,machine,action FROM v$session
WHERE SID IN (SELECT session_id
FROM v$locked_object
WHERE object_id IN (SELECT object_id
FROM dba_objects
WHERE object_name LIKE 'MTL_SYSTEM_ITEMS%'));

To find trace file for a Concurrent Request:

SELECT 'Request id: ' || request_id, 'Trace id: ' || oracle_process_id,
'Trace Flag: ' || req.enable_trace,
'Trace Name:
'
|| dest.VALUE
|| '/'
|| LOWER (dbnm.VALUE)
|| '_ora_'
|| oracle_process_id
|| '.trc',
'Prog. Name: ' || prog.user_concurrent_program_name,
'File Name: ' || execname.execution_file_name
|| execname.subroutine_name,
'Status : '
|| DECODE (phase_code, 'R', 'Running')
|| '-'
|| DECODE (status_code, 'R', 'Normal'),
'SID Serial: ' || ses.SID || ',' || ses.serial#,
'Module : ' || ses.module
FROM fnd_concurrent_requests req,
v$session ses,
v$process proc,
v$parameter dest,
v$parameter dbnm,
fnd_concurrent_programs_vl prog,
fnd_executables execname
WHERE req.request_id = &request
AND req.oracle_process_id = proc.spid(+)
AND proc.addr = ses.paddr(+)
AND dest.NAME = 'user_dump_dest'
AND dbnm.NAME = 'db_name'
AND req.concurrent_program_id = prog.concurrent_program_id
AND req.program_application_id = prog.application_id
AND prog.application_id = execname.application_id
AND prog.executable_id = execname.executable_id;

Abstract: This script can be used to find out the Users connected to Oracle Applications. I use the script for imitating the Users before bouncing Apache or Forms Services. There are several MetaLink Notes for doing the same, but not giving exact output in various instances. This works in 11.5.9 and 11.5.10.XX environments.

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;


select rpad('-',100,'-') || chr(10) ||
'USER: ' || s.username || '/' || s.osuser ||
'.....STATUS:' || s.status ||
'.....LOGON_TIME:' ||
to_char(s.logon_time,'DD-MON-YYYY HH24:MI:SS') || chr(10) ||
'.....SID:' || s.sid ||
'.....SERIAL#:' || s.serial# ||
'.....SPID:' || P.SPID || chr(10) ||
'.....MACHINE:' || s.machine ||
'.....PROGRAM:' || s.program || chr(10) ||
'.....SQL: ' || s.sql_address || chr(10) ||
decode(sa.sql_text,null,'<---no sql stmt--->',sa.sql_text)
from v$session s, v$process p, v$sqlarea sa
where s.username is not null
and s.paddr = p.addr
and s.sql_address = sa.address (+)
order by s.USERNAME,s.LOGON_TIME


To find the tablespaces which are having less than 20% free space

SELECT 'TABLESPACE: ' || df.tablespace_name,
'Percent Free: ' || ROUND (fs.sumbytes * 100 / df.sumbytes, 2) || '%',
'Free Space: ' || ROUND (fs.sumbytes / 1048576, 2) || ' MB',
'Total Size: ' || ROUND (df.sumbytes / 1048576, 2) || ' MB'
FROM (SELECT tablespace_name, SUM (BYTES) sumbytes
FROM dba_data_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name, SUM (BYTES) sumbytes
FROM dba_free_space
GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name
AND ((fs.sumbytes <
(df.sumbytes * DECODE (df.tablespace_name, 'SYSTEM', 0.07, 0.20)
)
)
)
AND df.tablespace_name != 'RBS';


To query the requests which are taking more than 1hr in current day

SELECT t.request_id, t.actual_start_date, t.actual_completion_date,
TRUNC ((SYSDATE - t.actual_start_date) * 24 * 60,
2
) AS "Total Time (Min)"
FROM fnd_concurrent_requests t
WHERE t.actual_start_date LIKE SYSDATE
AND (SYSDATE - t.actual_start_date) * 24 > 1
AND status_code = 'R'
AND phase_code = 'R'

To find the requests which will run more than an hour

SELECT t.request_id, t.actual_start_date, t.actual_completion_date,
TRUNC ((t.actual_completion_date - t.actual_start_date) * 24 * 60,
2
) AS "Total Time (Min)"
FROM fnd_concurrent_requests t
WHERE t.actual_start_date LIKE t.actual_completion_date
AND (t.actual_completion_date - t.actual_start_date) * 24 > 1


To find Sid and serial# for a particular request:

SELECT SID, serial#, action, paddr
FROM v$session
WHERE process IN (SELECT os_process_id
FROM fnd_concurrent_requests
WHERE request_id = 'request_id');

SELECT SID
FROM v$session
WHERE paddr LIKE (SELECT addr
FROM v$process
WHERE spid = (SELECT oracle_process_id
FROM fnd_concurrent_requests
WHERE request_id = &request_id))


To verify that the table has gathered statistics recently, use the following SQL:

SELECT last_analyzed, sample_size
FROM dba_tables
WHERE table_name = &table_name;


SELECT column_name, last_analyzed, sample_size
FROM all_tab_columns
WHERE table_name = &table_name;


SELECT index_name, last_analyzed, sample_size
FROM all_indexes
WHERE table_name = &table_name;


Gather schema Statistics

exec dbms_stats.gather_table_stats('ONT', 'ONT.OE_ORDER_HEADERS_ALL')

exec dbms_stats.gather_schema_stats('ONT')

select distinct trunc(last_analyzed), OWNER from dba_tables where owNer not in ('SYS', 'SYSTEM','MDSYS','ORDSYS','CTXSYS','OUTLN')
ORDER BY trunc(LAST_ANALYZED) ASC

select distinct trunc(last_analyzed), OWNER from dba_indexes where owNer not in ('SYS', 'SYSTEM','MDSYS','ORDSYS','CTXSYS','OUTLN')
ORDER BY trunc(LAST_ANALYZED) ASC

SELECT *
FROM dba_indexes
WHERE TRUNC (last_analyzed) < TRUNC (SYSDATE) - 4
AND owner = 'AP'
AND table_name NOT IN (
SELECT table_name
FROM dba_tables
WHERE TRUNC (last_analyzed) < TRUNC (SYSDATE) - 4
AND owner = 'AP'
AND tablespace_name = 'APD')

exec dbms_stats.gather_schema_stats(
ownname => 'APPS',
estimate_percent => dbms_stats.auto_sample_size, ----> or you can give % 10..30...etc
method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO',
cascade => TRUE);

exec DBMS_STATS.GATHER_INDEX_STATS (
ownname => 'BOM',
indname => 'CST_STD_COST_ADJ_TEMP_N2',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE );

execute dbms_stats.gather_schema_stats(ownname => 'JMDISC', estimate_percent => 30, method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO',cascade => TRUE);

execute dbms_stats.gather_schema_stats(ownname => 'JMAPPS', estimate_percent => 30, method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO',cascade => TRUE);

execute dbms_stats.gather_schema_stats(ownname => 'SADBAM', estimate_percent => 30, method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO',cascade => TRUE);

execute dbms_stats.gather_schema_stats(ownname => 'MSACCESS', estimate_percent => 30, method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO',cascade => TRUE);


Moving tables to new tablespace, rebuild indexes and compute statistics


A) Moving Tables from one tablespace to another tablespace

select ‘alter table ‘||table_name||’ move tablespace COSTPOINT_DATA4;’
from user_tables where tablespace_name=’COSTPOINT_DATA’;
spool off;

B) Rebuilding Indexexes

1)select ‘alter index ‘|| INDEX_NAME|| ‘ rebuild TABLESPACE costpoint_INDEX1;’ from user_indexes
where table_name in (select table_name from user_tables where tablespace_name=’COSTPOINT_DATA’);

2) select ‘alter index ‘|| INDEX_NAME|| ‘ rebuild TABLE costpoint_INDEX1;’ from user_indexes
where table_name in (select table_name from user_tables where table_name=’COSTPOINT_DATA’);

C) compute Statistics

a)select ‘analyze table ‘|| table_name|| ‘ compute statistics;’ from dba_tables where owner_name=’ONT’;

b)Analyze table Table_name compute statistics;


To move table (Re-Org)

a)alter table table_name move;

b)alter table table_name move tablespace tablespace_name;


To find Invalid indexes

a)select index_name from dba_indexes where status not in ('VALID')---UNUSABLE'

b)select owner||'.'||table_name,Index_name,status
from dba_indexes where status not in ('VALID') and owner in('MSC','APPLSYS','AR','APPS')


View for finding Database locks

CREATE OR REPLACE VIEW session_waits (waiting_session,
holding_session,
lock_type,
mode_held,
mode_requested,
lock_id1,
lock_id2
)
AS
SELECT /*+ordered */
w.SID, s.ksusenum,
DECODE (r.ksqrsidt,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',
r.ksqrsidt
),
DECODE (l.lmode,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
l.lmode
),
DECODE (BITAND (w.p1, 65535),
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
TO_CHAR (BITAND (w.p1, 65535))
),
r.ksqrsid1, r.ksqrsid2
FROM v$session_wait w, x$ksqrs r, v$_lock l, x$ksuse s
WHERE w.wait_time = 0
AND w.event = 'enqueue'
AND r.ksqrsid1 = w.p2
AND r.ksqrsid2 = w.p3
AND r.ksqrsidt =
CHR (BITAND (p1, -16777216) / 16777215)
|| CHR (BITAND (p1, 16711680) / 65535)
AND l.BLOCK = 1
AND l.saddr = s.addr
AND l.raddr = r.addr
AND s.inst_id = USERENV ('Instance')
/

And User below query to find locks

select * from session_waits where holding_session not in (select waiting_session from session_waits);

select * from dba_locks where blocking_others !='Not Blocking';

Here holding_session is lock session we need to kill this holding_session session

To find Plm patch set level and rollup patch

select 'Current PLM Patchset: '||ver||' ('||bug||')' "Description" from (
select x.* from
(select 1 seq, '2714179' bug,'11.5.0' pv,'A' ver from dual
union select 2,'3001751','11.5.0','B' from dual
union select 3,'3298205','11.5.0','C' from dual
union select 4,'3990439','11.5.0','D' from dual
union select 5,'4203793','11.5.0','E' from dual
union select 6,'4508296','12.0.0','A' from dual) x,
(select product_version pv from fnd_product_installations
where application_id=431) i,
ad_bugs bugs
where x.bug = bugs.bug_number and x.pv = i.pv order by 1 desc) where rownum = 1
union
select 'Current PLM Rollup: '||ver||' ('||bug||')' "Description" from (
select x.* from
(select 1 seq, '4574625' bug,'11.5.0' pv,'E2' ver from dual
union select 2, '4662995','11.5.0','E3' from dual
union select 3, '4743765','11.5.0','E4' from dual
union select 4, '4894197','11.5.0','E5' from dual
union select 5, '5031825','11.5.0','E6' from dual
union select 6, '5170870','11.5.0','E7' from dual
union select 7, '5239326','11.5.0','E8' from dual
union select 8, '5462432','11.5.0','E9' from dual
union select 9, '5671752','11.5.0','E10' from dual
union select 10,'5892657','11.5.0','E11' from dual
union select 11,'6192001','11.5.0','E12' from dual
union select 12,'6459400','11.5.0','E13' from dual
) x,
(select product_version pv from fnd_product_installations
where application_id=431) i,
ad_bugs bugs
where x.bug = bugs.bug_number and x.pv = i.pv order by 1 desc) where rownum = 1
union
select 'Product Installation: '||patch_level||': '||decode(status,
'I','Installed','S','Shared','Not Installed')
from fnd_product_installations
where application_id=431
order by 1;


SELECT (bug_number) "Patch Number", decode((bug_number),
'7291444','PO 11.5.10 RUP6',
'7168377','PO 11.5.10 RUP5',
'6778767','PO 11.5.10 RUP4',
'6505228','PO 11.5.10 RUP3',
'3384350', 'SCM_PF.J',
'2700001','PRC_PF.I',
'2320032','PRC_PF.H',
'2141229','PRC_PF.G',
'1891381','PRC_PF.F',
'1745369','PRC_PF.E',
'1554100','PRC_PF.D',
'2123967','11.5.7 11i.PRC_PF.G',
'2293243','11.5.8 11i.PRC_PF.H',
'2669606','11.5.9 11i.PRC_PF.I',
'3140000','11.5.10 11i.SCM_PF.J',
'3240000','11.5.10.1',
'3480000','11.5.10.2',
'4017300','ATG CU 1',
'4125550','ATG CU 2',
'4334965','ATG CU 3',
'4676589','ATG CU 4',
'5473858','ATG CU 5',
'5903765','ATG CU 6',
'5082400','12.0.1 RUP1',
'5484000','12.0.2 RUP2',
'6141000','12.0.3 RUP3',
'6435000','12.0.4 RUP4',
'7355145', '12.0 Update September 08',
'Other') "Patch Name" FROM ad_bugs
WHERE bug_number IN
('7291444','7355145','3384350','2700001','2320032', '2141229',
'1891381' ,'1745369','1554100','2123967',
'2293243','2669606','3140000','4017300','4125550','3480000',
'4334965','3240000','4676589','5473858','5903765','5082400','5484000','6141000', '6505228','6435000','6778767','7168377')
order by bug_number desc;

TO FIND THE PATCH LEVEL FOR APPLICATIONS

select substr(a.APPLICATION_NAME,1,60) "Application Name"
, substr(i.PRODUCT_VERSION,1,4) "Version"
, i.PATCH_LEVEL "Patch Level"
, i.APPLICATION_ID "Application ID"
, i.LAST_UPDATE_DATE "Last Update"
from APPS.FND_PRODUCT_INSTALLATIONS i
, APPS.FND_APPLICATION_ALL_VIEW a
where i.APPLICATION_ID = a.APPLICATION_ID
-- not all applications update the next field correctly
-- and i.PATCH_LEVEL like '11i%'
-- these are the applications that concern me most
-- and i.APPLICATION_ID in ('0','140','260','101','200','275','201','222','185')
order by a.APPLICATION_NAME


To Flush Shared Pool:

alter system flush shared_pool;


To Find Session details

SELECT s.sid, s.serial#, s.status, s.Action ,s.module, s.sql_address,s.machine, SUBSTR(s.username ,1,18) username, SUBSTR(s.program,1,15) program,
DECODE (s.command,0, 'No Command', 1, 'Create table', 2, 'Insert', 3, 'select', 6, 'update', 7, 'Delete', 9, 'Create Index', 15, 'Alter Table', 21, 'Create View', 23, 'Validate Index', 35, 'Alter Database', 39, 'Create Tablespace', 41, 'Drop Tablespace', 40, 'Alter Tablespace', 53, 'Drop User', 62, 'Analyze table', 63, 'Analyze Index',
s.command ||':other') Command
FROM v$session s,
v$process p ,
v$transaction t ,
v$rollstat r ,
v$rollname n
WHERE s.paddr=p.addr
AND s.taddr =t.addr (+)
AND t.xidusn =r.usn (+)
AND r.usn =n.usn (+)
order by 7;

select * from v$session_wait where sid='96'

select * from v$system_event where event='db file sequential read'

select * from v$session where paddr in (select addr from v$process where spid='6279')

select pid from v$process where spid =6279

select * from v$session_longops where sid='269'

select * from v$sql where module_hash='544722961'

select * from v$active_session_history where sid=197


select
event c1,
total_waits c2,
time_waited / 100 c3,
total_timeouts c4,
average_wait /100 c5
from
sys.v_$system_event
where
event not in (
'dispatcher timer',
'lock element cleanup',
'Null event',
'parallel query dequeue wait',
'parallel query idle wait - Slaves',
'pipe get',
'PL/SQL lock timer',
'pmon timer',
'rdbms ipc message',
'slave wait',
'smon timer',
'SQL*Net break/reset to client',
'SQL*Net message from client',
'SQL*Net message to client',
'SQL*Net more data to client',
'virtual circuit status',
'WMON goes to sleep'
)
AND
event not like 'DFS%'
and
event not like '%done%'
and
event not like '%Idle%'
AND
event not like 'KXFX%'
order by
c2 desc
;


select
b.sid c1,
decode(b.username,NULL,c.name,b.username) c2,
event c3,
a.total_waits c4,
round((a.time_waited / 100),2) c5,
a.total_timeouts c6,
round((average_wait / 100),2) c7,
round((a.max_wait / 100),2) c8
from
sys.v_$session_event a,
sys.v_$session b,
sys.v_$bgprocess c
where
event NOT LIKE 'DFS%'
and
event NOT LIKE 'KXFX%'
and
a.sid = b.sid
and
b.paddr = c.paddr (+)
and
event NOT IN
(
'lock element cleanup',
'pmon timer',
'rdbms ipc message',
'smon timer',
'SQL*Net message from client',
'SQL*Net break/reset to client',
'SQL*Net message to client',
'SQL*Net more data to client',
'dispatcher timer',
'Null event',
'io done',
'parallel query dequeue wait',
'parallel query idle wait - Slaves',
'pipe get',
'PL/SQL lock timer',
'slave wait',
'virtual circuit status',
'WMON goes to sleep'
)
order by 4 desc
;



SELECT
a.sid c1,
decode(
b.username,
NULL,c.name,
b.username) c2,
a.event c3,
a.seconds_in_wait c4,
a.wait_time c5,
a.state c6,
a.p1text c7,
a.p1 c8,
-- a.p1raw c9,
a.p2text c10,
a.p2 c11,
-- a.p2raw c12,
a.p3text c13,
a.p3 c14
-- a.p3raw c15
FROM
sys.v_$session_wait a,
sys.v_$session b,
sys.v_$bgprocess c
where
event NOT LIKE 'DFS%'
and
event NOT LIKE 'KXFX%'
and
a.sid = b.sid
and
b.paddr = c.paddr (+)
and
event NOT IN
('lock element cleanup',
'pmon timer',
'rdbms ipc message',
'smon timer',
'SQL*Net message from client',
'SQL*Net break/reset to client',
'SQL*Net message to client',
'SQL*Net more data to client',
'dispatcher timer',
'Null event',
'parallel query dequeue wait',
'parallel query idle wait - Slaves',
'pipe get',
'PL/SQL lock timer',
'slave wait',
'virtual circuit status',
'WMON goes to sleep'
)
order by
4 desc
;


To find request group for particular concurrent program

select fcp.user_concurrent_program_name, fcp.concurrent_program_name, frg.request_group_name
from
fnd_request_group_units frgu,
fnd_concurrent_programs_vl fcp,
fnd_request_groups frg
where fcp.user_concurrent_program_name='Jacmel 810 Report'
and frgu.request_unit_id = fcp.concurrent_program_id
and frg.request_group_id = frgu.request_group_id


To make sequence to no cache;

alter sequence FND_DOC_SEQ_1215_S increment by 1 nocache;

To create sequence:

create sequence AS_SALES FORCE_S increment By 1 start with 1 minvalue 1
maxvalue 2000000000 nocycle noorder nocache


To find the profile values to change when we clone the instance


select spo.profile_option_name,spov.profile_option_value,spov.level_id,spov.level_value
from fnd_profile_options spo,fnd_profile_option_values spov where spo.profile_option_id=spov.profile_option_id
and spov.profile_option_value like '%PRD%';


select spo.profile_option_name,spov.profile_option_value,spov.level_id,spov.level_value,fpov.user_profile_option_name
from fnd_profile_options spo,fnd_profile_option_values spov,fnd_profile_options_vl fpov where spo.profile_option_id=spov.profile_option_id
and fpov.profile_option_name=spo.profile_option_name and fpov.user_profile_option_Name like '%Debug%'

TO find the user_profile_name

select * from fnd_profile_options_TL where profile_option_name ='ECE_OUT_FILE_PATH';

select * from fnd_responsibility_tl where responsibility_id='20674'

select * from fnd_application_vl where application_id='521'

AOL/J Test

http(s)://:/OA_HTML/jsp/fnd/aoljtest.jsp


To Kill Concurrent Manager manually (Not recommended all time This should be user when you find ICM Actual '0' and Target 1 in CM Administer form)

ps -ef | grep FND | grep -v grep | awk '{print $2}' | xargs kill -9

To kill VNC session

Vncserver -kill: 22009
vncserver -kill: 2

To Find more Memory consuming processes

ps -eo pcpu,%mem,cmd,pid|sort -k2 -r|more

To Remove file like logs,out files...etc older and 10,20,30 days corresondingly(You can change mtime variable as per your req)

find /path -type f -mtime +10 -exec rm {} \;

find /path -type f -mtime +20 -exec rm {} \;

find /path -type f -mtime +30 -exec rm {} \;

Applying a Patch Using adpatch Defaults File:

adpatch defaultsfile=$APPL_TOP/admin/$SID/defaults.txt logfile=654321.log patchtop=/patches/654321 driver=u654321.drv interactive=no workers=4

Select forecast_designator, forecast_set, creation_date, last_update_date from MRP_FORECAST_DESIGNATORS;

To Find DB object using any Session (here 175 is session ID when event like "db file sequential read")

select * from v$session_wait where sid=175

In below query uses values from above for ex: below 164 file id from above query output P1 Column and 60859(Block#) from P2 column

select segment_name,segment_type from dba_extents
where file_id = 164 and 60859 between block_id and (block_id + blocks - 1);

How to deal with inactive JDBC

select sid,serial#,
module, program, status,
to_char(logon_time,'dd-mm-yy hh:mi:ss') "LOGON",
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) "IDLE",
program from v$session
where type='USER' and program like 'JDBC%'
and module not like'%SVC%'
and logon_timeand (floor(mod(last_call_et,3600)/60)>30 or floor(last_call_et/3600)>1)
order by last_call_et;


select spid from v$process where addr in
(select paddr from v$session where
type='USER' and program like 'JDBC%'
and module not like'%SVC%'
and logon_timeand (floor(mod(last_call_et,3600)/60)>30 or floor(last_call_et/3600)>1))


select * from v$session where
type='USER' and program like 'JDBC%'
and module like'%SVC%'

How To Identify The apps User Using The O/S PID For Forms Users In 11i

You have to pass the UNIX process id to this script

column "User Name" format a10
column "ClPID" format a8

select
d.user_name "User Name",
b.sid SID,b.serial# "Serial#", c.spid "srvPID", a.SPID "ClPID",
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 a.SPID = &PID

To Check Temp tablespace utilization:

select ss.tablespace_name,sum((ss.used_blocks*ts.blocksize))/1024/1024 mb
from gv$sort_segment ss, sys.ts$ ts
where ss.tablespace_name = ts.name
group by ss.tablespace_name;

Long Running Requests(The below query will show request took more than 1 hr to complete)

SELECT request_id, user_concurrent_program_name,
actual_start_date date_started, actual_completion_date,
TO_CHAR (NVL ((((actual_completion_date - actual_start_date) * 100000)/60
),
0
),
'99999.999'
) time_taken
FROM fnd_conc_req_summary_v
WHERE actual_start_date > SYSDATE - 10
AND NVL (((actual_completion_date - actual_start_date) * 100000), 0) >3600
ORDER BY 3 DESC



Error out Requests

SELECT request_id, user_concurrent_program_name, completion_text,
argument_text, actual_start_date, actual_completion_date, requestor
FROM fnd_conc_req_summary_v
WHERE actual_start_date > SYSDATE - 10 AND phase_code = 'C' AND status_code = 'E'
ORDER BY user_concurrent_program_name

Order Import stats


select order_number,h.creation_date, count(*)
from oe_order_headers_all h,
oe_order_lines_all l
where h.header_id=l.header_id
and trunc(h.creation_date)>trunc(sysdate)-1
group by order_number,h.creation_date;

SELECT COUNT(*)
FROM oe_order_headers_all
WHERE creation_date > SYSDATE – 1;

SELECT COUNT(*)
FROM oe_order_lines_all
WHERE orig_sys_document_ref IN (
SELECT orig_sys_document_ref
FROM oe_order_headers_all
WHERE creation_date > SYSDATE – 1);


select sum(count(*))
from oe_order_headers_all h,
oe_order_lines_all l
where h.header_id=l.header_id
and trunc(h.creation_date)>trunc(sysdate)-1
group by order_number,h.creation_date;

SELECT COUNT(*)
FROM oe_order_lines_all
WHERE orig_sys_document_ref IN (
SELECT orig_sys_document_ref
FROM oe_order_headers_all
WHERE sold_to_org_id = 3284
AND creation_date > SYSDATE – 1);


SELECT COUNT(*)
FROM oe_order_headers_all
WHERE sold_to_org_id = 3284
AND creation_date > SYSDATE – 1;

To update responsibility effective end date (Unable to find responsibility even after end date removed)

Update WF_LOCAL_USER_ROLES set effective_end_date=to_date(’01-01-9999’,’dd-mm-yyyy’) where role_name=’&role_name’ and user_name=’&username’;


Useful Concurrent request quires

SELECT request_id,user_concurrent_program_name,actual_start_date,actual_completion_date
FROM fnd_conc_req_summary_v
where to_char(actual_start_date,'DD-MM-YYYY HH24MISS') between '13-10-2008 210000' and '14-10-2008 100000'
order by actual_start_date,'DD-MM-YYYY HH24MISS'

SELECT request_id,user_concurrent_program_name,actual_start_date,actual_completion_date
FROM fnd_conc_req_summary_v WHERE TO_CHAR(actual_start_date,'MMDDYYYY HHMISS') BETWEEN '10162008 210000' AND '10172008 100000'
ORDER BY actual_start_date

SELECT request_id,user_concurrent_program_name,actual_start_date,actual_completion_date
FROM fnd_conc_req_summary_v WHERE TO_CHAR(actual_start_date,'MMDDYYYY HHMISS') > '101308 210000'
AND TO_CHAR(actual_start_date,'MMDDYYYY HHMISS') < '10142008 100000'
ORDER BY actual_start_date

Workflow:

1. select item_type, count from wf_items where end_date is null group by item_type order by 2 desc;

2. select item_type, count from wf_items where end_date is not null group by item_type order by 2 desc;


3. select item_type, count from WF_ITEM_ATTRIBUTE_VALUES group by item_type order by 2 desc;

4. select item_type, count from WF_ITEM_ACTIVITY_STATUSES group by item_type order by 2 desc;

5. select text from dba_source where name = 'WF_PURGE' and line = 2;

6. select parent_item_type, count from wf_items where item_type = 'WFERROR' and end_date is not null group by parent_item_type order by 2 desc;

7. select parent_item_type, count from wf_items where item_type = 'WFERROR' and end_date is null group by parent_item_type order by 2 desc;


To find out the raw trace name and location for the concurrent program
Input will be request id:

column traceid format a8
column tracename format a80
column user_concurrent_program_name format a40
column execname format a15
column enable_trace format a12
set lines 80
set pages 22
set head off

SELECT 'Request id: '||request_id ,
'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace Name:
'||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
'Prog. Name: '||prog.user_concurrent_program_name,
'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
'Status : '||decode(phase_code,'R','Running')
||'-'||decode(status_code,'R','Normal'),
'SID Serial: '||ses.sid||','|| ses.serial#,
'Module : '||ses.module
from fnd_concurrent_requests req, v$session ses, v$process proc,
v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog,
fnd_executables execname
where req.request_id = &request
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id;


To check the time line of the request:

SELECT request_id, TO_CHAR( request_date, 'DD-MON-YYYY HH24:MI:SS' )
request_date, TO_CHAR( requested_start_date,'DD-MON-YYYY HH24:MI:SS' )
requested_start_date, TO_CHAR( actual_start_date, 'DD-MON-YYYY HH24:MI:SS' )
actual_start_date, TO_CHAR( actual_completion_date, 'DD-MON-YYYY HH24:MI:SS' )
actual_completion_date, TO_CHAR( sysdate, 'DD-MON-YYYY HH24:MI:SS' )
current_date, ROUND( ( NVL( actual_completion_date, sysdate ) - actual_start_date ) * 24, 2 ) duration
FROM fnd_concurrent_requests
WHERE request_id = TO_NUMBER('&p_request_id');


SELECT M1.ORGANIZATION_ID ,M2.COMPILE_DESIGNATOR,COUNT(M1.INVENTORY_ITEM_ID) FROM MTL_SYSTEM_ITEMS_B M1,MRP_SYSTEM_ITEMS M2 WHERE M1.INVENTORY_ITEM_ID = M2.INVENTORY_ITEM_ID AND M1.ORGANIZATION_ID = M2.ORGANIZATION_ID AND M1.ORGANIZATION_ID IN (41,4) AND M2.COMPILE_DESIGNATOR IN(SELECT DISTINCT COMPILE_DESIGNATOR FROM MRP_SYSTEM_ITEMS) GROUP BY M1.ORGANIZATION_ID,M2.COMPILE_DESIGNATOR ORDER BY M1.ORGANIZATION_ID ,M2.COMPILE_DESIGNATOR

check version of Oracle Xml parser for java

under java_top

unzip -l appsborg2.zip | grep 9.0.4

For ORA-54 error:

select do.owner, do.object_name,do.object_type,dl.session_id, vs.serial#, vs.program,vs.machine, vs.osuser from dba_locks dl, dba_objects do,v$session vs where do.object_name='' and do.object_type = 'TABLE' and dl.lock_id1 = do.object_id and vs.sid = dl.session_id;

For MRP workbench performance:

SELECT *
FROM dba_indexes
WHERE table_name IN
('FND_LOOKUP_VALUES',
'MTL_ABC_CLASSES',
'MTL_SYSTEM_ITEMS_B',
'MRP_SYSTEM_ITEMS',
'MRP_PLAN_ORGANIZATIONS',
'MRP_PLANS');

alter index APPLSYS.FND_LOOKUP_VALUES_U1 rebuild online
alter index APPLSYS.FND_LOOKUP_VALUES_U2 rebuild online
alter index INV.MTL_ABC_CLASSES_U1 rebuild online
alter index INV.MTL_ABC_CLASSES_U2 rebuild online
alter index MRP.MRP_PLANS_U1 rebuild online
alter index MRP.MRP_PLAN_ORGANIZATIONS_U1 rebuild online
alter index MRP.MRP_SYSTEM_ITEMS_N1 rebuild online
alter index MRP.MRP_SYSTEM_ITEMS_N2 rebuild online
alter index MRP.MRP_SYSTEM_ITEMS_U1 rebuild online
alter index INV.MTL_SYSTEM_ITEMS_B_N10 rebuild online
alter index INV.MTL_SYSTEM_ITEMS_B_N12 rebuild online
alter index INV.MTL_SYSTEM_ITEMS_B_N13 rebuild online
alter index INV.MTL_SYSTEM_ITEMS_B_N14 rebuild online
alter index INV.MTL_SYSTEM_ITEMS_B_N8 rebuild online
alter index INV.MTL_SYSTEM_ITEMS_B_N9 rebuild online
alter index INV.MTL_SYSTEM_ITEMS_B_N11 rebuild online
alter index INV.MTL_SYSTEM_ITEMS_B_N1 rebuild online
alter index INV.MTL_SYSTEM_ITEMS_B_N6 rebuild online
alter index INV.MTL_SYSTEM_ITEMS_B_U1 rebuild online
alter index INV.MTL_SYSTEM_ITEMS_B_N2 rebuild online
alter index INV.MTL_SYSTEM_ITEMS_B_N3 rebuild online
alter index INV.MTL_SYSTEM_ITEMS_B_N7 rebuild online
alter index INV.MTL_SYSTEM_ITEMS_B_N4 rebuild online
alter index INV.MTL_SYSTEM_ITEMS_B_N5 rebuild online

About TEMP Tablespace:

select * from v$temp_space_header

select file_id, bytes/1024/1024,user_bytes/1024/1024 from dba_temp_files

select sysdate dtstamp,
s.tablespace_name,
d.tbspc_mb,
s.total_blocks*8192/1024/1024 temp_tot_mb,
s.used_blocks*8192/1024/1024 temp_used_mb,
s.free_blocks*8192/1024/1024 temp_free_mb
from v$sort_segment s,
(select tablespace_name,sum(bytes/1024/1024) tbspc_mb
from dba_data_files
group by tablespace_name
union
select tablespace_name,sum(bytes/1024/1024) tbspc_mb
from dba_temp_files
group by tablespace_name) d
where s.tablespace_name=d.tablespace_name;

select * from v$sort_segment where tablespace_name='TEMP';

Creator of Sort Segment in Oracle 8 and above


SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,
a.username, a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;

How Do You Find Who And What SQL Is Using Temp Segments?

SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text
FROM v$session a, v$sort_usage b, v$sqlarea c
WHERE a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
ORDER BY b.tablespace, b.blocks;

For 10.1 and above:

SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c
WHERE a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
ORDER BY b.tablespace, b.blocks;

The concurrent request was hold on by whom


SQL> select REQUEST_ID,To_char(LAST_UPDATE_DATE,'DD-MON-YYYY=>hh24:mi:ss') LAST_UPDATE_DATE,LAST_UPDATED_BY,REQUEST_DATE,REQUESTED_BY,PROGRAM_APPLICATION_ID,CONCURRENT_PROGRAM_ID,LAST_UPDATE_LOGIN from fnd_concurrent_requests where REQUEST_ID='&reqid';

SQL> select USER_ID,USER_NAME from fnd_user where LAST_UPDATED_BY=9852;

script to find out NLS patches applied or nor


SELECT driver.driver_file_name
,TO_CHAR(run.start_date,'DD-Mon HH24:MI:SS') start_date
,TO_CHAR(run.end_date ,'DD-Mon HH24:MI:SS') end_date
,lang.language
FROM ad_patch_runs run
,ad_patch_driver_langs lang
,ad_patch_drivers driver
,ad_applied_patches applied
WHERE run.patch_driver_id = driver.patch_driver_id
AND driver.applied_patch_id = applied.applied_patch_id
AND applied.patch_name = '&patch_number'
AND lang.patch_driver_id = driver.patch_driver_id
ORDER BY 1,2,3;

Running concurrent request


set lines 132
col os form A7 head AppProc
col spid form a6 head DBProc
col program form A43 trunc
set pages 38
col time form 9999.99 head Elapsed
col "Req Id" form 9999999
col "Parent" form a7
col "Prg Id" form 999999
col qname head "Concurrent Manager Queue" format a25 trunc
col sid format 99999 head SID
set recsep off
select q.concurrent_queue_name || ' - ' || target_node qname
,a.request_id "Req Id"
,decode(a.parent_request_id,-1,NULL,a.parent_request_id) "Parent"
,a.concurrent_program_id "Prg Id"
,a.phase_code,a.status_code
,nvl(a.os_process_id,b.os_process_id) "OS"
,vs.sid
,vp.spid
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 "Time"
,c.concurrent_program_name||' - '||
c2.user_concurrent_program_name "program"
from APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs_tl c2
,APPLSYS.fnd_concurrent_programs c
,gv$session vs
,gv$process vp
where a.controlling_manager = b.concurrent_process_id
and a.concurrent_program_id = c.concurrent_program_id
and a.program_application_id = c.application_id
and c2.concurrent_program_id = c.concurrent_program_id
and a.phase_code in ('I','P','R','T')
and b.queue_application_id = q.application_id
and b.concurrent_queue_id = q.concurrent_queue_id
and c2.language = 'US'
and vs.process (+) = b.os_process_id
and vs.paddr = vp.addr (+)
and vs.inst_id = vp.inst_id
order by 1,2
/


concurrent request status

col os form A6
col program form A40
set pages 38
set verify off
col time head Elapsed form 9999.99
col "Req Id" form 9999999
col "Prg Id" form 999999
col "Started On" format a10
col "Finished On" format a10
col "Submitted By" format a30 trunc
col argument_text head "Arguments" format a40
col statustxt head Status format a10 trunc
col phasetxt head Phase format a10 trunc
set recsep off
accept cmreqid number prompt 'What is the concurrent request id : '
select l2.meaning phasetxt
,l1.meaning statustxt
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 "Time"
,to_char(a.actual_start_date,'mm/dd/yy hh:mi:ssAM') "Started On"
,to_char(a.actual_completion_date,'mm/dd/yy hh:mi:ssAM') "Finished On"
,u.user_name || ' - ' || u.description "Submitted By"
,a.argument_text
from APPLSYS.fnd_Concurrent_requests a
,applsys.fnd_user u
,applsys.fnd_lookup_values l1
,applsys.fnd_lookup_values l2
where u.user_id = a.requested_by
and a.request_id = &cmreqid
and l1.lookup_type = 'CP_STATUS_CODE'
and l1.lookup_code = a.status_code
and l1.language = 'US'
and l1.enabled_flag = 'Y'
and (l1.start_date_active <= sysdate and l1.start_date_active is not null)
and (l1.end_date_active > sysdate or l1.end_date_active is null)
and l2.lookup_type = 'CP_PHASE_CODE'
and l2.lookup_code = a.phase_code
and l2.language = 'US'
and l2.enabled_flag = 'Y' and (l2.start_date_active <= sysdate and l2.start_date_active is not null)
and (l2.end_date_active > sysdate or l2.end_date_active is null)

User Log time and status:

select distinct user_name,ss.action responsibility,ss.Module form,to_char(start_time,'DD-MM-YYYY HH24:MI:SS') last_login_time,fl.end_time
from fnd_logins fl, fnd_user fu ,v$process pr,v$session ss
where fu.user_id= fl.user_id and fl.pid=pr.pid and ss.paddr=pr.addr;

select * from v$session where status='INACTIVE' and paddr in (select addr from v$process where pid in
(select pid from fnd_logins where user_id in (select user_id from fnd_user where user_name ='&USERNAME')));

To get DDL statements for Table Or Indexes:

select dbms_metadata.get_ddl('TABLE','','') from dual;

select dbms_metadata.get_ddl('INDEX','','') from dual;

DB Object Fragmentation(HWM):

Analyze table compute statistics ;

SELECT TABLE_NAME , (BLOCKS *8192 / 1024/1024 ) - (NUM_ROWS*AVG_ROW_LEN/1024/1024)
"Data lower than HWM in MB" FROM DBA_TABLES WHERE UPPER(owner) =UPPER('&OWNER') order by 2 desc;