Sign Up to our social questions and Answers to ask questions, answer people’s questions, and connect with other people.
Login to our social questions & Answers to ask questions, answer people’s questions & connect with other people.
Lost your password? Please enter your email address. You will receive a link and will create a new password via email.
Please briefly explain why you feel this question should be reported.
Please briefly explain why you feel this answer should be reported.
Please briefly explain why you feel this user should be reported.
list of all Oracle APPS DBA questions and answers
Tablespace size
Find below the query to find the free space of a tablespace: SELECT DFQ.TABLESPACE_NAME "Tablespace Name" ,DFQ.TOTALSPACE "Total Size MB" , (DFQ.TOTALSPACE - DSQ.TOTALUSEDSPACE) "Free Space MB" ,ROUND (100 * ( (DFQ.TOTALSPACE - DSQ.TOTALUSEDSPACE) / DFQ.TOTALSPACE)) || '%' "Free Space %" FROM (SELECRead more
Find below the query to find the free space of a tablespace:
Also, you can refer to this article to find a handful of detail about the tablespace:
Check Tablespace Usage in Oracle
See lessWould you use flashback query to synchronize two schemas as of a certain point in time?
there is two options here : a) use serializable or read only isolation - the database would be consistent. b) use flashback query themselves - mandate that you set undo_retention to "X" and then they can just flashback and get a read consistent version of the database back "X" units in time. anythinRead more
there is two options here :
a) use serializable or read only isolation – the database would be consistent.
b) use flashback query themselves – mandate that you set undo_retention to “X” and then they can just flashback and get a read consistent version of the database back “X” units in time.
anything else does not really make sense. To synchronize the two scheme’s (using flashback) would mean a big bump and grind – AND – would prove that the data they need is flashback queryable – meaning, they didn’t need to copy it, it already exist.
See lessHow to see lock on table and query?
Hi Mina , Try this query : [code] SELECT c.owner, c.object_name, c.object_type, b.sid, b.serial#, b.status, b.osuser, b.machine FROM v$locked_object a, v$session b, dba_objects c WHERE b.sid = a.session_id AND a.object_id = c.object_id AND xidsqn != 0; [/code] Regards , Mahmoud Morsy
Hi Mina ,
Try this query :
[code]
SELECT c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine
FROM v$locked_object a, v$session b, dba_objects c
WHERE b.sid = a.session_id AND a.object_id = c.object_id AND xidsqn != 0;
[/code]
Regards ,
Mahmoud Morsy
See lessI need query to get Apps_user_name , Date , SID , SERIAL#, oracle process , application process , Module , responsibility name in EBS R12.
Hello , The below query help you : select usr.user_name apps_username ,ses.osuser ,ses.machine ,i.first_connect "First Connect Date" ,ses.sid ,ses.serial# ,ses.module ,v.spid "Oracle Server Process" ,ses.process "Application Server Process" ,rsp.responsibility_name "Responsibility Name" ,null "RespoRead more
Hello ,
The below query help you :
select usr.user_name apps_username
,ses.osuser
,ses.machine
,i.first_connect "First Connect Date"
,ses.sid
,ses.serial#
,ses.module
,v.spid "Oracle Server Process"
,ses.process "Application Server Process"
,rsp.responsibility_name "Responsibility Name"
,null "Responsibility Start Time"
,fuc.function_name "Function Name"
,i.function_type "Function Type"
,i.last_connect function_start_time
from icx_sessions i
,fnd_logins l
,fnd_appl_sessions a
,fnd_user usr
,fnd_responsibility_tl rsp
,fnd_form_functions fuc
,gv$process v
,gv$session ses
where i.disabled_flag = 'N'
and i.login_id = l.login_id
and l.end_time is null
and i.user_id = usr.user_id
and l.login_id = a.login_id
and a.audsid = ses.audsid
and l.pid = v.pid
and l.serial# = v.serial#
and i.responsibility_application_id = rsp.application_id(+)
and i.responsibility_id = rsp.responsibility_id(+)
and i.function_id = fuc.function_id(+)
and i.responsibility_id not in (select t1.responsibility_id
from fnd_login_responsibilities t1
where t1.login_id = l.login_id)
and rsp.language(+) = 'US'
union
select usr.user_name apps_username
,ses.osuser
,ses.machine
,l.start_time
,ses.sid
,ses.serial#
,ses.module
,v.spid
,ses.process
,rsp.responsibility_name
,r.start_time
,null
,null
,null function_start_time
from fnd_logins l
,fnd_login_responsibilities r
,fnd_user usr
,fnd_responsibility_tl rsp
,gv$process v
,gv$session ses
where l.end_time is null
and l.user_id = usr.user_id
and l.pid = v.pid
and l.serial# = v.serial#
and v.addr = ses.paddr
and l.login_id = r.login_id(+)
and r.end_time is null
and r.responsibility_id = rsp.responsibility_id(+)
and r.resp_appl_id = rsp.application_id(+)
and rsp.language(+) = 'US'
and r.audsid = ses.audsid
union
select usr.user_name
,ses.osuser
,ses.machine
,l.start_time
,ses.sid
,ses.serial#
,ses.module
,v.spid
,ses.process
,null
,null
,frm.user_form_name
,ff.type
,f.start_time function_start_time
from fnd_logins l
,fnd_login_resp_forms f
,fnd_user usr
,fnd_form_tl frm
,fnd_form_functions ff
,gv$process v
,gv$session ses
where l.end_time is null
and l.user_id = usr.user_id
and l.pid = v.pid
and l.serial# = v.serial#
and v.addr = ses.paddr
and l.login_id = f.login_id(+)
and f.end_time is null
and f.form_id = frm.form_id(+)
and f.form_appl_id = frm.application_id(+)
and frm.language(+) = 'US'
and f.audsid = ses.audsid
and ff.form_id = frm.form_id
order by function_start_time
Regards,
Mahmoud Morsy
See lessHow to check Workflow Mailer Status By query in EBS R12?
Hi , You can use the below query : SELECT component_name AS Component, component_status AS Status FROM apps.fnd_svc_components WHERE component_type = 'WF_MAILER' Regards, Mahmoud Morsy
Hi , You can use the below query :
Regards,
See lessMahmoud Morsy
How to query Gather schema statistic For Last week NonApps schemas in EBS R12?
Hello , Check the below Query: [code] SELECT * FROM apps.fnd_concurrent_programs_vl p, apps.fnd_concurrent_requests r WHERE r.concurrent_program_id = p.concurrent_program_id AND r.program_application_id = p.application_id AND p.user_concurrent_program_name IN ('Gather Statistics for NonApps Schema')Read more
Hello ,
Check the below Query:
[code]
SELECT *
FROM apps.fnd_concurrent_programs_vl p, apps.fnd_concurrent_requests r
WHERE r.concurrent_program_id = p.concurrent_program_id
AND r.program_application_id = p.application_id
AND p.user_concurrent_program_name IN (‘Gather Statistics for NonApps
Schema’)
AND ( r.phase_code = ‘C’
AND r.status_code = ‘C’
AND r.actual_start_date >= SYSDATE)
[/code]
Regards
Mahmoud Morsy
See lessHow to query Gather schema statistic For Last week For Apps schema in EBS R12?
Hi, Try the below Query: [code] SELECT * FROM apps.fnd_concurrent_programs_vl p, apps.fnd_concurrent_requests r WHERE r.concurrent_program_id = p.concurrent_program_id AND r.program_application_id = p.application_id AND p.user_concurrent_program_name IN ('OnDemand Gather Schema Statistics', 'GatherRead more
Hi,
Try the below Query:
[code]
SELECT *
FROM apps.fnd_concurrent_programs_vl p, apps.fnd_concurrent_requests r
WHERE r.concurrent_program_id = p.concurrent_program_id
AND r.program_application_id = p.application_id
AND p.user_concurrent_program_name IN (‘OnDemand Gather Schema Statistics’,
‘Gather Schema Statistics’,
‘Gather Schema Statistics (IT_ANALYZE)’)
AND ( r.phase_code = ‘C’
AND r.status_code = ‘C’
AND r.actual_start_date >= sysdate)
[/code]
Regards,
Mahmoud Morsy.
See lessError rman 06026 some targets not found aborting restore
Hi, Solution : try with the below command in RMAN Prompt catalog start with '/u03/oracle/hotbackup/DATE/' ; restore database ; Regards, Mahmoud Morsy.
Hi,
Solution :
try with the below command in RMAN Prompt
catalog start with ‘/u03/oracle/hotbackup/DATE/’ ;
restore database ;
Regards,
Mahmoud Morsy.
See lessHow to know Version Database $ Application?
Hi , The below query will help you. FOR DB: [code] SELECT * FROM PRODUCT_COMPONENT_VERSION; [/code] FOR Apps: [code] SELECT release_name FROM apps.fnd_product_groups; [/code] Regards, Mahmoud Morsy
Hi ,
The below query will help you.
FOR DB:
[code]
SELECT * FROM PRODUCT_COMPONENT_VERSION;
[/code]
FOR Apps:
[code]
SELECT release_name FROM apps.fnd_product_groups;
[/code]
Regards,
Mahmoud Morsy
See lessHow to get process ID By Concurrent request ID in EBS R12?
Hi Check this query. [code] SELECT request_id, TO_CHAR (ACTUAL_START_DATE, 'DDMMYYYY HH24:MI:SS'), TO_CHAR (ACTUAL_COMPLETION_DATE, 'DDMMYYYY HH24:MI:SS'), phase_code, status_code, os_process_id, oracle_process_id FROM apps.fnd_concurrent_requests WHERE request_id = &req_id [/code] Regards, MahmRead more
Hi
Check this query.
[code]
SELECT request_id,
TO_CHAR (ACTUAL_START_DATE, ‘DDMMYYYY
HH24:MI:SS’),
TO_CHAR (ACTUAL_COMPLETION_DATE, ‘DDMMYYYY
HH24:MI:SS’),
phase_code,
status_code,
os_process_id,
oracle_process_id
FROM apps.fnd_concurrent_requests
WHERE request_id = &req_id
[/code]
Regards,
Mahmoud Morsy.
See less