监控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;