简要记录orcale XE 导入导出dmp过程
准备导出导入目录:
1.运行sql*plus
2.键入命令:connect username/password
3.创建导出目录 键入命令:create directory expdp_dir as 'c:\oraclexe\app\oracle\expdp_dir';
4.创建导入目录 键入命令:create directory impdp_dir as 'c:\oraclexe\app\oracle\impdp_dir';
相关命令:select * from dba_directories; 查看所有目录
创建用户:
1..键入命令:create user test identified by 123456;
2.赋予权限: grant dba to test; 此处直接省事赋予dba。
3.目录访问授权: grant READ,WRITE ON DIRECTORY expdp_dir to test; grant READ,WRITE ON DIRECTORY impdp_dir to test;
创建表空间:
1.创建临时表空间:create temporary tablespace test_temp tempfile 'c:\oraclexe\app\oracle\oradata\XE\test_temp.dbf' size 50m autoextend on next 50m maxsize 2018m extent management local;
2.创建表空间: create tablespace test_data logging datafile 'c:\oraclexe\app\oracle\oradata\XE\test_data.dbf' size 50m autoextend on next 50m maxsize 2018m extent management local;
3.与用户绑定: alter user test default tablespace test_data;
相关链接:http://www.cnblogs.com/netsql/articles/1745978.html
相关命令:select username,default_tablespace from dba_users;查询用户与tablespace 关系
没有此步骤可能导入时会报出 ora39171 ora01653
导出dmp:
1.win+r→运行cmd
2.键入命令:expdp system/root directory=expdp_dir dumpfile=test.dmp schemas=MCP
*命令结尾不能加 ';'
导入dmp:
1.键入命令:impdp system/root@XE remap_schema=MCP:TEST remap_tablespace=system:test_data,index:test_index directory=impdp_dir dumpfile=TEST.DMP
如果直接使用 impdp system/root@XE schema=TEST directory=impdp_dir dumpfile=TEST.DMP 可能会报出Schema TEST was not found
*命令结尾不能加 ';'
多个tablespace 用逗号隔开。
相关链接:http://www.cnblogs.com/lanzi/archive/2011/01/06/1927731.html