SQL Collection
SQL Collection
SQL Collection
select
p.user_concurrent_program_name Program_name,
to_char(r.actual_start_date,'DD-MON-YYYY HH24') Date_of_exec,
count(r.request_id) Executions,
avg((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60)
Avg_run_time,
min((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60)
Min_run_time,
max((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60)
Max_run_time
from
apps.fnd_concurrent_requests r,
apps.fnd_concurrent_processes c,
apps.fnd_concurrent_queues q,
apps.fnd_concurrent_programs_vl p
where
p.concurrent_program_id = r.concurrent_program_id
and p.application_id = r.program_application_id
and c.concurrent_process_id = r.controlling_manager
and q.concurrent_queue_id = c.concurrent_queue_id
-- and p.application_id = '&appl_id'
and r.status_code = 'C'
and r.phase_code = 'C'
and to_char(r.actual_start_date,'DD-MON-YYYY')> sysdate -1
group by p.user_concurrent_program_name,to_char(r.actual_start_date,'DD-MON-YYYY
HH24')
order by to_char(r.actual_start_date,'DD-MON-YYYY HH24') asc;
SELECT
i.instance_name instance_name
, l.session_id || ' / ' || s.serial# sid_serial
, s.status session_status
, l.oracle_username locking_oracle_user
, o.owner object_owner
, o.object_name object_name
, o.object_type object_type
, DECODE ( l.locked_mode
, 0,
'None' /* Mon Lock equivalent */
, 1,
'NoLock' /* N */
, 2,
'Row-Share (SS)' /* L */
, 3,
'Row-Exclusive (SX)' /* R */
, 4,
'Share-Table' /* S */
, 5,
'Share-Row-Exclusive (SSX)' /* C */
, 6,
'Exclusive' /* X */
, '[Nothing]'
) locked_mode
, s.client_identifier,s.program,action,module
FROM
dba_objects o
, gv$session s
, gv$locked_object l
, gv$instance i
WHERE
i.inst_id = l.inst_id
AND s.inst_id = l.inst_id
AND s.sid = l.session_id
AND o.object_id = l.object_id
ORDER BY
i.instance_name
, l.session_id;
Database Growth:
SELECT
TO_CHAR(creation_time, 'RRRR-MM-DD') "Month",
SUM(bytes/1024/1024) "growth in MB",TS#
FROM sys.v_$datafile where ts# in (1,22)
GROUP BY TO_CHAR(creation_time, 'RRRR-MM-DD'),ts#
order by to_char(creation_time, 'RRRR-MM-DD');
Wofkflow related:
select *
from APPLSYS.AQ$WF_NOTIFICATION_OUT
where CORR_ID like 'APPS:ALR%' and msg_state in ('READY','WAIT');
select * from wf_notification_out where CORRID like 'APPS:ALR%' and state<>2;
select decode(wno.state,
0, '0 = Pending in mailer queue',
1, '1 = Pending in mailer queue',
2, '2 = Sent by mailer on '||to_char(DEQ_TIME),
3, '3 = Exception', 4,'4 = Wait', to_char(state)) State,
to_char(DEQ_TIME),
wno.user_data.TEXT_VC
from wf_notification_out wno
where corrid like 'APPS:ALR%'
order by 2 desc;
and **/ upper(wno.user_data.TEXT_VC) like '%15762%';;
select decode(wno.state,
0, '0 = Pending in mailer queue',
1, '1 = Pending in mailer queue',
2, '2 = Sent by mailer on '||to_char(DEQ_TIME),
3, '3 = Exception', 4,'4 = Wait', to_char(state)) State,
to_char(DEQ_TIME),
wno.user_data.TEXT_VC
from wf_notification_out wno
where corrid like '%ALR%'
--and wno.user_data.TEXT_VC like '%Raghavan%'
order by 2 desc
;
SELECT COUNT(*),mail_status
FROM wf_notifications
WHERE-- mail_status ='MAIL'
--AND
TRUNC(begin_date)=TRUNC(sysdate)
--and trunc(begin_date) > sysdate -2
group by mail_status;
SELECT n.notification_id,
n.recipient_role,
ln.email_address,
n.status,
n.mail_status,
n.message_type,
n.message_name,
n.begin_date,
ln.notification_preference,
de.def_enq_time,
de.def_deq_time,
de.def_state,
ou.out_enq_time,
ou.out_deq_time,
ou.out_state
FROM applsys.wf_notifications n,
(SELECT d.enq_time def_enq_time,
d.deq_time def_deq_time,
TO_NUMBER(
(SELECT VALUE
FROM TABLE(d.user_data.parameter_list)
WHERE NAME = 'NOTIFICATION_ID'
)) d_notification_id,
msg_state def_state
FROM applsys.aq$wf_deferred d
WHERE d.corr_id = 'APPS:oracle.apps.wf.notification.send'
) de,
(SELECT o.deq_time out_deq_time,
o.enq_time out_enq_time,
TO_NUMBER(
(SELECT str_value
FROM TABLE(o.user_data.header.properties)
WHERE NAME = 'NOTIFICATION_ID'
)) o_notification_id,
msg_state out_state
FROM applsys.aq$wf_notification_out o
) ou,
(SELECT *
FROM wf_local_roles
WHERE name IN
(SELECT recipient_role FROM apps.wf_notifications
)
) ln
WHERE n.notification_id =
&NOTIFICATION_ID
/** IN
(SELECT notification_id
FROM apps.wf_notifications
WHERE mail_status ='MAIL'
AND status ='OPEN'
AND TRUNC(begin_date) between '01-NOV-13' and '07-NOV-13'
)**/
AND ln.name =n.recipient_role
AND n.notification_id = de.d_notification_id(+)
AND n.notification_id = ou.o_notification_id(+)
ORDER BY n.begin_date;
SELECT D.PATCH_NAME,
B.APPLICATIONS_SYSTEM_NAME,
B.NAME,
c.patch_abstract,
C.DRIVER_FILE_NAME,
-- A.PATCH_DRIVER_ID,
-- A.PATCH_RUN_ID,
-- A.SESSION_ID,
a.patch_top,
to_char(a.start_date , 'DD-Mon-yyyy HH24:MI:SS') startdate ,
to_char(A.END_DATE, 'DD-Mon-yyyy HH24:MI:SS') enddate,
FLOOR(((a.end_date-a.start_date)*24*60*60)/3600)||':'||
FLOOR((((a.end_date-a.start_date)*24*60*60) -
FLOOR(((a.end_date-a.start_date)*24*60*60)/3600)*3600)/60)||':'||
round((((a.end_date-a.start_date)*24*60*60) -
FLOOR(((a.end_date-a.start_date)*24*60*60)/3600)*3600 -
(FLOOR((((a.end_date-a.start_date)*24*60*60) -
FLOOR(((a.end_date-a.start_date)*24*60*60)/3600)*3600)/60)*60) ))
"HOURS:MINUTES:SECONDS"
--A.SUCCESS_FLAG,
-- A.FAILURE_COMMENTS
FROM AD_PATCH_RUNS A,
AD_APPL_TOPS B,
AD_PATCH_DRIVERS C,
AD_APPLIED_PATCHES D
WHERE A.APPL_TOP_ID = B.APPL_TOP_ID
AND A.PATCH_DRIVER_ID = C.PATCH_DRIVER_ID
AND C.APPLIED_PATCH_ID = D.APPLIED_PATCH_ID
--and upper(b.name) in (select node_name from fnd_nodes)
--and b.name like '%tst003kkkmaaind%'
and trunc(a.start_date) > (select trunc(resetlogs_time) from v$database)
AND A.PATCH_DRIVER_ID IN
(SELECT PATCH_DRIVER_ID
FROM AD_PATCH_DRIVERS
WHERE APPLIED_PATCH_ID IN
(SELECT APPLIED_PATCH_ID
FROM AD_APPLIED_PATCHES
WHERE PATCH_NAME IN (16000686,17395845,14069503,8919491,9239089,17203944)
)
) ORDER BY 6;
SELECT request_id,
parent_request_id,
phase_code,
status_code,
---- responsibility_application_id,
-- responsibility_id,
request_date,
actual_start_date,
actual_completion_date,
program,
requestor,
--user_concurrent_program_name,16000686
round (((actual_completion_date-actual_start_date)*24)) diff,
completion_text,argument_text
FROM fnd_conc_req_summary_v
WHERE user_concurrent_program_name LIKE 'OP3: Update Project(s)'
and round (((actual_completion_date-actual_start_date)*24)) >5
--and trunc(request_date) = trunc(sysdate)
ORDER BY 8 desc ;
select
r.request_id ,
--R.PHASE_CODE ,
--R.STATUS_CODE ,
--R.REQUEST_DATE ,
--R.REQUESTED_BY ,
--R.REQUESTED_START_DATE ,
-- R.PARENT_REQUEST_ID ,
--R.CONTROLLING_MANAGER ,
r.actual_start_date ,
--R.ACTUAL_COMPLETION_DATE ,
(nvl(r.actual_completion_date,sysdate)-r.actual_start_date)*1440 "Duration in
Min",
avg((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60)
avg_run_time,
min((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60)
min_run_time,
max((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60)
max_run_time,
pt.user_concurrent_program_name
program ,
u.user_name requestor
from fnd_concurrent_programs_tl pt,
fnd_concurrent_programs pb,
fnd_user u,
fnd_concurrent_requests r
where pb.application_id = r.program_application_id
and pb.concurrent_program_id = r.concurrent_program_id
and pb.application_id = pt.application_id
and pb.concurrent_program_id = pt.concurrent_program_id
and u.user_id = r.requested_by
and r.phase_code='R'
and r.status_code='R'
and ((sysdate - r.actual_start_date) * 24 *60) > 60
--AND (SYSDATE - R.actual_start_date) * 24 > 24
--and to_char(r.actual_start_date,'DD-MON-YYYY')> sysdate -10
group by
r.request_id,/**R.PARENT_REQUEST_ID**/r.actual_start_date,r.actual_completion_date,
pt.user_concurrent_program_name,u.user_name;
SELECT oracle_username
|| ' ('
|| s.osuser
|| ')' username ,
s.sid
|| ',
'
|| s.serial# sess_id ,
owner
|| '.'
|| object_name object ,
object_type ,
DECODE( l.block , 0, 'Not Blocking' , 1, 'Blocking' , 2, 'Global') status ,
DECODE(v.locked_mode , 0, 'None' , 1, 'Null' , 2, 'Row-S (SS)' , 3, 'Row-X
(SX)' , 4, 'Share' , 5, 'S/Row-X (SSX)' , 6, 'Exclusive', TO_CHAR(lmode)) mode_held
FROM gv$locked_object v ,
dba_objects d ,
gv$lock l ,
gv$session s
WHERE v.object_id = d.object_id
AND v.object_id = l.id1
AND v.session_id = s.sid
and l.request>0
ORDER BY oracle_username ,
session_id;
SELECT sid
, serial#
, username
, osuser
, machine
FROM gv$sessionSELECT request_id,
phase_code,
status_code,
request_date,
actual_start_date,
actual_completion_date,
(NVL(actual_completion_date,SYSDATE)-actual_start_date)*1440 "Duration in Min",
PROGRAM,
requestor
FROM fnd_conc_req_summary_v
WHERE status_code = 'R'
AND phase_code = 'R'
AND ((SYSDATE - actual_start_date) * 24 *60) > 60;
dba_blockers;
SELECT request_id,
phase_code,
status_code,
request_date,
actual_start_date,
actual_completion_date,
(NVL(actual_completion_date,SYSDATE)-actual_start_date)*1440 "Duration in Min",
PROGRAM,
requestor
FROM fnd_conc_req_summary_v
WHERE status_code = 'R'
AND phase_code = 'R'
AND ((SYSDATE - actual_start_date) * 24 *60) > 60;
select
p.user_concurrent_program_name Program_name,
to_char(r.actual_start_date,'DD-MON-YYYY HH24') Date_of_exec,
count(r.request_id) Executions,
avg((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60)
Avg_run_time,
min((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60)
Min_run_time,
max((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60)
Max_run_time
from
apps.fnd_concurrent_requests r,
apps.fnd_concurrent_processes c,
apps.fnd_concurrent_queues q,
apps.fnd_concurrent_programs_vl p
where
p.concurrent_program_id = r.concurrent_program_id
and p.application_id = r.program_application_id
and c.concurrent_process_id = r.controlling_manager
and q.concurrent_queue_id = c.concurrent_queue_id
--and p.application_id = '&appl_id'
and r.status_code = 'R'
and r.phase_code = 'R'
and to_char(r.actual_start_date,'DD-MON-YYYY') > sysdate -1
group by p.user_concurrent_program_name,to_char(r.actual_start_date,'DD-MON-YYYY
HH24')
order by to_char(r.actual_start_date,'DD-MON-YYYY HH24') asc;
ptl.user_concurrent_program_name,qtl.user_concurrent_queue_name,t.request_id,t.phas
e_code,t.status_code
from fnd_concurrent_requests t,
fnd_concurrent_processes k,
fnd_concurrent_queues_tl qtl,
fnd_concurrent_programs_tl ptl
where k.concurrent_process_id = t.controlling_manager
and qtl.concurrent_queue_id = k.concurrent_queue_id
and ptl.concurrent_program_id=t.concurrent_program_id
and qtl.user_concurrent_queue_name like 'PFC CRT Account Manager'
and qtl.language='US';
SELECT SUM(bytes)/1024/1024/1024,
segment_name
FROM dba_segments
WHERE segment_name IN ('EC_SHJ_DM_HRT18_PROD_ALL','SYS_LOB0000700593C00020$
$','SYS_IL0000700593C00020$$')
GROUP BY segment_name
ORDER BY 1;
SELECT SUM(bytes)/1024/1024/1024,
segment_name,
owner,
segment_type,
tablespace_name
FROM dba_segments
WHERE segment_name in ('EC_SHJ_DM_HRT18_PROD_ALL','SYS_LOB0000700593C00020$
$','SYS_IL0000700593C00020$$')
GROUP BY segment_name,
owner,
segment_type,
tablespace_name
ORDER BY 1 DESC;
select * from v$database;
xxcnv.SYS_LOB0000700593C00020$$;
SELECT *
FROM dba_lobs
WHERE segment_name = (select object_name from dba_objects where object_name like
'SYS_LOB0000700593C00020$$');
=========================================================
RMAN related:
This script by Osama Mustafa will monitor the progress of a running RMAN job:
select
to_char(start_time,'DD-MON-YY HH24:MI') "BACKUP STARTED",
sofar,
totalwork,
elapsed_seconds/60 "ELAPSE (Min)",
round(sofar/totalwork*100,2) "Complete%"
from
sys.v_$session_longops
where compnam = 'dbms_backup_restore';
=================================================
===========================================
Undosizing related :
SELECT tablespace_name,
file_name,
bytes/(1024*1024) MB,
autoextensible,
maxbytes/(1024*1024) MaxMB
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME IN
(SELECT UPPER(VALUE) FROM GV$PARAMETER WHERE NAME LIKE '%undo_tablespace%'
)
ORDER BY tablespace_name;
SELECT y.tablespace_name,
y.totmb "Total size MB",
ROUND(x.usedmb*100/y.totmb,2) "% Used"
FROM
(SELECT a.tablespace_name,
NVL(SUM(bytes),0)/(1024*1024) usedmb
FROM dba_undo_extents a
WHERE tablespace_name IN
(SELECT upper(value) FROM gv$parameter WHERE name='undo_tablespace'
)
AND status IN ('ACTIVE','UNEXPIRED')
GROUP BY a.tablespace_name
) x,
(SELECT b.tablespace_name,
SUM(bytes)/(1024*1024) totmb
FROM dba_data_files b
WHERE tablespace_name IN
(SELECT upper(value) FROM gv$parameter WHERE name='undo_tablespace'
)
GROUP BY b.tablespace_name
) y
WHERE y.tablespace_name=x.tablespace_name
ORDER BY y.tablespace_name;
SELECT sql_text
FROM dba_hist_sqltext
WHERE sql_id IN
(SELECT maxquerysqlid
FROM
(SELECT begin_time,
maxquerylen,
maxquerysqlid,
nospaceerrcnt,
ssolderrcnt,
tuned_undoretention
FROM dba_hist_undostat
WHERE TRUNC(BEGIN_TIME)>SYSDATE-10
ORDER BY 2 DESC
)
WHERE maxquerylen>3600
);
SELECT BEGIN_TIME,
MAXQUERYLEN,
MAXQUERYSQLID,
NOSPACEERRCNT,
SSOLDERRCNT,
TUNED_UNDORETENTION
FROM dba_hist_undostat
WHERE TRUNC(BEGIN_TIME)>SYSDATE-10
order by 2 desc;
SELECT tablespace_name,
status,
SUM(bytes)/1024/1024/1024
FROM dba_undo_extents
GROUP BY tablespace_name,
status
ORDER BY tablespace_name,
status;
=============================================================================