plsql Oracle导入dmp文件

使用PL/SQL 工具-->导入表-->Oracle导入 选择dmp文件


命令导入：
sqlplus system/admin@alms

drop user alms2010 cascade;

create user alms2010
default tablespace USERS
temporary tablespace TEMP
identified by almsacconsys;

grant connect, resource,create view to alms2010;

imp system/admin@alms fromuser=alms2010 touser=alms2010 file=43.dmp

有时候导入不成功可以这样试试：imp system/admin fromuser=alms2010 touser=alms2010 file=43.dmp

dos操作

————————————————————————————————————DOS执行——————————————————————————————————————————————

--导出alms2013用户的dmp文件（所里）
exp alms2013/almsacconsys@alms  file=d:/dbdata/0731.dmp owner=alms2013 

--需要导入的机器（院里），用dba权限用户登录
sqlplus system/almsacconsys@alms  

--删除旧的用户alms2013
drop user alms2013 cascade; 

--重新创建alms2013用户并赋权（注意判断如果表空间存在则不用再次创建）
CREATE TEMPORARY TABLESPACE alms2013_temp
 TEMPFILE 'd:\oracle\product\10.2.0\oradata\alms\alms2013_temp.dbf'
 SIZE 32M
 AUTOEXTEND ON
 NEXT 32M MAXSIZE 2048M
 EXTENT MANAGEMENT LOCAL;

CREATE TABLESPACE alms2013_data
 LOGGING
 DATAFILE 'd:\oracle\product\10.2.0\oradata\alms\alms2013_data.dbf'
 SIZE 32M
 AUTOEXTEND ON
 NEXT 32M MAXSIZE 2048M
 EXTENT MANAGEMENT LOCAL;

CREATE USER alms2013 IDENTIFIED BY almsacconsys
 DEFAULT TABLESPACE alms2013_data
 TEMPORARY TABLESPACE alms2013_temp;

GRANT 
CREATE SESSION, CREATE ANY TABLE, CREATE ANY VIEW ,CREATE ANY INDEX, CREATE ANY PROCEDURE, 
ALTER ANY TABLE, ALTER ANY PROCEDURE, 
DROP ANY TABLE, DROP ANY VIEW, DROP ANY INDEX, DROP ANY PROCEDURE, SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE
TO alms2013;

GRANT DBA TO alms2013; 

ctrl+c退出当前dos ，执行如下命令导入：
imp userid=alms2013/almsacconsys@alms file=d:\0731.dmp fromuser=alms2013 touser=alms2013
-------------------------------------------------------------------------------------------------------------------------------------------------------------------