实验过程如下:
SQL> create directory dpdir as 'c:\' ;
目录已创建。
SQL> create database link linklocal connect to dex identified by xiaojun using '127.0.0.1:1521/startrek' ;
数据库链接已创建。
dex(source) :
dex@STARTREK>select table_name , tablespace_name from user_tables ;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
P USERS
C USERS
dexter :
impdp dexter/xiaojun directory=dpfie network_link=linklocal remap_tablespace=users:example remap_schema=dex:dexter logfile=dexterimp.log
***************************if using datapump in rac env
如果是集群,并且没有使用共享文件系统作为dump file的目录,那么需要使用参数cluster=n 否则,其他rac 节点没法访问到dump file可能会引起bug。
CLUSTER=N
在执行导入的时候,会产生一个导入|到导出的状态表,在导入时连接的用户下,名为sys_[schema|table|tablespace]_N 由此表可以查看impdp|expdp的状态。
*************************monitoring datapump
可以使用DBA_DATAPUBMP_JOBS和DBA_DATAPUMP_SESSIONS视图来显示数据泵取作业的信息。
select * from DBA_DATAPUMP_JOBS;
select * from DBA_DATAPUMP_SESSIONS;
select sid,serial# from v$session s,dba_datapump_sessions d where s.saddr=d.saddr;
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(+);
*************************monitoring datapump
col table_name format a30
select substr(sql_text, instr(sql_text,'"')+1,
instr(sql_text,'"', 1, 2)-instr(sql_text,'"')-1)
table_name,
rows_processed,
round((sysdate
- to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))
*24*60, 1) minutes,
trunc(rows_processed /
((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))
*24*60)) rows_per_min
from
v$sqlarea
where
upper(sql_text) like 'INSERT % INTO "%'
and
command_type = 2
and
open_versions > 0;
select
sid,
serial#
from
v$session s,
dba_datapump_sessions d
where
s.saddr = d.saddr;
select
sid,
serial#,
sofar,
totalwork
from
v$session_longops;
********************************using filesize and parallel
expdp dexter/xiaojun directory=dpdir dumpfile=dex_expdp_%U.dmp logfile=dex_expdp.log filesize=1g parallel=5
如果expdp中使用了filesize参数,impdp中可以这样使用
impdp hr DIRECTORY=dpump_dir1 DUMPFILE=dpump_dir2:exp1.dmp, exp2%U.dmp
From metalink:
- queries against the data dictionary:
The DBA_DATAPUMP_JOBS view shows whether EXPDP or IMPDP jobs are still running, or have terminated with either a success or failure status. The data dictionary view V$SESSION_LONGOPS can be queried to get more detailed information on how much work is done already by the exporting job. E.g. the following queries can be used for this:
- the status of the job:
SQL> select * from dba_datapump_jobs;
OWNER_NAME JOB_NAME
------------------------------ ------------------------------
OPERATION JOB_MODE
------------------------------ ------------------------------
STATE DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
------------------------------ ---------- ----------------- -----------------
SYS SYS_EXPORT_FULL_01
EXPORT FULL
EXECUTING 1 1 3 - the percentage of work done, like in:
SQL> SELECT b.username, a.sid, b.opname, b.target,
round(b.SOFAR*100/b.TOTALWORK,0) || '%' as "%DONE", b.TIME_REMAINING,
to_char(b.start_time,'YYYY/MM/DD HH24:MI:SS') start_time
FROM v$session_longops b, v$session a
WHERE a.sid = b.sid ORDER BY 6;
USERNAME SID
------------------------------ ----------
OPNAME
----------------------------------------------------------------
TARGET
----------------------------------------------------------------
%DONE TIME_REMAINING START_TIME
----------------------------------------- -------------- -------------------
SYS 140
SYS_EXPORT_FULL_01
0% 2012/07/12 12:57:21 - the percentage of work done and the current status of the Datapump job, like in:
SQL> SELECT sl.sid, sl.serial#, sl.sofar, sl.totalwork, dp.owner_name, dp.state, dp.job_mode
FROM v$session_longops sl, v$datapump_job dp
WHERE sl.opname = dp.job_name
AND sl.sofar != sl.totalwork;
SID SERIAL# SOFAR TOTALWORK OWNER_NAME
---------- ---------- ---------- ---------- ------------------------------
STATE JOB_MODE
------------------------------ ------------------------------
140 9 0 67 SYS
EXECUTING FULL
- the status of the job:
• USER_DATAPUMP_JOBS – summary of the user’s active Data Pump jobs
• DBA_DATAPUMP_SESSIONS – all active user sessions that are attached to a Data Pump job
• V$SESSION_LONGOPS – shows all progress on each active Data Pump job
Or press CTRL+C
status