Oracle: Script to track RMAN recovery and other long operations progress

Possibly can be used for other purposes as well. This isn't my query. I was from another cool DBA I used to work with long time ago. Can be used for import/export.

set pages 9999 lines 200
col sess_id for a15
col message for a80
col pct_comp for 999999999
select  inst_id|| ':' || sid || ',' || serial# sess_id, sofar, totalwork, 100-round(( 1-(sofar/totalwork))* 100) pct_comp, message from gv$session_longops where sofar < totalwork;

Example.

SQL> set pages 9999 lines 200
SQL> col sess_id for a15
col message for a80
SQL> SQL> col pct_comp for 999999999
SQL> select  inst_id|| ':' || sid || ',' || serial# sess_id, sofar, totalwork, 100-round(( 1-(sofar/totalwork))* 100) pct_comp, message from gv$session_longops where sofar < totalwork;

SESS_ID              SOFAR  TOTALWORK   PCT_COMP MESSAGE
--------------- ---------- ---------- ---------- --------------------------------------------------------------------------------
1:9,34888                0      19496          0 SYS_IMPORT_SCHEMA_01: IMPORT : 0 out of 19496 MB done




Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

imported "VCADM"."AUDIT_EV"           16.99 GB 5797832 rows



SQL> /

SESS_ID              SOFAR  TOTALWORK   PCT_COMP MESSAGE
--------------- ---------- ---------- ---------- --------------------------------------------------------------------------------
1:9,34888            17399      19496         89 SYS_IMPORT_SCHEMA_01: IMPORT : 17399 out of 19496 MB done


. . imported "VCADM"."VDS_CONTEXT"                           0 KB       0 rows
. . imported "VCADM"."VM_DISK"                               0 KB       0 rows
. . imported "VCADM"."VM_RESOURCE"                           0 KB       0 rows
....
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX



SESS_ID              SOFAR  TOTALWORK   PCT_COMP MESSAGE
--------------- ---------- ---------- ---------- --------------------------------------------------------------------------------
1:9,34888            19496      19497        100 SYS_IMPORT_SCHEMA_01: IMPORT : 19496 out of 19497 MB done



At the processing Index stage, it does something interesting ...Table Scan


SQL> /

SESS_ID              SOFAR  TOTALWORK   PCT_COMP MESSAGE
--------------- ---------- ---------- ---------- --------------------------------------------------------------------------------
1:34,35127          666376    2905118         23 Table Scan:  VCADM.AUDIT_EVENT: 666376 out of 2905118 Blocks done
1:9,34888            19496      19497        100 SYS_IMPORT_SCHEMA_01: IMPORT : 19496 out of 19497 MB done

SQL> /

SESS_ID              SOFAR  TOTALWORK   PCT_COMP MESSAGE
--------------- ---------- ---------- ---------- --------------------------------------------------------------------------------
1:34,35127         1013815    2905118         35 Table Scan:  VCADM.AUDIT_EVENT: 1013815 out of 2905118 Blocks done
1:9,34888            19496      19497        100 SYS_IMPORT_SCHEMA_01: IMPORT : 19496 out of 19497 MB done

SQL> /

SESS_ID              SOFAR  TOTALWORK   PCT_COMP MESSAGE
--------------- ---------- ---------- ---------- --------------------------------------------------------------------------------
1:34,35127         1198719    2905118         41 Table Scan:  VCADM.AUDIT_EVENT: 1198719 out of 2905118 Blocks done
1:9,34888            19496      19497        100 SYS_IMPORT_SCHEMA_01: IMPORT : 19496 out of 19497 MB done

SQL>


SESS_ID              SOFAR  TOTALWORK   PCT_COMP MESSAGE
--------------- ---------- ---------- ---------- --------------------------------------------------------------------------------
1:34,35127         2440006    2905118         84 Table Scan:  VCADM.AUDIT_EVENT: 2440006 out of 2905118 Blocks done
1:9,34888            19496      19497        100 SYS_IMPORT_SCHEMA_01: IMPORT : 19496 out of 19497 MB done

SQL> /

SESS_ID              SOFAR  TOTALWORK   PCT_COMP MESSAGE
--------------- ---------- ---------- ---------- --------------------------------------------------------------------------------
1:34,35127         2829509    2905118         97 Table Scan:  VCADM.AUDIT_EVENT: 2829509 out of 2905118 Blocks done
1:9,34888            19496      19497        100 SYS_IMPORT_SCHEMA_01: IMPORT : 19496 out of 19497 MB done

SQL> /

SESS_ID              SOFAR  TOTALWORK   PCT_COMP MESSAGE
--------------- ---------- ---------- ---------- --------------------------------------------------------------------------------
1:9,34888            19496      19497        100 SYS_IMPORT_SCHEMA_01: IMPORT : 19496 out of 19497 MB done


It does that for 3 times then it does the sort.




SESS_ID              SOFAR  TOTALWORK   PCT_COMP MESSAGE

--------------- ---------- ---------- ---------- --------------------------------------------------------------------------------

1:34,35127           20539      89827         23 Sort Output:  : 20539 out of 89827 Blocks done
1:9,34888            19496      19497        100 SYS_IMPORT_SCHEMA_01: IMPORT : 19496 out of 19497 MB done

SQL>

SQL> /

SESS_ID              SOFAR  TOTALWORK   PCT_COMP MESSAGE
--------------- ---------- ---------- ---------- --------------------------------------------------------------------------------
1:34,35127          146569     266598         55 Table Scan:  VCADM.LICENSING_VM_DATA: 146569 out of 266598 Blocks done
1:9,34888            19496      19497        100 SYS_IMPORT_SCHEMA_01: IMPORT : 19496 out of 19497 MB done


When it is done ...


SQL> /

SESS_ID              SOFAR  TOTALWORK   PCT_COMP MESSAGE
--------------- ---------- ---------- ---------- --------------------------------------------------------------------------------
1:9,34888            19496      19497        100 SYS_IMPORT_SCHEMA_01: IMPORT : 19496 out of 19497 MB done

SQL> /

no rows selected

SQL> /


No comments:

Post a Comment