DBMS_DATAPUMP包

用来在数据库之间的移动全部或部分数据和元数据。这种方式用的不多，可以直接使用数据泵即可

2
概述

支持功能如下：
2.1 源和目标数据库可以有不同的硬件，操作系统，字符集，和时区
2.2 支持所有对象类型和数据类型
2.3 传输数据库之间的数据和元数据，无需使用任何中间文件
2.4 可移动数据库的一个子集，根据对象类型和对象的名称
2.5 Schemas, datafile, tablespace 可在导入时传输.
2.6 支持断点续传
2.7 可以修改导入或导出的资源
2.8 Oracle专有的格式的数据可以被卸载和加载

  handler := DBMS_DATAPUMP.OPEN('EXPORT','SCHEMA',NULL,'EXAMPLE1','LATEST');

  DBMS_DATAPUMP.ADD_FILE(handler,'example1.dmp','DMPDIR');

  DBMS_DATAPUMP.METADATA_FILTER(handler,'SCHEMA_EXPR','IN (''HR'')');

  DBMS_DATAPUMP.START_JOB(handler);

  percent_done := 0;
  job_state := 'UNDEFINED';
  while (job_state != 'COMPLETED') and (job_state != 'STOPPED') loop
    dbms_datapump.get_status(handler,
           dbms_datapump.ku$_status_job_error +
           dbms_datapump.ku$_status_job_status +
           dbms_datapump.ku$_status_wip, -1, job_state, sts);
    js := sts.job_status;

    if js.percent_done != percent_done
    then
      dbms_output.put_line('*** Job percent done = ' ||
                           to_char(js.percent_done));
      percent_done := js.percent_done;
    end if;

    if (bitand(sts.mask, dbms_datapump.ku$_status_wip) != 0)
    then
      le := sts.wip;
    else
      if (bitand(sts.mask,dbms_datapump.ku$_status_job_error) != 0)
      then
        le := sts.error;
      else
        le := null;
      end if;
    end if;
    if le is not null
    then
      ind := le.FIRST;
      while ind is not null loop
        dbms_output.put_line(le(ind).LogText);
        ind := le.NEXT(ind);
      end loop;
    end if;
  end loop;

  dbms_output.put_line('Job has completed');
  dbms_output.put_line('Final job state = ' || job_state);
  dbms_datapump.detach(handler);

复制

导入示例:

   handler := DBMS_DATAPUMP.OPEN('IMPORT','FULL',NULL,'EXAMPLE2');

   DBMS_DATAPUMP.ADD_FILE(handler ,'example1.dmp','DMPDIR');

   DBMS_DATAPUMP.METADATA_REMAP(handler , 'REMAP_SCHEMA','hr', 'scott');

   BMS_DATAPUMP.SET_PARAMETER(handler , 'TABLE_EXISTS_ACTION','SKIP');

   DBMS_DATAPUMP.START_JOB(handler);

   percent_done := 0;
   job_state := 'UNDEFINED';
   while (job_state != 'COMPLETED') and (job_state != 'STOPPED') loop
     dbms_datapump.get_status(h1,
           dbms_datapump.ku$_status_job_error +
           dbms_datapump.ku$_status_job_status +
           dbms_datapump.ku$_status_wip,-1,job_state,sts);
     js := sts.job_status;

     if js.percent_done != percent_done then
       dbms_output.put_line('*** Job percent done = ' ||
           to_char(js.percent_done));
       percent_done := js.percent_done;
     end if;

     if (bitand(sts.mask,dbms_datapump.ku$_status_wip) != 0)
     then
       le := sts.wip;
     else
       if (bitand(sts.mask,dbms_datapump.ku$_status_job_error) != 0)
       then
         le := sts.error;
       else
         le := null;
       end if;
     end if;

     if le is not null
     then
       ind := le.FIRST;
       while ind is not null loop
         dbms_output.put_line(le(ind).LogText);
         ind := le.NEXT(ind);
       end loop;
     end if;
  end loop;

  dbms_output.put_line('Job has completed');
  dbms_output.put_line('Final job state = ' || job_state);
  dbms_datapump.detach(handler);
复制

--导出当前schema下的所有表并过滤特定表
 
	DECLARE
	   l_dp_handle   NUMBER;
	BEGIN
	   l_dp_handle :=
	      DBMS_DATAPUMP.open (operation => 'EXPORT', job_mode => 'TABLE');
	   DBMS_DATAPUMP.
	    add_file (handle      => l_dp_handle
	            , filename    => 'scott_filter_2.dmp'
	            , directory   => 'DB_DUMP_DIR'
	            , filetype    => DBMS_DATAPUMP.KU$FILE_TYPE_DUMP_FILE);
	   DBMS_DATAPUMP.
	    add_file (handle      => l_dp_handle
	            , filename    => 'scott_filter_2.log'
	            , directory   => 'DB_DUMP_DIR'
	            , filetype    => DBMS_DATAPUMP.KU$FILE_TYPE_LOG_FILE);
	   DBMS_DATAPUMP.metadata_filter (handle        => l_dp_handle
	                                , name          => 'NAME_EXPR'
	                                , VALUE         => ' !=''EMP'' ');
	   DBMS_DATAPUMP.metadata_filter (handle        => l_dp_handle
	                                , name          => 'NAME_EXPR'
	                                , VALUE         => ' !=''DEPT'' ');
	   DBMS_DATAPUMP.start_job (l_dp_handle);
	   DBMS_DATAPUMP.detach (l_dp_handle);
	END;
	/

复制

对于过滤表上的特定记录可以使用多种SQL表达式，如 LIKE, NOT LIKE,IN, NOT IN, = , != 符号等

过滤掉不等于的就是等于的了，

参考：https://blog.csdn.net/leshami/article/details/7195849

把EXPORT改成IMPORT就是导入了

declare

hand number;
vmessage varchar2(2000);

begin
hand := dbms_datapump.open (operation => 'IMPORT',
job_mode => 'SCHEMA',
remote_link => 'DUMP_LINK');

dbms_datapump.add_file (handle => hand,
filename => 'impdp_testS.log',
directory => 'DUMP_DIR',
filetype => dbms_datapump.ku$_file_type_log_file);

dbms_datapump.metadata_filter (handle => hand,
name => 'SCHEMA_LIST',
value => 'TESTS');

dbms_datapump.start_job (hand);

end;
复制

参考：
http://blog.itpub.net/10130206/viewspace-1039773/