ITPub博客

首页 > Linux操作系统 > Linux操作系统 > data pump学习笔记

data pump学习笔记

原创 Linux操作系统 作者:楚国布衣 时间:2009-08-05 17:38:19 0 删除 编辑
导出的脚本:
declare
  h1   NUMBER;
begin
  begin
      h1 := dbms_datapump.open (operation => 'EXPORT', job_mode => 'TABLE', job_name => 'EXPORT000041', version => 'COMPATIBLE');
  end;
  begin
     dbms_datapump.set_parallel(handle => h1, degree => 1);
  end;
  begin
     dbms_datapump.add_file(handle => h1, filename => 'EXPDAT.LOG', directory => 'DP_DIR', filetype => 3);
  end;
  begin
     dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 0);
  end;
  begin
     dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_EXPR', value => 'IN(''EYGLE'')');
  end;
  begin
     dbms_datapump.metadata_filter(handle => h1, name => 'NAME_EXPR', value => 'IN(''DP_TEST1'')');
  end;
  begin
     dbms_datapump.set_parameter(handle => h1, name => 'ESTIMATE', value => 'BLOCKS');
  end;
  begin
     dbms_datapump.add_file(handle => h1, filename => 'EXPDAT%U.DMP', directory => 'DP_DIR', filetype => 1);
  end;
  begin
     dbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', value => 1);
  end;
  begin
     dbms_datapump.set_parameter(handle => h1, name => 'DATA_ACCESS_METHOD', value => 'AUTOMATIC');
  end;
  begin
     dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0);
  end;
  begin
     dbms_datapump.detach(handle => h1);
  end;
end;
/
-----------------------------------------
*
* 导入的脚本:
------------------------------------------
declare
h1   NUMBER;
 begin
  begin
      h1 := dbms_datapump.open (operation => 'IMPORT', job_mode => 'TABLE', job_name => 'IMPORT000081', version => 'COMPATIBLE');
  end;
  begin
     dbms_datapump.set_parallel(handle => h1, degree => 1);
  end;
  begin
     dbms_datapump.add_file(handle => h1, filename => 'IMPORT.LOG', directory => 'DP_DIR', filetype => 3);
  end;
  begin
     dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 0);
  end;
  begin
     dbms_datapump.add_file(handle => h1, filename => 'EXPDAT%U.DMP', directory => 'DP_DIR', filetype => 1);
  end;
  begin
     dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_EXPR', value => 'IN(''EYGLE'')');
  end;
  begin
     dbms_datapump.metadata_filter(handle => h1, name => 'NAME_EXPR', value => 'IN(''DP_TEST1'')');
  end;
  begin
     dbms_datapump.set_parameter(handle => h1, name => 'DATA_ACCESS_METHOD', value => 'AUTOMATIC');
  end;
  begin
     dbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', value => 1);
  end;
  begin
     dbms_datapump.set_parameter(handle => h1, name => 'SKIP_UNUSABLE_INDEXES', value => 0);
  end;
  begin
     dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0);
  end;
  begin
     dbms_datapump.detach(handle => h1);
  end;
end;
/

[oracle@oracle10ga dp_dir]$ impdp system/edin2008 dumpfile=expdat01.dmp directory=dp_dir;
Import: Release 10.2.0.1.0 - 64bit Production on Wednesday, 05 August, 2009 16:34:16
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/home/oracle/dp_dir/expdat01.dmp" for read
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
 
下面成功执行:修改大小写  补上了logfile,顺序也改变了。
[oracle@oracle10ga dp_dir]$ ls -al
total 88
drwxr-xr-x  2 oracle oinstall  4096 Aug  5 16:26 .
drwx------ 19 oracle oinstall  4096 Aug  5 16:29 ..
-rw-r-----  1 oracle oinstall 69632 Aug  5 16:23 EXPDAT01.DMP
-rw-r--r--  1 oracle oinstall   830 Aug  5 16:23 EXPDAT.LOG
-rw-r--r--  1 oracle oinstall   529 Aug  5 16:46 import.log
[oracle@oracle10ga dp_dir]$ impdp eygle/eygle DIRECTORY=dp_dir DUMPFILE=EXPDAT01.DMP LOGFILE=EXPDAT.LOG
Import: Release 10.2.0.1.0 - 64bit Production on Wednesday, 05 August, 2009 16:58:20
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Master table "EYGLE"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "EYGLE"."SYS_IMPORT_FULL_01":  eygle/******** DIRECTORY=dp_dir DUMPFILE=EXPDAT01.DMP LOGFILE=EXPDAT.LOG
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "EYGLE"."DP_TEST1"                          6.031 KB      23 rows
Job "EYGLE"."SYS_IMPORT_FULL_01" successfully completed at 16:58:26
一次实验,去掉logfile,成功执行:
[oracle@oracle10ga dp_dir]$ impdp eygle/eygle DIRECTORY=dp_dir DUMPFILE=EXPDAT01.DMP
Import: Release 10.2.0.1.0 - 64bit Production on Wednesday, 05 August, 2009 17:03:41
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Master table "EYGLE"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "EYGLE"."SYS_IMPORT_FULL_01":  eygle/******** DIRECTORY=dp_dir DUMPFILE=EXPDAT01.DMP
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "EYGLE"."DP_TEST1"                          6.031 KB      23 rows
Job "EYGLE"."SYS_IMPORT_FULL_01" successfully completed at 17:03:48
将大写改为小写,立马出错:
[oracle@oracle10ga dp_dir]$ impdp eygle/eygle DIRECTORY=dp_dir DUMPFILE=EXPDAT01.dmp  <---将DMP改为dmp
Import: Release 10.2.0.1.0 - 64bit Production on Wednesday, 05 August, 2009 17:05:35
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/home/oracle/dp_dir/EXPDAT01.dmp" for read
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
在导的时候最好到文件目录中去查看,大小写以查看到的为标准。各个参数的顺序没有影响。
 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12880841/viewspace-611430/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2009-08-05

  • 博文量
    1
  • 访问量
    2384