oracle 数据泵导入导出实例
一: 导出
导出在服务端的DB
0、查询路径
SELECT * FROM DBA_DIRECTORIES
1、创建路径
create or replace directory dump_dir as 'd:/u01'; --此路径是导出的DMP文件放的地址 注意 此地址一般不要有中文
2、赋权限
grant read,write on directory dump_dir to system;--赴权限
3、导出数据
expdp system/sys@dbname directory=dump_dir full=y parallel=6 dumpfile=qce3sv.dmp logfile=qce3sv.log
--此时的dump_dir 就会自动的找到你创建的dmo路径来从目标路径导出到你创建的存储路径dump_dir
二:导入
此方法的导出和导入是配对出现的。
1:首先要创建和导出的DB 一样的表空间和临时表空间 例如:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
CREATE TABLESPACE TSP_E3S DATAFILE 'E:\ZNA\ZNAV.ORA' SIZE 1000M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED, 'E:\ZNA\ZNAV02.ORA' SIZE 1000M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED, 'E:\ZNA\ZNAV03.ORA' SIZE 1000M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON; CREATE TEMPORARY TABLESPACE TST_E3S TEMPFILE 'E:\ZNA\ZNATEMP.ORA' SIZE 1000M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED TABLESPACE GROUP '' EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
2:创建和导出DB一致的用户信息 可以直接从源db的用户中,把脚本corp过来执行 例如:
CREATE USER E3S IDENTIFIED BY VALUES 'e3s' DEFAULT TABLESPACE TSP_E3S TEMPORARY TABLESPACE TST_E3S PROFILE DEFAULT ACCOUNT UNLOCK; -- 4 Roles for E3S GRANT DBA TO E3S; GRANT EXECUTE_CATALOG_ROLE TO E3S; GRANT CONNECT TO E3S WITH ADMIN OPTION; GRANT EXP_FULL_DATABASE TO E3S; ALTER USER E3S DEFAULT ROLE ALL; -- 1 System Privilege for E3S GRANT UNLIMITED TABLESPACE TO E3S; CREATE USER READV IDENTIFIED BY VALUES 'readv' DEFAULT TABLESPACE TSP_E3S TEMPORARY TABLESPACE TST_E3S PROFILE DEFAULT ACCOUNT UNLOCK; CREATE USER MGMT_VIEW IDENTIFIED BY VALUES 'mgmt' DEFAULT TABLESPACE SYSTEM TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK; -- 1 Role for MGMT_VIEW GRANT MGMT_USER TO MGMT_VIEW; ALTER USER MGMT_VIEW DEFAULT ROLE ALL; -- 1 Object Privilege for MGMT_VIEW GRANT EXECUTE ON SYSMAN.SETEMVIEWUSERCONTEXT TO MGMT_VIEW;
3:导入脚本
Impdp system/password@dbname directory=dump_dir dumpfile=XXX.dmp logfile=XXX.log --这里的XXX.dmp 即是你导出的。dmp文件的name log一样
以上是鄙人自己操作 如有错误请指教!!