Oracle Tips:

The Oracle Digital dictionary contains a little-known v$session_longops view. The V$session_longops view enables Oracle experts to reduce the elapsed time of a long-running DDL and DML statement.

For example, in a data warehouse environment, even with parallel index creation techniques, it takes many hours to build a large number of G-byte indexes. Here you can query the V$session_longops view to quickly find out how much of a particular DDL statement has been completed. In fact, the V$session_longops view can also be used for any long-running operation, including an update operation that is running for a long time.

The following script displays a status message describing the time that a long-running DDL operation has been used. Note that you must obtain the SID from the v$session and insert it into the following SQL statement:

Select
Sid
Message
From
V$session_longops
where
SID = 13
ORDER BY
Start_time;
Here is an example of an output that shows the running process of the CREATE INDEX statement that is running for a long time.

SID message
--- -------------------------------------------------------------------
One Table scan:cust. pk_idx:732 out of the 243260 Blocks done