oracle导入数据

导入数据

(pl/sql，system登入)创建表空间，用户，分配权限
Create tablespace ENVIR_DATA  datafile 'd:\zyfProgram\ENVIR_DATA.dbf' size 700m autoextend on;
Create user envir identified by 1 default tablespace ENVIR_DATA ;
Grant dba,connect,resource to envir;

导入数据，控制台执行：
Imp envir/1@orcl  file=D:\envir20180314.dmp  fromuser=hb touser=envir

启动服务命令;
控制台执行services.msc


pl/sql 查询当前用户所有表名:  SELECT * FROM USER_TABLES;

导出某些表的前多少条数据
exp sxgl/a123456A@172.20.13.214:/orcl  file=sxgl.dmp tables=(item_list,item_list_of_agent) query="'where rownum<=100'"

导入数据

1、SQL Plus登入系统账号，创建表空间(执行完会在D:\zyfProgram目录下生成 PURCHASE_GPMS.DBF文件)(表空间名字与别人导出的文件中的表空间名字一样)
create tablespace TB_JG datafile 'D:\zyfProgram\PURCHASE_GPMS.DBF' size 500m  autoextend on  next 50m maxsize 20480m extent management local;  
2、创建用户
create user purchase identified by 1 default tablespace TB_JG;
3、分配权限
grant connect,resource,dba to purchase;
4、cmd 导入
Imp purchase/1@orcl  file=D:\purchase_zf20180606.dmp  full=y

pl/sql 登入数据库 purchase/1

导入数据(impdp方式)

create tablespace EIS datafile 'D:\app\Administrator\EIS.DBF' size 500m  autoextend on  next 50m maxsize 20480m extent management local;
create user eis identified by 1 default tablespace EIS;
grant connect,resource,dba to eis;
CREATE OR REPLACE DIRECTORY DMPDIR AS  'd:\'; 


impdp eis/1@orcl directory=dmpdir dumpfile=TACLES.dmp full=y

 impdp eis/1@orcl directory=dmpdir dumpfile=TACLES2222.dmp table_exists_action=replace(更新库)

导入(表空间、用户迁移)

--导入(表空间、用户迁移)
impdp userid='projectdemo/1@orcl as sysdba' remap_schema=eis:projectdemo  remap_tablespace=eis:projectdemo directory=dmpdir dumpfile=eis.DMP logfile=test.log  

导出(expdp)

导出：
cmd执行
expdp jeesite/1 directory=dmpdir  dumpfile=jeesite
导出某一张表
expdp mweis/1 directory=dmpdir  dumpfile=mweis_sysquery tables=(XTWH_SYSQUERY)