Datapump 小节-1

实验过程如下：

dexter(target) :
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:

1. 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:
   
   
   1. 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
      
      
      
   2. 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
      
      
      
   3. 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

• DBA_DATAPUMP_JOBS - all active Data Pump jobs and the state of each 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