Please share server hardware details.
Please correct me if Im wrong
CentOS is not certified platform for application/database node
sharing is Caring
What is the expected time to complete in your instance? This is the first time you are running "Gather Schema Statistics"?
Please identify the sid and check which SQL statement is currently running.
Expected time is 02:30 hrs.
We have already ran and scheduled. But suddenly it is running since 3 days.
OS: CentOS 5.6(64 bit)
We have successfully run this program previously.
Still "Gather Schema Statistics" is running. Please help
have you checked the session details and the current SQL statement which is being executed?
No, how can I check those?
If you have any query for that?
1 person found this helpful
Please check below Queries, I assume that your instance is NON-RAC:
--Query to get SID from RequestID
SELECT a.request_id, c.spid,s.sid,s.serial#
FROM apps.fnd_concurrent_requests a, v$process c, v$session s
WHERE a.request_id in ('&req_id')
AND s.paddr = c.addr
AND a.oracle_process_id = c.spid
AND a.phase_code = UPPER ('R');
--Query to check status of SID
col status for a10;
col osuser for a15;
col program for a18;
col username for a15
col machine for a20
select sid,serial#,status,machine,osuser,program,username from v$session where sid=&sid
--Query to get sql_text for given sid
from v$sqltext, v$session
and hash_value = sql_hash_value
order by piece
Yes, my instance is NON-RAC
sql_text for given sid is,
BEGIN FND_STATS.GATHER_SCHEMA_STATS(:errbuf,:rc,:A0,:A1,:A2,:A3,:A4,:A5,:A6,:A7,:A8); END;
run below, it will tell you exactly what it is doing .
set verify off
col opname format a40 trunc
col pctdone format 999
col mintogo format 999,999.90
accept trgtsid number default 0 prompt 'Limit to which SID : '
round(o.sofar/o.totalwork * 100,0) pctdone, o.message
from v$session_longops o, v$session s
where o.sid = s.sid
and sofar < totalwork
and (o.sid = &trgtsid or &trgtsid = 0)
It is asking for SID value. Which value I will give?
You need to pass the sid which you have got earlier from --Query to get SID from RequestID