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> 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