oracle11g数据泵的使用
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博客 ” ，链接：https://blog.itpub.net/24531354/viewspace-707431/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
oracle修改数据库字符集
请登录后发表评论
登录
全部评论
