To monitor long running operations in oracle the following two conditions must met.
1) Set the initialization parameter TIMED_STATISTICS or SQL_TRACE parameters to true.
2) Gather statistics for your objects with the ANALYZE statement or the DBMS_STATS package.
After you have met above two conditions you can easily monitor your long running operations by querying V$SESSION_LONGOPS view.
SQL> desc V$SESSION_LONGOPS Name Null? Type ----------------------------------------- -------- ---------------------------- SID NUMBER SERIAL# NUMBER OPNAME VARCHAR2(64) TARGET VARCHAR2(64) TARGET_DESC VARCHAR2(32) SOFAR NUMBER TOTALWORK NUMBER UNITS VARCHAR2(32) START_TIME DATE LAST_UPDATE_TIME DATE TIMESTAMP DATE TIME_REMAINING NUMBER ELAPSED_SECONDS NUMBER CONTEXT NUMBER MESSAGE VARCHAR2(512) USERNAME VARCHAR2(30) SQL_ADDRESS RAW(4) SQL_HASH_VALUE NUMBER SQL_ID VARCHAR2(13) SQL_PLAN_HASH_VALUE NUMBER SQL_EXEC_START DATE SQL_EXEC_ID NUMBER SQL_PLAN_LINE_ID NUMBER SQL_PLAN_OPERATION VARCHAR2(30) SQL_PLAN_OPTIONS VARCHAR2(30) QCSID NUMBERFrom above view,
- SOFAR is units of work done so far.
- TIME_REMAINING is estimate (in seconds) of time remaining for the operation to complete
In order to monitor how much percentage of an operation is completed query as,
SQL> SELECT sid, to_char(start_time,'hh24:mi:ss') stime, message,( sofar/totalwork)* 100 percent FROM v$session_longops;
If you have long running operations in your database then above query will return rows.
You can query long running operations based on they have started like,
SQL> select opname, target, sofar, totalwork,
units, elapsed_seconds, message
from v$session_longops order by start_time desc;
Related Documents
http://arjudba.blogspot.com/2009/12/oracle-object-type-exercises-varray.html
http://arjudba.blogspot.com/2009/12/practice-oracle-joins-examples.html
http://arjudba.blogspot.com/2009/12/oracle-security-practices.html
http://arjudba.blogspot.com/2009/12/exercises-with-oracle-create-table-add.html
http://arjudba.blogspot.com/2009/12/oracle-database-creation-exercises.html
http://arjudba.blogspot.com/2009/12/basic-oracle-sql-exercise.html
http://arjudba.blogspot.com/2009/08/format-model-modifiers-fx-and-fm.html
http://arjudba.blogspot.com/2009/08/number-format-models-in-oracle.html
http://arjudba.blogspot.com/2009/08/format-models-in-oracle.html
http://arjudba.blogspot.com/2009/07/sql-decode.html
http://arjudba.blogspot.com/2009/07/how-to-know-row-of-table-belong-to.html
http://arjudba.blogspot.com/2009/06/how-to-know-which-objects-are-being.html
http://arjudba.blogspot.com/2009/06/ddl-with-wait-option-in-11g.html
http://arjudba.blogspot.com/2009/06/ora-00939-too-many-arguments-when-case.html
http://arjudba.blogspot.com/2009/03/oracle-datatype-internal-code.html
http://arjudba.blogspot.com/2009/03/how-to-know-list-of-constraints-and.html
http://arjudba.blogspot.com/2009/02/how-to-know-dependent-objectswhich.html
http://arjudba.blogspot.com/2009/02/how-to-search-stringkey-value-from.html
http://arjudba.blogspot.com/2009/02/how-to-know-when-tableobjects-ddlcode.html
http://arjudba.blogspot.com/2009/02/ora-00920-invalid-relational-operator.html
http://arjudba.blogspot.com/2009/01/adding-default-value-to-column-on-table.html
http://arjudba.blogspot.com/2009/01/ora-12838-cannot-readmodify-object.html
http://arjudba.blogspot.com/2009/01/ora-01779-cannot-modify-column-which.html
http://arjudba.blogspot.com/2009/01/updating-table-based-on-another-table.html
http://arjudba.blogspot.com/2009/01/ora-00054-resource-busy-and-acquire.html
http://arjudba.blogspot.com/2008/12/troubleshoot-ora-02292-ora-02449-and.html
