oracle11g数据泵的使用

最新推荐文章于 2021-06-10 16:17:44 发布

ciyang1448

最新推荐文章于 2021-06-10 16:17:44 发布

阅读量118

点赞数

文章标签：数据库

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/24531354/viewspace-707431/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24531354/viewspace-707431/