一: 导出
导出在服务端的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://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.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一样
以上是鄙人自己操作 如有错误请指教!!