The queries mentioned here are for my own purpose , please ensure and be careful before executing any DDL or delete script
1.Database Size or Size of Database
set linesize 200
set pagesize 200
SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)",
SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024),
SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;
1.Database Size or Size of Database
set linesize 200
set pagesize 200
SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)",
SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024),
SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;
2.How to Determine Which Manager Ran a Specific Concurrent Request?
col USER_CONCURRENT_QUEUE_NAME for a100
select b.USER_CONCURRENT_QUEUE_NAME from fnd_concurrent_processes a,
fnd_concurrent_queues_vl b, fnd_concurrent_requests c
where a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID
and a.CONCURRENT_PROCESS_ID = c.controlling_manager
and c.request_id = '&conc_reqid';
3.Concurrent manager status for a given sid?
col MODULE for a20
col OSUSER for a10
col USERNAME for a10
set num 10
col MACHINE for a20
set lines 200
col SCHEMANAME for a10
select s.sid,s.serial#,p.spid os_pid,s.status, s.osuser,s.username, s.MACHINE,s.MODULE, s.SCHEMANAME,
s.action from gv$session s, gv$process p WHERE s.paddr = p.addr and s.sid = '&oracle_sid';
4. Find out request id from Oracle_Process Id:
select REQUEST_ID,ORACLE_PROCESS_ID,OS_PROCESS_Id from apps.fnd_concurrent_requests where ORACLE_PROCESS_ID='&a';
5.To find sid,serial# for a given concurrent request id?
set lines 200
SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_id
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID
AND a.phase_code = 'R';
6.To find concurrent program name,phase code,status code for a given request id?
SELECT request_id, user_concurrent_program_name, DECODE(phase_code,'C','Completed',phase_code) phase_code, DECODE(status_code,'D', 'Cancelled' ,
'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X', 'Terminated', 'C', 'Normal', status_code) status_code, to_char(actual_start_date,'dd-mon-yy:hh24:mi:ss') Start_Date, to_char(actual_completion_date,'dd-mon-yy:hh24:mi:ss'), completion_text FROM apps.fnd_conc_req_summary_v WHERE request_id = '&req_id' ORDER BY 6 DESC;
7.To find the sql query for a given concurrent request sid?
select sid,sql_text from gv$session ses, gv$sqlarea sql where
ses.sql_hash_value = sql.hash_value(+) and ses.sql_address = sql.address(+) and ses.sid='&oracle_sid'
/
8. To find child requests
set lines 200
col USER_CONCURRENT_PROGRAM_NAME for a40
col PHASE_CODE for a10
col STATUS_CODE for a10
col COMPLETION_TEXT for a20
SELECT sum.request_id,req.PARENT_REQUEST_ID,sum.user_concurrent_program_name, DECODE(sum.phase_code,'C','Completed',sum.phase_code) phase_code, DECODE(sum.status_code,'D', 'Cancelled' ,
'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X', 'Terminated', 'C', 'Normal', sum.status_code) status_code, sum.actual_start_date, sum.actual_completion_date, sum.completion_text FROM apps.fnd_conc_req_summary_v sum, apps.fnd_concurrent_requests req where req.request_id=sum.request_id and req.PARENT_REQUEST_ID = '&parent_concurrent_request_id';
9. Cancelling Concurrent request :
update fnd_concurrent_requests
set status_code='D', phase_code='C'
where request_id=&req_id;
10. Kill sessions program wise
select 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' immediate;' from v$session where MODULE like '';
11 .Concurrent Request running by SID
SELECT a.request_id,
d.sid as Oracle_SID,
d.serial#,
d.osuser,
d.process,
c.SPID as OS_Process_ID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND d.sid = &SID;
12. Find out request id from Oracle_Process Id:
select REQUEST_ID,ORACLE_PROCESS_ID,OS_PROCESS_Id from fnd_concurrent_requests where ORACLE_PROCESS_ID='&a';
13. Oracle Concurrent Request Error Script (requests which were error ed out)
SELECT a.request_id "Req Id"
,a.phase_code,a.status_code
, actual_start_date
, actual_completion_date
,c.concurrent_program_name || ': ' || ctl.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 c
,APPLSYS.fnd_concurrent_programs_tl ctl
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 a.status_code = 'E'
AND a.phase_code = 'C'
AND actual_start_date > sysdate - 2
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND ctl.LANGUAGE = 'US'
ORDER BY 5 DESC;
14. Request submitted by User
SELECT
user_concurrent_program_name,
request_date,
request_id,
phase_code,
status_code
FROM
fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcp,
fnd_responsibility_tl fr,
fnd_user fu
WHERE
fcr.CONCURRENT_PROGRAM_ID = fcp.concurrent_program_id
and fcr.responsibility_id = fr.responsibility_id
and fcr.requested_by = fu.user_id
and user_name = '&user'
AND actual_start_date > sysdate - 1
ORDER BY REQUEST_DATE Asc;
15.Concurrent Program enable with trace
col User_Program_Name for a40
col Last_Updated_By for a30
col DESCRIPTION for a30
SELECT A.CONCURRENT_PROGRAM_NAME "Program_Name",
SUBSTR(A.USER_CONCURRENT_PROGRAM_NAME,1,40) "User_Program_Name",
SUBSTR(B.USER_NAME,1,15) "Last_Updated_By",
SUBSTR(B.DESCRIPTION,1,25) DESCRIPTION
FROM APPS.FND_CONCURRENT_PROGRAMS_VL A, APPLSYS.FND_USER B
WHERE A.ENABLE_TRACE='Y'
AND A.LAST_UPDATED_BY=B.USER_ID;
col USER_CONCURRENT_QUEUE_NAME for a100
select b.USER_CONCURRENT_QUEUE_NAME from fnd_concurrent_processes a,
fnd_concurrent_queues_vl b, fnd_concurrent_requests c
where a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID
and a.CONCURRENT_PROCESS_ID = c.controlling_manager
and c.request_id = '&conc_reqid';
3.Concurrent manager status for a given sid?
col MODULE for a20
col OSUSER for a10
col USERNAME for a10
set num 10
col MACHINE for a20
set lines 200
col SCHEMANAME for a10
select s.sid,s.serial#,p.spid os_pid,s.status, s.osuser,s.username, s.MACHINE,s.MODULE, s.SCHEMANAME,
s.action from gv$session s, gv$process p WHERE s.paddr = p.addr and s.sid = '&oracle_sid';
4. Find out request id from Oracle_Process Id:
select REQUEST_ID,ORACLE_PROCESS_ID,OS_PROCESS_Id from apps.fnd_concurrent_requests where ORACLE_PROCESS_ID='&a';
5.To find sid,serial# for a given concurrent request id?
set lines 200
SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_id
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID
AND a.phase_code = 'R';
6.To find concurrent program name,phase code,status code for a given request id?
SELECT request_id, user_concurrent_program_name, DECODE(phase_code,'C','Completed',phase_code) phase_code, DECODE(status_code,'D', 'Cancelled' ,
'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X', 'Terminated', 'C', 'Normal', status_code) status_code, to_char(actual_start_date,'dd-mon-yy:hh24:mi:ss') Start_Date, to_char(actual_completion_date,'dd-mon-yy:hh24:mi:ss'), completion_text FROM apps.fnd_conc_req_summary_v WHERE request_id = '&req_id' ORDER BY 6 DESC;
7.To find the sql query for a given concurrent request sid?
select sid,sql_text from gv$session ses, gv$sqlarea sql where
ses.sql_hash_value = sql.hash_value(+) and ses.sql_address = sql.address(+) and ses.sid='&oracle_sid'
/
8. To find child requests
set lines 200
col USER_CONCURRENT_PROGRAM_NAME for a40
col PHASE_CODE for a10
col STATUS_CODE for a10
col COMPLETION_TEXT for a20
SELECT sum.request_id,req.PARENT_REQUEST_ID,sum.user_concurrent_program_name, DECODE(sum.phase_code,'C','Completed',sum.phase_code) phase_code, DECODE(sum.status_code,'D', 'Cancelled' ,
'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X', 'Terminated', 'C', 'Normal', sum.status_code) status_code, sum.actual_start_date, sum.actual_completion_date, sum.completion_text FROM apps.fnd_conc_req_summary_v sum, apps.fnd_concurrent_requests req where req.request_id=sum.request_id and req.PARENT_REQUEST_ID = '&parent_concurrent_request_id';
9. Cancelling Concurrent request :
update fnd_concurrent_requests
set status_code='D', phase_code='C'
where request_id=&req_id;
10. Kill sessions program wise
select 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' immediate;' from v$session where MODULE like '
11 .Concurrent Request running by SID
SELECT a.request_id,
d.sid as Oracle_SID,
d.serial#,
d.osuser,
d.process,
c.SPID as OS_Process_ID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND d.sid = &SID;
12. Find out request id from Oracle_Process Id:
select REQUEST_ID,ORACLE_PROCESS_ID,OS_PROCESS_Id from fnd_concurrent_requests where ORACLE_PROCESS_ID='&a';
13. Oracle Concurrent Request Error Script (requests which were error ed out)
SELECT a.request_id "Req Id"
,a.phase_code,a.status_code
, actual_start_date
, actual_completion_date
,c.concurrent_program_name || ': ' || ctl.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 c
,APPLSYS.fnd_concurrent_programs_tl ctl
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 a.status_code = 'E'
AND a.phase_code = 'C'
AND actual_start_date > sysdate - 2
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND ctl.LANGUAGE = 'US'
ORDER BY 5 DESC;
14. Request submitted by User
SELECT
user_concurrent_program_name,
request_date,
request_id,
phase_code,
status_code
FROM
fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcp,
fnd_responsibility_tl fr,
fnd_user fu
WHERE
fcr.CONCURRENT_PROGRAM_ID = fcp.concurrent_program_id
and fcr.responsibility_id = fr.responsibility_id
and fcr.requested_by = fu.user_id
and user_name = '&user'
AND actual_start_date > sysdate - 1
ORDER BY REQUEST_DATE Asc;
15.Concurrent Program enable with trace
col User_Program_Name for a40
col Last_Updated_By for a30
col DESCRIPTION for a30
SELECT A.CONCURRENT_PROGRAM_NAME "Program_Name",
SUBSTR(A.USER_CONCURRENT_PROGRAM_NAME,1,40) "User_Program_Name",
SUBSTR(B.USER_NAME,1,15) "Last_Updated_By",
SUBSTR(B.DESCRIPTION,1,25) DESCRIPTION
FROM APPS.FND_CONCURRENT_PROGRAMS_VL A, APPLSYS.FND_USER B
WHERE A.ENABLE_TRACE='Y'
AND A.LAST_UPDATED_BY=B.USER_ID;
Sunday, May 13, 2012
16.How to check Size of all tablespaces in the instance.
set linesize 200
set pagesize 200
SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)",
SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024),
SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;
Thursday, September 29, 2011
all important scripts
<< CM >>
select decode(count(*),0,' All Active',' WARNING: Please check RUNNING vs MAX Process on CCM!')
from fnd_concurrent_queues
where running_processes <> max_processes
/
<< Running >>
select
/*+ choose */
count(*)
from
fnd_concurrent_requests fcr,
fnd_concurrent_programs c,
fnd_conc_req_summary_v fcrv
where
fcr.concurrent_program_id = c.concurrent_program_id
and fcr.program_application_id = c.application_id
and fcr.CONCURRENT_PROGRAM_ID = fcrv.CONCURRENT_PROGRAM_ID
and fcr.REQUEST_ID = fcrv.REQUEST_ID
and fcr.phase_code in ('R','T')
/
------------
List
------------
select
/*+ choose */
U.USER_NAME User_name,
fcr.request_id request_id,
to_char(round((nvl(fcr.actual_completion_date,sysdate)-fcr.actual_start_date)*1440)) Time,
c.concurrent_program_name||' - ' ||
substr(fcrv.program,1,55) PROGRAM,
fcr.CONCURRENT_PROGRAM_ID Program_id,
fcr.PARENT_REQUEST_ID Parent_id
from
fnd_concurrent_requests fcr,
fnd_concurrent_programs c,
fnd_conc_req_summary_v fcrv,
fnd_user U
where
fcr.concurrent_program_id = c.concurrent_program_id
and fcr.program_application_id = c.application_id
and fcr.CONCURRENT_PROGRAM_ID = fcrv.CONCURRENT_PROGRAM_ID
and fcr.REQUEST_ID = fcrv.REQUEST_ID
and fcr.phase_code in ('R','T')
and fcr.requested_by = U.user_id
order by request_id asc
/
<< Pending >>
select
/*+ choose */
count(*)
from
fnd_concurrent_requests fcr,
fnd_concurrent_programs c,
fnd_conc_req_summary_v fcrv
where
fcr.concurrent_program_id = c.concurrent_program_id
and fcr.program_application_id = c.application_id
and fcr.CONCURRENT_PROGRAM_ID = fcrv.CONCURRENT_PROGRAM_ID
and fcr.REQUEST_ID = fcrv.REQUEST_ID
and fcrv.REQUESTED_START_DATE < sysdate+10/1440
and fcr.phase_code in ('P')
and fcr.HOLD_FLAG != 'Y'
/
------------
List
------------
select
/*+ choose */
U.USER_NAME User_name,
fcr.request_id request_id,
to_char(round((nvl(fcr.actual_completion_date,sysdate)-fcr.actual_start_date)*1440)) Time,
c.concurrent_program_name||' - ' ||
substr(fcrv.program,1,45) PROGRAM,
fcr.CONCURRENT_PROGRAM_ID Program_id,
fcr.PARENT_REQUEST_ID Parent_id,
to_char(fcrv.REQUESTED_START_DATE,'DD-MON-RR HH24:MI') Scheduled,
DECODE(fcr.phase_code ,
'C','Completed',
'I','Inactive',
'P','Pending',
'R','Running') || ' - ' ||
DECODE(fcr.status_code,
'A','Waiting',
'B','Resuming',
'C','Normal',
'D','Cancelled',
'E','Error',
'G','Warning',
'H','On Hold',
'I','Normal',
'M','No Manager',
'P','Scheduled',
'Q','Standby',
'R','Normal',
'S','Suspended',
'T','Terminating',
'U','Disabled',
'W','Paused',
'X','Terminated',
'Z',' Waiting') Status
from
fnd_concurrent_requests fcr,
fnd_concurrent_programs c,
fnd_conc_req_summary_v fcrv,
fnd_user U
where
fcr.concurrent_program_id = c.concurrent_program_id
and fcr.program_application_id = c.application_id
and fcr.CONCURRENT_PROGRAM_ID = fcrv.CONCURRENT_PROGRAM_ID
and fcr.REQUEST_ID = fcrv.REQUEST_ID
and fcrv.REQUESTED_START_DATE < sysdate+10/1440
and fcr.phase_code in ('P')
and fcr.HOLD_FLAG != 'Y'
and fcr.requested_by = U.user_id
order by 1, 2 asc
/
<< Long Running >>
select
count(*)
from fnd_conc_req_summary_v fcrv,
fnd_concurrent_requests fcr
where fcrv.phase_code in ('R','I')
and round(((sysdate - fcrv.actual_start_date)*1440),2) > 60
and fcrv.request_id = fcr.request_id
and fcrv.program not in ('Notification Mailer')
/
------------
List
------------
select substr(fcrv.request_id,1,7)REQUEST,
lpad(decode(fcrv.phase_code,'P','Pending','R','Running','I','Inactive','Completed'),8,' ')PHASE,
decode(fcrv.status_code,
'A','Waiting',
'B','Resuming',
'C','Normal',
'F','Scheduled',
'G','Warning',
'H','On Hold',
'I','Normal',
'M','No Manager',
'Q','Standby',
'R','Normal',
'S','Suspended',
'T','Terminating',
'U','Disabled',
'W','Paused',
'X','Terminated',
'Z','Waiting',fcrv.status_code)STATUS,
rpad(substr(fcrv.program,1,65),65,' ')PROGRAM,
lpad(substr(fcrv.requestor,1,9),9,' ')REQUESTOR,
rpad(to_char(fcrv.actual_start_date,'MM/DD/RR HH24:MI:SS AM'),20,' ')START_TIME,
lpad(round(((sysdate - fcrv.actual_start_date)*1440),2),11,' ')RUN_TIME
--lpad(substr(fcr.oracle_process_id,1,7),7,' ')OS_PID
from fnd_conc_req_summary_v fcrv,
fnd_concurrent_requests fcr
where fcrv.phase_code in ('R','I')
and round(((sysdate - fcrv.actual_start_date)*1440),2) > 120
and fcrv.request_id = fcr.request_id
and fcrv.program not in ('Notification Mailer')
order by RUN_TIME desc
/
<< Sessions Forms >>
select count(*) from fnd_form_sessions_v
/
<< Sessions SQL >>
select
count(*)
from
gv$session a,
v$dispatcher b,
v$circuit c,
v$process d
where
a.saddr = c.saddr(+)
and a.server is not null
and b.paddr(+) = c.dispatcher
and a.paddr(+) = d.addr
and ( UPPER(a.program) like '%SQL%'
or UPPER(a.program) like '%TOAD%'
or a.module like '%SQL%'
or a.module like 'T.O.A.D%'
or a.module like 'TOAD%'
)
/
<< Sessions Web >>
SELECT
count(*)
FROM v$process p,
gv$session s
WHERE p.addr = s.paddr
and s.program = 'JDBC Thin Client'
/
<< Global Locks >>
select count(*)
from gv$lock a
where a.id1 in ( select id1 from gv$lock where request <> 0 )
/
------------
List
------------
select a.inst_id, a.sid, a.type, a.id1, a.id2,
decode(a.lmode, 0, 'NONE', 1, 'NULL', 2, 'ROW SHARE',
3, 'ROW EXCLUSIVE', 4, 'SHARE', 5, 'SHARE ROW EXCLUSIVE',
6, 'EXCLUSIVE', '?'),
decode(a.request, 0, 'NONE', 1, 'NULL', 2, 'ROW SHARE',
3, 'ROW EXCLUSIVE', 4, 'SHARE', 5, 'SHARE ROW EXCLUSIVE',
6, 'EXCLUSIVE', '?'),
a.block
from gv$lock a
where a.id1 in ( select id1 from gv$lock where request <> 0 )
order by a.id1, a.request, a.sid
/
<< Memory Hogs > 512MB >>
select s.inst_id, s.sid, name, round(value/1024/1024) Mem_in_mb
from gv$sesstat s, v$statname n, gv$session p
where n.statistic# = s.statistic#
and s.inst_id = p.inst_id
and s.sid = p.sid
and name like '%pga memory%'
and value > 1*1024*1024*512
/
------------
List
------------
select s.inst_id, s.sid, name, round(value/1024/1024) Mem_in_mb
from gv$sesstat s, v$statname n, gv$session p
where n.statistic# = s.statistic#
and s.inst_id = p.inst_id
and s.sid = p.sid
and name like '%pga memory%'
and value > 1*1024*1024*512
/
<< Top 10 SQL >>
select * from
(select sql_text,
cpu_time/1000000 cpu_time,
elapsed_time/1000000 elapsed_time,
disk_reads,
buffer_gets,
rows_processed
from v$sqlarea
order by cpu_time desc, disk_reads desc
)
where rownum < 11
/
select decode(count(*),0,' All Active',' WARNING: Please check RUNNING vs MAX Process on CCM!')
from fnd_concurrent_queues
where running_processes <> max_processes
/
<< Running >>
select
/*+ choose */
count(*)
from
fnd_concurrent_requests fcr,
fnd_concurrent_programs c,
fnd_conc_req_summary_v fcrv
where
fcr.concurrent_program_id = c.concurrent_program_id
and fcr.program_application_id = c.application_id
and fcr.CONCURRENT_PROGRAM_ID = fcrv.CONCURRENT_PROGRAM_ID
and fcr.REQUEST_ID = fcrv.REQUEST_ID
and fcr.phase_code in ('R','T')
/
------------
List
------------
select
/*+ choose */
U.USER_NAME User_name,
fcr.request_id request_id,
to_char(round((nvl(fcr.actual_completion_date,sysdate)-fcr.actual_start_date)*1440)) Time,
c.concurrent_program_name||' - ' ||
substr(fcrv.program,1,55) PROGRAM,
fcr.CONCURRENT_PROGRAM_ID Program_id,
fcr.PARENT_REQUEST_ID Parent_id
from
fnd_concurrent_requests fcr,
fnd_concurrent_programs c,
fnd_conc_req_summary_v fcrv,
fnd_user U
where
fcr.concurrent_program_id = c.concurrent_program_id
and fcr.program_application_id = c.application_id
and fcr.CONCURRENT_PROGRAM_ID = fcrv.CONCURRENT_PROGRAM_ID
and fcr.REQUEST_ID = fcrv.REQUEST_ID
and fcr.phase_code in ('R','T')
and fcr.requested_by = U.user_id
order by request_id asc
/
<< Pending >>
select
/*+ choose */
count(*)
from
fnd_concurrent_requests fcr,
fnd_concurrent_programs c,
fnd_conc_req_summary_v fcrv
where
fcr.concurrent_program_id = c.concurrent_program_id
and fcr.program_application_id = c.application_id
and fcr.CONCURRENT_PROGRAM_ID = fcrv.CONCURRENT_PROGRAM_ID
and fcr.REQUEST_ID = fcrv.REQUEST_ID
and fcrv.REQUESTED_START_DATE < sysdate+10/1440
and fcr.phase_code in ('P')
and fcr.HOLD_FLAG != 'Y'
/
------------
List
------------
select
/*+ choose */
U.USER_NAME User_name,
fcr.request_id request_id,
to_char(round((nvl(fcr.actual_completion_date,sysdate)-fcr.actual_start_date)*1440)) Time,
c.concurrent_program_name||' - ' ||
substr(fcrv.program,1,45) PROGRAM,
fcr.CONCURRENT_PROGRAM_ID Program_id,
fcr.PARENT_REQUEST_ID Parent_id,
to_char(fcrv.REQUESTED_START_DATE,'DD-MON-RR HH24:MI') Scheduled,
DECODE(fcr.phase_code ,
'C','Completed',
'I','Inactive',
'P','Pending',
'R','Running') || ' - ' ||
DECODE(fcr.status_code,
'A','Waiting',
'B','Resuming',
'C','Normal',
'D','Cancelled',
'E','Error',
'G','Warning',
'H','On Hold',
'I','Normal',
'M','No Manager',
'P','Scheduled',
'Q','Standby',
'R','Normal',
'S','Suspended',
'T','Terminating',
'U','Disabled',
'W','Paused',
'X','Terminated',
'Z',' Waiting') Status
from
fnd_concurrent_requests fcr,
fnd_concurrent_programs c,
fnd_conc_req_summary_v fcrv,
fnd_user U
where
fcr.concurrent_program_id = c.concurrent_program_id
and fcr.program_application_id = c.application_id
and fcr.CONCURRENT_PROGRAM_ID = fcrv.CONCURRENT_PROGRAM_ID
and fcr.REQUEST_ID = fcrv.REQUEST_ID
and fcrv.REQUESTED_START_DATE < sysdate+10/1440
and fcr.phase_code in ('P')
and fcr.HOLD_FLAG != 'Y'
and fcr.requested_by = U.user_id
order by 1, 2 asc
/
<< Long Running >>
select
count(*)
from fnd_conc_req_summary_v fcrv,
fnd_concurrent_requests fcr
where fcrv.phase_code in ('R','I')
and round(((sysdate - fcrv.actual_start_date)*1440),2) > 60
and fcrv.request_id = fcr.request_id
and fcrv.program not in ('Notification Mailer')
/
------------
List
------------
select substr(fcrv.request_id,1,7)REQUEST,
lpad(decode(fcrv.phase_code,'P','Pending','R','Running','I','Inactive','Completed'),8,' ')PHASE,
decode(fcrv.status_code,
'A','Waiting',
'B','Resuming',
'C','Normal',
'F','Scheduled',
'G','Warning',
'H','On Hold',
'I','Normal',
'M','No Manager',
'Q','Standby',
'R','Normal',
'S','Suspended',
'T','Terminating',
'U','Disabled',
'W','Paused',
'X','Terminated',
'Z','Waiting',fcrv.status_code)STATUS,
rpad(substr(fcrv.program,1,65),65,' ')PROGRAM,
lpad(substr(fcrv.requestor,1,9),9,' ')REQUESTOR,
rpad(to_char(fcrv.actual_start_date,'MM/DD/RR HH24:MI:SS AM'),20,' ')START_TIME,
lpad(round(((sysdate - fcrv.actual_start_date)*1440),2),11,' ')RUN_TIME
--lpad(substr(fcr.oracle_process_id,1,7),7,' ')OS_PID
from fnd_conc_req_summary_v fcrv,
fnd_concurrent_requests fcr
where fcrv.phase_code in ('R','I')
and round(((sysdate - fcrv.actual_start_date)*1440),2) > 120
and fcrv.request_id = fcr.request_id
and fcrv.program not in ('Notification Mailer')
order by RUN_TIME desc
/
<< Sessions Forms >>
select count(*) from fnd_form_sessions_v
/
<< Sessions SQL >>
select
count(*)
from
gv$session a,
v$dispatcher b,
v$circuit c,
v$process d
where
a.saddr = c.saddr(+)
and a.server is not null
and b.paddr(+) = c.dispatcher
and a.paddr(+) = d.addr
and ( UPPER(a.program) like '%SQL%'
or UPPER(a.program) like '%TOAD%'
or a.module like '%SQL%'
or a.module like 'T.O.A.D%'
or a.module like 'TOAD%'
)
/
<< Sessions Web >>
SELECT
count(*)
FROM v$process p,
gv$session s
WHERE p.addr = s.paddr
and s.program = 'JDBC Thin Client'
/
<< Global Locks >>
select count(*)
from gv$lock a
where a.id1 in ( select id1 from gv$lock where request <> 0 )
/
------------
List
------------
select a.inst_id, a.sid, a.type, a.id1, a.id2,
decode(a.lmode, 0, 'NONE', 1, 'NULL', 2, 'ROW SHARE',
3, 'ROW EXCLUSIVE', 4, 'SHARE', 5, 'SHARE ROW EXCLUSIVE',
6, 'EXCLUSIVE', '?'),
decode(a.request, 0, 'NONE', 1, 'NULL', 2, 'ROW SHARE',
3, 'ROW EXCLUSIVE', 4, 'SHARE', 5, 'SHARE ROW EXCLUSIVE',
6, 'EXCLUSIVE', '?'),
a.block
from gv$lock a
where a.id1 in ( select id1 from gv$lock where request <> 0 )
order by a.id1, a.request, a.sid
/
<< Memory Hogs > 512MB >>
select s.inst_id, s.sid, name, round(value/1024/1024) Mem_in_mb
from gv$sesstat s, v$statname n, gv$session p
where n.statistic# = s.statistic#
and s.inst_id = p.inst_id
and s.sid = p.sid
and name like '%pga memory%'
and value > 1*1024*1024*512
/
------------
List
------------
select s.inst_id, s.sid, name, round(value/1024/1024) Mem_in_mb
from gv$sesstat s, v$statname n, gv$session p
where n.statistic# = s.statistic#
and s.inst_id = p.inst_id
and s.sid = p.sid
and name like '%pga memory%'
and value > 1*1024*1024*512
/
<< Top 10 SQL >>
select * from
(select sql_text,
cpu_time/1000000 cpu_time,
elapsed_time/1000000 elapsed_time,
disk_reads,
buffer_gets,
rows_processed
from v$sqlarea
order by cpu_time desc, disk_reads desc
)
where rownum < 11
/
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current';
open_cursors_by_users.txt
--total cursors open, by username & machine
select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur,
s.username, s.machine
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current'
group by s.username, s.machine
order by 1 desc;
check tablespace in gb.txt
set linesize 200
set pagesize 200
SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)",
SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024),
SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;
blocking_sessions.txt
set lines 300;
set echo off;
set head off;
set pages 60;
set feed off;
spool &1
select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2;
spool off;
exit;
select s1.sid||','||s1.serial# Blocker ,sql1.sql_text Blocking_sql,
s2.sid||','||s2.serial# Waiting ,sql2.sql_text Waiting_sql
from v$lock l1, v$session s1, v$lock l2, v$session s2,
v$sqltext_with_newlines sql1,v$sqltext_with_newlines sql2
where s1.sid=l1.sid and s2.sid=l2.sid
and sql1.hash_value=s1.sql_hash_value
and sql1.address=s1.sql_address
and sql2.hash_value=s2.sql_hash_value
and sql2.address=s2.sql_address
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2;
concurrent running status.txt
set echo off
set linesize 130
set serveroutput on size 50000
set feed off
set veri off
DECLARE
running_count NUMBER := 0;
pending_count NUMBER := 0;
crm_pend_count NUMBER := 0;
--get the list of all conc managers and max worker and running workers
CURSOR conc_que IS
SELECT concurrent_queue_id,
concurrent_queue_name,
user_concurrent_queue_name,
max_processes,
running_processes
FROM apps.fnd_concurrent_queues_vl
WHERE enabled_flag='Y' and
concurrent_queue_name not like 'XDP%' and
concurrent_queue_name not like 'IEU%' and
concurrent_queue_name not in ('ARTAXMGR','PASMGR') ;
BEGIN
DBMS_OUTPUT.PUT_LINE('====================================================================================================');
DBMS_OUTPUT.PUT_LINE('QueueID'||' '||'Queue '||
'Concurrent Queue Name '||' '||'MAX '||' '||'RUN '||' '||
'Running '||' '||'Pending '||' '||'In CRM');
DBMS_OUTPUT.PUT_LINE('====================================================================================================');
FOR i IN conc_que
LOOP
--for each manager get the number of pending and running requests in each queue
SELECT /*+ RULE */ nvl(sum(decode(phase_code, 'R', 1, 0)), 0),
nvl(sum(decode(phase_code, 'P', 1, 0)), 0)
INTO running_count, pending_count
FROM fnd_concurrent_worker_requests
WHERE
requested_start_date <= sysdate
and concurrent_queue_id = i.concurrent_queue_id
AND hold_flag != 'Y';
--for each manager get the list of requests pending due to conflicts in each manager
SELECT /*+ RULE */ count(1)
INTO crm_pend_count
FROM apps.fnd_concurrent_worker_requests a
WHERE concurrent_queue_id = 4
AND hold_flag != 'Y'
AND requested_start_date <= sysdate
AND exists (
SELECT 'x'
FROM apps.fnd_concurrent_worker_requests b
WHERE a.request_id=b.request_id
and concurrent_queue_id = i.concurrent_queue_id
AND hold_flag != 'Y'
AND requested_start_date <= sysdate);
--print the output by joining the outputs of manager counts,
DBMS_OUTPUT.PUT_LINE(
rpad(i.concurrent_queue_id,8,'_')||
rpad(i.concurrent_queue_name,15, ' ')||
rpad(i.user_concurrent_queue_name,40,' ')||
rpad(i.max_processes,6,' ')||
rpad(i.running_processes,6,' ')||
rpad(running_count,10,' ')||
rpad(pending_count,10,' ')||
rpad(crm_pend_count,10,' '));
--DBMS_OUTPUT.PUT_LINE('----------------------------------------------------------------------------------------------------');
END LOOP;
DBMS_OUTPUT.PUT_LINE('====================================================================================================');
END;
/
set verify on
set echo on
forms_sessions_by_node.txt
set lines 300;
col RESPONSIBILITY_NAME for a30;
col USER_FORM_NAME for a30;
col machine for a15;
col user_name for a20;
select to_char(sysdate,'DD-MM-YY HH24:MI:SS') from dual;
select
count(user_name)
,Machine
from
apps.fnd_form_sessions_v a, gv$session b
where
a.sid=b.sid and
a.serial# = b.serial#
group by machine;
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current';
open_cursors_by_users.txt
--total cursors open, by username & machine
select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur,
s.username, s.machine
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current'
group by s.username, s.machine
order by 1 desc;
check tablespace in gb.txt
set linesize 200
set pagesize 200
SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)",
SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024),
SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;
blocking_sessions.txt
set lines 300;
set echo off;
set head off;
set pages 60;
set feed off;
spool &1
select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2;
spool off;
exit;
select s1.sid||','||s1.serial# Blocker ,sql1.sql_text Blocking_sql,
s2.sid||','||s2.serial# Waiting ,sql2.sql_text Waiting_sql
from v$lock l1, v$session s1, v$lock l2, v$session s2,
v$sqltext_with_newlines sql1,v$sqltext_with_newlines sql2
where s1.sid=l1.sid and s2.sid=l2.sid
and sql1.hash_value=s1.sql_hash_value
and sql1.address=s1.sql_address
and sql2.hash_value=s2.sql_hash_value
and sql2.address=s2.sql_address
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2;
concurrent running status.txt
set echo off
set linesize 130
set serveroutput on size 50000
set feed off
set veri off
DECLARE
running_count NUMBER := 0;
pending_count NUMBER := 0;
crm_pend_count NUMBER := 0;
--get the list of all conc managers and max worker and running workers
CURSOR conc_que IS
SELECT concurrent_queue_id,
concurrent_queue_name,
user_concurrent_queue_name,
max_processes,
running_processes
FROM apps.fnd_concurrent_queues_vl
WHERE enabled_flag='Y' and
concurrent_queue_name not like 'XDP%' and
concurrent_queue_name not like 'IEU%' and
concurrent_queue_name not in ('ARTAXMGR','PASMGR') ;
BEGIN
DBMS_OUTPUT.PUT_LINE('====================================================================================================');
DBMS_OUTPUT.PUT_LINE('QueueID'||' '||'Queue '||
'Concurrent Queue Name '||' '||'MAX '||' '||'RUN '||' '||
'Running '||' '||'Pending '||' '||'In CRM');
DBMS_OUTPUT.PUT_LINE('====================================================================================================');
FOR i IN conc_que
LOOP
--for each manager get the number of pending and running requests in each queue
SELECT /*+ RULE */ nvl(sum(decode(phase_code, 'R', 1, 0)), 0),
nvl(sum(decode(phase_code, 'P', 1, 0)), 0)
INTO running_count, pending_count
FROM fnd_concurrent_worker_requests
WHERE
requested_start_date <= sysdate
and concurrent_queue_id = i.concurrent_queue_id
AND hold_flag != 'Y';
--for each manager get the list of requests pending due to conflicts in each manager
SELECT /*+ RULE */ count(1)
INTO crm_pend_count
FROM apps.fnd_concurrent_worker_requests a
WHERE concurrent_queue_id = 4
AND hold_flag != 'Y'
AND requested_start_date <= sysdate
AND exists (
SELECT 'x'
FROM apps.fnd_concurrent_worker_requests b
WHERE a.request_id=b.request_id
and concurrent_queue_id = i.concurrent_queue_id
AND hold_flag != 'Y'
AND requested_start_date <= sysdate);
--print the output by joining the outputs of manager counts,
DBMS_OUTPUT.PUT_LINE(
rpad(i.concurrent_queue_id,8,'_')||
rpad(i.concurrent_queue_name,15, ' ')||
rpad(i.user_concurrent_queue_name,40,' ')||
rpad(i.max_processes,6,' ')||
rpad(i.running_processes,6,' ')||
rpad(running_count,10,' ')||
rpad(pending_count,10,' ')||
rpad(crm_pend_count,10,' '));
--DBMS_OUTPUT.PUT_LINE('----------------------------------------------------------------------------------------------------');
END LOOP;
DBMS_OUTPUT.PUT_LINE('====================================================================================================');
END;
/
set verify on
set echo on
forms_sessions_by_node.txt
set lines 300;
col RESPONSIBILITY_NAME for a30;
col USER_FORM_NAME for a30;
col machine for a15;
col user_name for a20;
select to_char(sysdate,'DD-MM-YY HH24:MI:SS') from dual;
select
count(user_name)
,Machine
from
apps.fnd_form_sessions_v a, gv$session b
where
a.sid=b.sid and
a.serial# = b.serial#
group by machine;
