Have you ever started a query in your favourite Oracle SQL tool and it is just runnin’, and runnin’, and runnin’…? And, you are wondering if the query will let you eat lunch before it finishes…? Well, here is a way you might be able to find out how long your SQL query will take.
Example of v$session_longops usage
select * from ( select opname ,target ,round(((elapsed_seconds * (totalwork - sofar)) / sofar), 2) time_remaining ,sofar ,totalwork ,units ,elapsed_seconds ,message ,a.start_time ,b.sql_text from v$sql b ,v$session_longops a where b.sql_id (+) = a.sql_id and round(((elapsed_seconds * (totalwork - sofar)) / sofar), 2) > 0 ) order by time_remaining desc ;
Note that your ability to use the v$ views in Oracle is based on the rights you are set up with in the database. If you do not know, please get with your DBA. If you are doing this in your personal / private database, look on the internet to find out how to set the correct rights.
Hope this helps.
If you have a different way you like to do this, please let us all know so we all can grow in experience.