The game never ends after the creation of anything. It starts when it comes to the phase of maintenance. Today we are going to have look on the query which is used to look “Progress status of index rebuild/creation or long operation”.
Let’s have look on the query.
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;
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;
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Telegram Channel: https://t.me/helporacle
Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/
Joel Perez’s LinkedIn: Joel Perez’s Profile
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp
