最新下载
热门教程
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
oracle数据库中expdp和impdp导出导入
时间:2022-06-29 09:51:58 编辑:袖梨 来源:一聚教程网
expdp和impdp是oracle从10g开始新增加的导出、导入功能。名称叫数据泵。
expdp:Data Pump Export
impdp:Data Pump Import
一、expdp、impdp和exp、imp的区别
expdp、impdp:
1、可以导入导出单个或多个数据库、用户(schema)、表空间、表。
2、强大的数据过滤功能。
3、速度快。
4、不支持XMLType数据。
exp、imp:
1、支持XMLType数据。
2、不支持FLOAT和DOUBLE数据类型。
3、功能和数据泵类似,更推荐使用数据泵除非是XMLType数据。
二、导出导入方法
小实验:将hr用户数据导出,新建用户hr2,再导入新建用户。
1、创建目录对象
目录是数据库对象,它是一个在物理主机文件系统上的目录的别名。
mkdir /tmp/expdata
chmod 777 /tmp/expdata
同时要注意oracle用户对导出目录要有读写权限。
SQL> create or replace directory dmpdir as '/tmp/expdata';
2、授予权限
SQL> grant read,write on directory dmpdir to hr;
3、导出
SQL> expdp hr/hr schemas=hr directory=dmpdir dumpfile=expdp.dmp logfile=expdp.log job_name=my_job;
Starting "HR"."MY_JOB": hr/******** schemas=hr directory=dmpdir dumpfile=expdp.dmp logfile=expdp.log job_name=my_job
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 512 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/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.367 KB 25 rows
. . exported "HR"."DEPARTMENTS" 7.007 KB 27 rows
. . exported "HR"."EMPLOYEES" 16.80 KB 107 rows
. . exported "HR"."JOBS" 6.992 KB 19 rows
. . exported "HR"."JOB_HISTORY" 7.054 KB 10 rows
. . exported "HR"."LOCATIONS" 8.273 KB 23 rows
. . exported "HR"."REGIONS" 5.476 KB 4 rows
. . exported "HR"."TEST_T" 6.796 KB 10 rows
Master table "HR"."MY_JOB" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.MY_JOB is:
/tmp/expdata/expdp.dmp
Job "HR"."MY_JOB" successfully completed at 09:07:28
4、创建用户hr2
-- USER SQL
CREATE USER hr2 IDENTIFIED BY hr2
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";
-- QUOTAS
ALTER USER hr2 QUOTA UNLIMITED ON USERS;
-- ROLES
GRANT "CONNECT" TO hr2 ;
GRANT "RESOURCE" TO hr2 ;
-- SYSTEM PRIVILEGES
GRANT CREATE ANY INDEX TO hr2 ;
GRANT CREATE VIEW TO hr2 ;
GRANT CREATE SESSION TO hr2 ;
5、导入
SQL> grant read,write on directory dmpdir to hr2;
SQL> impdp hr2/hr2 schemas=hr remap_schema=hr:hr2 directory=dmpdir dumpfile=expdp.dmp logfile=impdp.log job_name=my_jod2;
Master table "HR2"."MY_JOD2" successfully loaded/unloaded
Starting "HR2"."MY_JOD2": hr2/******** schemas=hr remap_schema=hr:hr2 directory=dmpdir dumpfile=expdp.dmp logfile=impdp.log job_name=my_jod2
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/TABLE_DATA
. . imported "HR2"."COUNTRIES" 6.367 KB 25 rows
. . imported "HR2"."DEPARTMENTS" 7.007 KB 27 rows
. . imported "HR2"."EMPLOYEES" 16.80 KB 107 rows
. . imported "HR2"."JOBS" 6.992 KB 19 rows
. . imported "HR2"."JOB_HISTORY" 7.054 KB 10 rows
. . imported "HR2"."LOCATIONS" 8.273 KB 23 rows
. . imported "HR2"."REGIONS" 5.476 KB 4 rows
. . imported "HR2"."TEST_T" 6.796 KB 10 rows
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
Job "HR2"."MY_JOD2" successfully completed at 09:24:39
remap_schema表示从用户hr导入到用户hr2。
相关文章
- 人们熟悉的寄居蟹属于以下哪种分类 神奇海洋11月21日答案 11-21
- 第五人格11.22共研服有什么更新 11月22日共研服更新内容介绍 11-21
- 原神恰斯卡怎么培养 11-21
- 无期迷途四星装束是谁 11-21
- 王者荣耀帝丹高中校服怎么获得 11-21
- 光遇姆明季后续版本怎么玩 11-21