To calculate the Datafile Growth on a yearly basis:
select to_char(CREATION_TIME,’RRRR’) year,to_char(CREATION_TIME,’MM’) month,round(sum(bytes)/1024/1024/1024) gb
from v$datafile group by to_char(CREATION_TIME,’RRRR’),to_char(CREATION_TIME,’MM’) order by 1,2;
Through OEM :
Here are the steps to know the Database growth pattern for the last month/year using OEM
1) log in to OEM and Click on the Reports Tab
2) Navigate to Reports–>Storage–>Oracle Database Space Usage path and Click on the Oracle Database Space Usage link.
3) Select the Target database and here we are getting Oracle Database space usage for the last month.
4) Also we can get one year of Database growth by setting Set Time Period Button.
5) Also we can find Oracle Database Tablespace Monthly Space Usage by Navigating Reports–>Storage–>Oracle Database Space Usage path and clicking on the Oracle Database Tablespace Monthly Space Usage link.
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
1. 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';
2. 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';
3. 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';
4. 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';
5. To find the concurrent program name, phase code, and status code for a given request id?
6. 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'
/
7. 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';
8. Cancelling Concurrent requests:
update fnd_concurrent_requests
set status_code='D', phase_code='C'
where request_id=&req_id;
9. Kill sessions program-wise
select 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' immediate;' from v$session where
MODULE like '';
10 . 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;
11. 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';
12. Oracle Concurrent Request Error Script (requests which were errored 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;
13. 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;
14. Concurrent Program enables 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;
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
1. Check the status of Notifications: ====================================
col RECIPIENT_ROLE format a20
col FROM_USER format a20
col TO_USER format a20
set lines 170
select NOTIFICATION_ID,MESSAGE_TYPE,MESSAGE_NAME,RECIPIENT_ROLE,STATUS,FROM_USER,TO_USER from wf_notifications where NOTIFICATION_ID=‘&1′;
2.Check the status of Workflow components: =========================================
SELECT component_name as Component, component_status as Status FROM fnd_svc_components
3. To check whether notification is present: =========================================
select recipient_role,notification_id,status,mail_status from wf_notifications where recipient_role like ‘&user_name’;
The e-mail notification is sent only if all of the following is true.
Notification status is OPEN or CANCELED
Notification mail_status is MAIL or INVALID
4. Check Recipient role has a valid e-mail address and notification preference MAIL% =========================================
SELECT email_address, nvl(WF_PREF.get_pref(name, ‘MAILTYPE’), notification_preference)
FROM wf_roles
WHERE name = ‘&recipient_role’;
Recipient can receive email notification only if
notification preference is not set ‘QUERY’ / ‘DISABLED’ / ‘SUMMARY’ / ‘SUMHTML’ &
recipient has valid email address
5.WF_DEFERRED Queue volume: ===========================
col corrid format a60
set lines 130
set pages 100
select NVL(substr(wfe.corrid,1,50),’NULL – No Value’) corrid, decode(wfe.state,0,’0 = Ready’,1,’1 = Delayed’,2,’2 = Retained’,
3,’3 = Exception’,to_char(substr(wfe.state,1,12))) State,count(*) COUNT
from applsys.wf_deferred wfe group by wfe.corrid, wfe.state;
6.WF_NOTIFICATION_OUT Queue volume: ===================================
col corrid format a60
set lines 130
set pages 100
select NVL(substr(wfe.corrid,1,50),’NULL – No Value’) corrid, decode(wfe.state,0,’0 = Ready’,1,’1 = Delayed’,2,’2 = Retained’,
3,’3 = Exception’,to_char(substr(wfe.state,1,12))) State,count(*)COUNT
from applsys.wf_notification_out wfe group by wfe.corrid, wfe.state;