Sometimes you might think that your query doing nothing and want to see the progress of long running operation such as rebuild large index or RMAN backup.
You can use V$SESSION_LONGOPS to check the progress of log operation.
select sid,
target,
to_char(start_time,'hh24:mi') start_time,
elapsed_seconds/60 elapsed,
round(time_remaining/60,2) "min_remaining",
message
from v$session_longops where time_remaining > 0;
SELECT MESSAGE
FROM V$SESSION_LONGOPS
WHERE SID IN (SELECT SID FROM V$SESSION WHERE USERNAME='IPX' AND STATUS='ACTIVE')
ORDER BY START_TIME;
v$session_longops will show you the number of blocks scans out of the total block. Once these are done then the index will be constructed in the destination tablespace. Using the following command you can monitor the incremental size of the index that is being written in the disk.
select owner,segment_name,segment_type,tablespace_name,sum(bytes/1024/1024)
from dba_segments
where segment_type='TEMPORARY'
and TABLESPACE_NAME='<TARGET_TABLESPACE_NAME>'
group by owner,segment_name,segment_type,tablespace_name,bytes;