Home/Oracle E-Business Suite/Oracle Applications DBA
  1. Asked: June 14, 2022In:

    Tablespace size

    Hassan AbdElrahman
    Hassan AbdElrahman Master Oracle ACE Pro Alum ♠ | Oracle Senior ERP Technical Consultant

    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:

    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 (SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / 1048576) TOTALSPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) DFQ
,(SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / (1024 * 1024)) TOTALUSEDSPACE
FROM DBA_SEGMENTS
GROUP BY TABLESPACE_NAME) DSQ
WHERE DFQ.TABLESPACE_NAME = DSQ.TABLESPACE_NAME(+);

    Also, you can refer to this article to find a handful of detail about the tablespace:

    Check Tablespace Usage in Oracle

    See less
  2. Asked: July 14, 2017In:

    Would you use flashback query to synchronize two schemas as of a certain point in time?

    Googler
    Googler Explorer

    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 less
  3. Asked: July 31, 2017In:

    How to see lock on table and query?

    Mmorsy
    Mmorsy Junior mahmoudmorsymm1985@gmail.com

    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 less
  4. Asked: July 30, 2017In:

    I need query to get Apps_user_name , Date , SID , SERIAL#, oracle process , application process , Module , responsibility name in EBS R12.

    Mmorsy
    Mmorsy Junior mahmoudmorsymm1985@gmail.com

    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 less
  5. Asked: July 30, 2017In:

    How to check Workflow Mailer Status By query in EBS R12?

    Mmorsy
    Mmorsy Junior mahmoudmorsymm1985@gmail.com
    This answer was edited.

    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 :

    SELECT component_name AS Component, component_status AS Status 
FROM apps.fnd_svc_components 
WHERE component_type = 'WF_MAILER'

    Regards,
    Mahmoud Morsy

    See less
  6. Asked: July 30, 2017In:

    How to query Gather schema statistic For Last week NonApps schemas in EBS R12?

    Mmorsy
    Mmorsy Junior mahmoudmorsymm1985@gmail.com

    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 less
  7. Asked: July 30, 2017In:

    How to query Gather schema statistic For Last week For Apps schema in EBS R12?

    Mmorsy
    Mmorsy Junior mahmoudmorsymm1985@gmail.com

    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 less
  8. Asked: July 19, 2017In:

    Error rman 06026 some targets not found aborting restore

    Mmorsy
    Mmorsy Junior mahmoudmorsymm1985@gmail.com

    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 less
  9. Asked: July 19, 2017In:

    How to know Version Database $ Application?

    Mmorsy
    Mmorsy Junior mahmoudmorsymm1985@gmail.com

    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 less
  10. Asked: July 19, 2017In:

    How to get process ID By Concurrent request ID in EBS R12?

    Mmorsy
    Mmorsy Junior mahmoudmorsymm1985@gmail.com

    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