監控EXPDP/IMPDP進度

--獲取JOB_NAME
select * from DBA_DATAPUMP_JOBS;
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
-------------------- ------------------------------ -------------------- -------------------- ------------------------------ ---------- ----------------- -----------------
SYSTEM SYS_IMPORT_SCHEMA_01 IMPORT SCHEMA EXECUTING 1 0 2

1 row selected.

--使用attach參數
impdp system/*** attach=SYS_IMPORT_SCHEMA_01
expdp system/*** attach=SYS_IMPORT_SCHEMA_01

[oracle@test ~]$ impdp system/qwer1234 attach=SYS_IMPORT_SCHEMA_01
Import: Release 11.2.0.4.0 - Production on Thu Jan 3 15:19:42 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Job: SYS_IMPORT_SCHEMA_01
Owner: SYSTEM
Operation: IMPORT
Creator Privs: TRUE
GUID: 7E87236A9B0B4EFAE05333060B0AA077
Start Time: Thursday, 03 January, 2019 12:24:29
Mode: SCHEMA
Instance: WOFU
Max Parallelism: 1
EXPORT Job Parameters:
CLIENT_COMMAND system/******** directory=dump_dir schemas=username.... dumpfile=expdp....dmp logfile=expdp....log
IMPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND system/******** directory=dump_dir dumpfile=username....dmp logfile=username....log schemas=username... table_exists_action=replace
TABLE_EXISTS_ACTION REPLACE
State: EXECUTING
Bytes Processed: 130,059,459,432
Percent Done: 82
Current Parallelism: 1
Job Error Count: 0
Dump File: /backup/expdp....dmp

Worker 1 Status:
Process Name: DW00
State: EXECUTING
Object Schema: username...
Object Name: tablename...
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects: 20
Completed Rows: 4,178,798
Completed Bytes: 906,721,488
Percent Done: 34
Worker Parallelism: 1

按 Ctrl+D 退出

---
查看impdp進度
col OWNER_NAME for a20
col OPERATION for a20
col JOB_MODE for a20
set line 500
select * from dba_datapump_jobs;

查看正在運行的job
SELECT SID,
SERIAL#,
S.SADDR,
S.PROGRAM,
S.USERNAME,
S.STATUS,
S.SQL_ID,
SQL.SQL_FULLTEXT
FROM V$SESSION S, DBA_DATAPUMP_SESSIONS D, V$SQLAREA SQL
WHERE S.SADDR = D.SADDR
AND S.SQL_ADDRESS = SQL.ADDRESS
AND S.SQL_ID = SQL.SQL_ID;

SELECT SID,SERIAL# FROM V$SESSION S,DBA_DATAPUMP_SESSIONS D WHERE S.SADDR=D.SADDR;

SELECT * FROM DBA_DATAPUMP_SESSIONS;

通過語句查看impdp進度
SELECT a.tablespace_name,
ROUND (a.total_size) "total_size(MB)",
ROUND (a.total_size) - ROUND (b.free_size, 3) "used_size(MB)",
ROUND (b.free_size, 3) "free_size(MB)",
ROUND (b.free_size / total_size * 100, 2) || '%' free_rate
FROM ( SELECT tablespace_name, SUM (bytes) / 1024 / 1024 total_size
FROM dba_data_files
GROUP BY tablespace_name) a,
( SELECT tablespace_name, SUM (bytes) / 1024 / 1024 free_size
FROM dba_free_space
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+);

使用視圖v$session_longops查看RMAN備份進度
SELECT SID,OPNAME, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
AND SOFAR <> TOTALWORK
order by "%_COMPLETE" desc;