Windows,Linux 环境下导入导出数据

将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