DATA PUMPfenghuang - 千亿集团

DATA PUMPfenghuang

2019-02-09 10:50:53 | 作者: 寒云 | 标签: 简略,指令,数据 | 浏览: 7144

  数据泵 学习心得 (from:https://blog.sina.com.cn/hellohuangqian)

ORACLE 10g 开端供给了2个有用的东西以便利导入导出作业.分别是expdp/impdp
  先说说我的了解吧,本来的exp/imp东西是归于client/server形式,单线程操作,简略引起i/o瓶颈和操作中止新的datapump 处理了这些问题,datapump能够因为在服务器端操作,而且能够添加并行度,以进步i/o功能.操作意外中止也能够从中止点康复持续操作.比较有用.

  详细简略有用办法
  expdp
  举例 expdp hr/hr directory=expdir dumpfile=hr.dmp
很简略吧.首要要设置相关的保存文件目录.我设置在/tmp下
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Oct 9 17:12:06 2008
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL create or replace directory expdir
  2  as /tmp/;
Directory created.
SQL select * from dba_directories where directory_name=EXPDIR;
OWNER  DIRECTORY_NAME

DIRECTORY_PATH

SYS  EXPDIR
/tmp/

SQL select * from dba_directories where directory_name=DATA_PUMP_DIR;
OWNER  DIRECTORY_NAME

DIRECTORY_PATH

SYS  DATA_PUMP_DIR
/oracle/10g/rdbms/log/
设置完结后,接下来给予相关目录供给权限
SQL grant read,write on directory expdir to hr;
Grant succeeded.
SQL exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
完结后能够开端操作了
[oracle@localhost ~]$ expdp hr/hr dumpfile=hr.dmp directory=expdir
Export: Release 10.2.0.4.0 - Production on Thursday, 09 October, 2008 17:15:33
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HR"."SYS_EXPORT_SCHEMA_01":  hr/******** dumpfile=hr.dmp directory=expdir
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 448 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HR"."COUNTRIES"  6.085 KB  25 rows
. . exported "HR"."DEPARTMENTS"  6.632 KB  27 rows
. . exported "HR"."EMPLOYEES"  15.76 KB  107 rows
. . exported "HR"."JOBS"  6.609 KB  19 rows
. . exported "HR"."JOB_HISTORY"  6.585 KB  10 rows
. . exported "HR"."LOCATIONS"  7.710 KB  23 rows
. . exported "HR"."REGIONS"  5.289 KB  4 rows
Master table "HR"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_SCHEMA_01 is:
  /tmp/hr.dmp
Job "HR"."SYS_EXPORT_SCHEMA_01" successfully completed at 17:16:14
[oracle@localhost ~]$
OK 数据泵现已将数据导出完结了.
验证一下
[oracle@localhost ~]$ ll /tmp/hr.dmp
-rw-r- 1 oracle oinstall 425984 10-09 17:16 /tmp/hr.dmp
[oracle@localhost ~]$
没有问题.完结.

或许看过上面的过程发现指令好简略
  果真如此?
  假如我们输入下面就知道了
  [oracle@localhost ~]$ expdp -help
  其间关于关系到重发动和封闭等比较重要的参数是下面几个(影响expdp中止再次中止点运用)
  continue_client 
  start_job
  status
  stop_job

  老样子,用例子来介绍吧...想学的睁大眼睛看好了哦  (我来露一手)

首要发动方才的数据库导出指令,而且赋予一个使命名
[oracle@localhost ~]$ expdp hr/hr dumpfile=hr.dmp directory=expdir job_name=expfull

Export: Release 10.2.0.4.0 - Production on Thursday, 09 October, 2008 17:36:25

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HR"."EXPFULL":  hr/******** dumpfile=hr.dmp directory=expdir job_name=expfull
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 448 KB

好了,这个时分按 ctrl+c 退出交互界面定心,因为datapump现已提交了使命,退出界面不会形成使命中止,服务器会自己完结.
此时会进入export指令行形式
Export
记住上面4个指令?开端用了.
中止使命能够用stop_job
Export stop_job
Are you sure you wish to stop this job ([yes]/no): yes

[oracle@localhost ~]$

从头连进使命
[oracle@localhost ~]$ expdp hr/hr attach=expfull

Export: Release 10.2.0.4.0 - Production on Thursday, 09 October, 2008 17:41:44

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Job: EXPFULL
  Owner: HR
  Operation: EXPORT
  Creator Privs: FALSE
  GUID: 58CEC4441662D8D6E040007F01007F57
  Start Time: Thursday, 09 October, 2008 17:41:48
  Mode: SCHEMA
  Instance: orcl
  Max Parallelism: 1
  EXPORT Job Parameters:
  Parameter Name  Parameter Value:
  CLIENT_COMMAND  hr/******** dumpfile=hr.dmp directory=expdir job_name=expfull
  State: IDLING
  Bytes Processed: 0
  Current Parallelism: 1
  Job Error Count: 0
  Dump File: /tmp/hr.dmp
  bytes written: 12,288

Worker 1 Status:
  State: UNDEFINED

Export

用satatus检查
Export status

Job: EXPFULL
  Operation: EXPORT
  Mode: SCHEMA
  State: IDLING
  Bytes Processed: 0
  Current Parallelism: 1
  Job Error Count: 0
  Dump File: /tmp/hr.dmp
  bytes written: 12,288

Worker 1 Status:
  State: UNDEFINED

Export
从头发动使命
[oracle@localhost ~]$ expdp hr/hr attach=expfull

Export: Release 10.2.0.4.0 - Production on Thursday, 09 October, 2008 17:44:47

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Job: EXPFULL
  Owner: HR
  Operation: EXPORT
  Creator Privs: FALSE
  GUID: 58CEE0828384E4F0E040007F01007F84
  Start Time: Thursday, 09 October, 2008 17:44:50
  Mode: SCHEMA
  Instance: orcl
  Max Parallelism: 1
  EXPORT Job Parameters:
  Parameter Name  Parameter Value:
  CLIENT_COMMAND  hr/******** dumpfile=hr.dmp directory=expdir job_name=expfull
  State: IDLING
  Bytes Processed: 0
  Current Parallelism: 1
  Job Error Count: 0
  Dump File: /tmp/hr.dmp
  bytes written: 12,288

Worker 1 Status:
  State: UNDEFINED

Export start_job

Export continue_client
Job EXPFULLL has been reopened at Thursday, 09 October, 2008 17:44
Restarting "HR"."EXPFULLL":  hr/******** dumpfile=hr.dmp directory=expdir job_name=expfull
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-31693: Table data object "HR"."EXPFULL" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-00942: table or view does not exist
. . exported "HR"."COUNTRIES"  6.085 KB  25 rows
. . exported "HR"."DEPARTMENTS"  6.632 KB  27 rows
. . exported "HR"."EMPLOYEES"  15.76 KB  107 rows
. . exported "HR"."JOBS"  6.609 KB  19 rows
. . exported "HR"."JOB_HISTORY"  6.585 KB  10 rows
. . exported "HR"."LOCATIONS"  7.710 KB  23 rows
. . exported "HR"."REGIONS"  5.289 KB  4 rows
Master table "HR"."EXPFULLL" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.EXPFULLL is:
  /tmp/hr.dmp
Job "HR"."EXPFULLL" completed with 1 error(s) at 17:45:03


hr@BBK conn test/test
Connected.
test@BBK select table_name from user_tables;

no rows selected

test@BBK create table t (id int);

Table created.

test@BBK insert into t values(1);

1 row created.

test@BBK insert into t values(2);

1 row created.

test@BBK commit;

Commit complete.

test@BBK select * from t;

  ID

  1
  2

test@BBK conn /as sysdba
Connected.
sys@BBK grant read,write on directory expdir to test;

Grant succeeded.

sys@BBK exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[ora10g@bbk254 oracle]$ expdp test/test dumpfile=test.dmp directory=expdir

Export: Release 10.2.0.1.0 - Production on Monday, 06 September, 2010 16:10:23

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "TEST"."SYS_EXPORT_SCHEMA_01":  test/******** dumpfile=test.dmp directory=expdir
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
. . exported "TEST"."T"  4.937 KB  2 rows
Master table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is:
  /tmp/test.dmp
Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at 16:10:39


[ora10g@bbk254 oracle]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 6 16:10:57 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

idle conn /as sysdba
Connected.
sys@BBK conn test/test
Connected.
test@BBK insert into t values(0);

1 row created.

test@BBK commit;

Commit complete.

test@BBK select * from t;

  ID

  1
  2
  0

test@BBK exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[ora10g@bbk254 oracle]$ impdp test/test directory=expdir dumpfile=test.dmp tables=t table_exists_action=replace

Import: Release 10.2.0.1.0 - Production on Monday, 06 September, 2010 16:14:19

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "TEST"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_TABLE_01":  test/******** directory=expdir dumpfile=test.dmp tables=t table_exists_action=replace
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."T"  4.937 KB  2 rows
Job "TEST"."SYS_IMPORT_TABLE_01" successfully completed at 16:14:29

[ora10g@bbk254 oracle]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 6 16:15:02 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

sys@BBK conn test/test
Connected.
test@BBK select * from t;

  ID

  1
  2

test@BBK
版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表千亿集团立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章

阅读排行

  • 1

    按要求写sql句子itjob

    句子,学员,课程
  • 2

    检查数据库的SQL快报

    检查,数据库,检查表
  • 3

    DB2 备份和康复huabian

    康复,备份,数据库
  • 4

    运用MySQL头条

    运用,问题,效劳
  • 5
  • 6

    DATA PUMPfenghuang

    简略,指令,数据
  • 7

    Oracle Dimension 下alibaba

    邮编,区域,月度
  • 8

    Oracle失望锁和达观锁ITeyecsdn

    数据,时分,数据库
  • 9

    start with 用法ITeyeitjob

    子句,末梢,树形
  • 10

    mysql 根本指令ITeye头条

    用户,权限,体系