ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle11g数据泵的使用

oracle11g数据泵的使用

Linux操作系统 作者:xiaoyan5686670 时间:2015-11-24 11:52:49 0 删除 编辑

oracle11g数据泵的使用

要求:在一台机器使用数据泵导出数据,在另一台机器恢复

1、导出

---------------------------数据泵-----------------------------------
[oracle@fcdb2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Sep 14 15:03:43 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> create or replace directory  expdir as '/u01/app/';

Directory created.

SQL> grant read,write on directory expdir to cczq;

Grant succeeded.



[oracle@fcdb2 ~]$ expdp cczq/cczq   dumpfile=cczq.dmp directory=expdir

Export: Release 11.1.0.6.0 - 64bit Production on Wednesday, 14 September, 2011 15:11:15

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "CCZQ"."SYS_EXPORT_SCHEMA_01":  cczq/******** dumpfile=cczq.dmp directory=expdir
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 18.25 GB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/DB_LINK
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/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/JOB
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
. . exported "CCZQ"."TBL_ACCOUNT_ASSETS_LOG"             5.238 GB 48366194 rows
. . exported "CCZQ"."TBL_STOCK_BALANCE_LOG"              1.043 GB 9864020 rows
. . exported "CCZQ"."TBL_LOG_03"                         840.9 MB 8087999 rows
. . exported "CCZQ"."TBL_LOG_06"                         768.6 MB 7497304 rows
. . exported "CCZQ"."TBL_LOG_07"                         773.9 MB 7535127 rows
. . exported "CCZQ"."TBL_TL_STOCK_FUND"                  733.3 MB 21114809 rows
. . exported "CCZQ"."CC_KHZJHZ_03"                       744.6 MB 8525799 rows
. . exported "CCZQ"."CC_JY_03"                           404.6 MB 2581389 rows
. . exported "CCZQ"."KM_RESEARCH_REPORT"                 300.7 MB  169635 rows
. . exported "CCZQ"."TBL_ACCOUNT_BREAK":"ACB20110101"    347.3 MB 6189472 rows
. . exported "CCZQ"."CC_GYB_T_XXHFDXK"                   306.9 MB 3927422 rows
. . exported "CCZQ"."CC_T_KH"                            303.1 MB  909013 rows


Master table "CCZQ"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for CCZQ.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/cczq.dmp
Job "CCZQ"."SYS_EXPORT_SCHEMA_01" successfully completed at 15:23:06

[oracle@fcdb2 ~]$


2、导入
---REMAP_SCHEMA 指定转换的用户名 REMAP_TABLESPACE表空间 ,---schemas=cczq
-----在target创建目录
create or replace directory  impdir as '/u01/app/';   
grant read,write on directory impdir to tg_test;
impdp tg_test/tg_test   dumpfile=cczq.dmp directory=impdir;
Import: Release 11.1.0.6.0 - 64bit Production on Wednesday, 14 September, 2011 17:36:15

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "TG_TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TG_TEST"."SYS_IMPORT_FULL_01":  tg_test/******** dumpfile=cczq.dmp directory=impdir
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE failed to create with error:
ORA-00959: tablespace 'CCZQFC' does not exist
Failing sql is:
CREATE TABLE "CCZQ"."KM_REPORT_CLICK" ("ID" VARCHAR2(36), "REPORTID" VARCHAR2(36), "REPORTTYPE" VARCHAR2(36), "CUSTIP" VARCHAR2(36), "CLICKTIME" DATE
, "ACCOUNTNO" VARCHAR2(36), "COMEFROM" VARCHAR2(10)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048
576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_PO
ORA-39083: Object type TABLE failed to create with error:
ORA-00959: tablespace 'CCZQFC' does not exist
Failing sql is:
CREATE TABLE "CCZQ"."KM_REPORT_RATING" ("ID" VARCHAR2(36), "REPORTID" VARCHAR2(36), "REPORTTYPE" VARCHAR2(36), "RATING" NUMBER, "RATINGTYPE" VARCHAR2
(36), "ACCOUNTNO" VARCHAR2(36), "CUSTIP" VARCHAR2(36), "COMEFROM" VARCHAR2(10), "RATINGTIME" DATE) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCO
MPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCR
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "CCZQ"."TBL_ACCOUNT_ASSETS_LOG"             5.238 GB 48366194 rows
. . imported "CCZQ"."TBL_STOCK_BALANCE_LOG"              1.043 GB 9864020 rows
. . imported "CCZQ"."TBL_LOG_03"                         840.9 MB 8087999 rows
. . imported "CCZQ"."TBL_LOG_06"                         768.6 MB 7497304 rows

ok导入成功!

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

上一篇: ORACLE11G创建dblink
请登录后发表评论 登录
全部评论

注册时间:2012-07-25

  • 博文量
    108
  • 访问量
    196853