plsql Oracle匯入dmp檔案

tiantangqiu發表於2014-07-01
SQLOracle
使用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

[b]dos操作[/b]

————————————————————————————————————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
-------------------------------------------------------------------------------------------------------------------------------------------------------------------

相關文章