将Linux下的資料庫服務端(reinsure/[email protected]:WLDB)的資料導出來,然後在導入本地(reinsure/[email protected]:ZYDEV)。
導出資料:
在Linux下建立邏輯目錄: create or replace directory data_dump_dir as‘/u02/dmp’;
用管理者給reinsure使用者賦予在指定目錄的操作權限: grant read,write on directory data_dump_dir to reinsure;
檢視本地的oracle版本:SELECT * from v$version(11.1.0.6.0)
在Linux下輸入如下指令expdp reinsure/[email protected] directory=data_dump_dir dumpfile=REINSURE_20150619.DMP logfile=reinsure_exp.log schemas=reinsure version=11.1.0.6.0
進入‘/u02/dmp’目錄,将REINSURE_20150619.DMP這個檔案導出到本地的‘D:\temp’目錄下。
導入資料到本地
1.建立臨時表空間:create temporary tablespace Tblspace_TEMP TEMPFILE 'D:\APP\ADMINISTRATOR\ORADATA\ZYDEV\Tblspace_TEMP.DBF' SIZE 32m REUSE
AUTOEXTEND ON NEXT 512k MAXSIZE UNLIMITED;
2.建立資料表空間:create tablespace LISTP datafile 'D:\APP\ADMINISTRATOR\ORADATA\ZYDEV\LISTP.DBF' size 32m reuse autoextend on maxsize unlimited default storage(initial 512k next 512k
minextents 1 maxextents unlimited pctincrease 0);
建立使用者: create user reinsure identified by reinsure default tablespace LISTP temporary tablespace Tblspace_TEMP;
4.為使用者授權:
grant connect to reinsure;
grant resource to reinsure;
grant create any table to reinsure;
grant create procedure to reinsure;
grant create session to reinsure;
grant debug connect session to reinsure;
grant select any table to reinsure;
grant unlimited tablespace to reinsure;
grant create any view to reinsure;
grant create any Synonym to reinsure;
grant select any dictionary to reinsure;
grant create any job to reinsure;
解鎖使用者: alter user reinsure account unlock;
建立目錄: create or replace directory data_dump_dir as 'd:/temp';
給使用者操作目錄權限: grant read,write on directory data_dump_dir to reinsure;
在DOS下輸入:impdp reinsure/[email protected] directory=data_dump_dir dumpfile=REINSURE_20150619.DMP logfile=reinsure_imp.log