How To Monitor Gather Schema stats status and progress and also Tablespace Used by Users
to check schema gather stats :-
SELECT inst_id,sid,serial#,opname,target_desc,round(sofar/totalwork*100,2) "%_completed",units,start_time,last_update_time,time_remaing "time_remaining(seconds)",elapsed_seconds,context,message,username,sql_address,sql_hash_value,
sql_id,sql_exec_start,sql_plan_operation,sql_plan_options FROM gv$session_longopsWHERE opname LIKE '%Gather%' AND time_remaing "time_remaining(seconds)" != 0
ORDER BY SID;
sql_id,sql_exec_start,sql_plan_operation,sql_plan_options FROM gv$session_longopsWHERE opname LIKE '%Gather%' AND time_remaing "time_remaining(seconds)" != 0
ORDER BY SID;
SELECT inst_id,sid,serial#,opname,target_desc,round(sofar/totalwork*100,2) "%_completed",username,units,
to_char(start_time,'DD-MM-YY HH:MM:SS') start_time,last_update_time,time_remaining time_remainig_seconds,elapsed_seconds,
message,sql_address,sql_hash_value,sql_id,sql_exec_start,sql_plan_operation,sql_plan_options
FROM gv$session_longops
WHERE opname LIKE '%Gather%' AND time_remaining != 0 and username!='SYS'
ORDER BY SID;
Best query to monitor whose using most of the tablespace in Oracle:
SELECT b.TABLESPACE
, b.segfile#
, b.segblk#
, ROUND ( ( ( b.blocks * p.VALUE )) / power(1024,3 ), 2 ) size_GB
, a.inst_ID
, a.SID
, a.serial#
, a.username
, a.osuser
, a.program
, a.status
FROM gv$session a
, gv$sort_usage b
, gv$process c
, gv$parameter p
WHERE p.NAME = 'db_block_size'
AND a.saddr = b.session_addr
AND a.paddr = c.addr
-- AND b.TABLESPACE='TEMP2'
order by size_GB desc;
and to kill sessions :
SELECT b.TABLESPACE, a.username , a.osuser , a.program , a.status ,
'ALTER SYSTEM KILL SESSION '''||a.SID||','||a.SERIAL#||',@'||a.inst_ID||''' IMMEDIATE;'
FROM gv$session a
, gv$sort_usage b
, gv$process c
, gv$parameter p
WHERE p.NAME = 'db_block_size'
AND a.saddr = b.session_addr
AND a.paddr = c.addr
-- AND b.TABLESPACE='TEMP'
ORDER BY a.inst_ID , b.TABLESPACE
, b.segfile#
, b.segblk#
, b.blocks;
