Oracle数据库实例导入导出dmp文件-建表空间、用户等

一、导入dmp

--表空间
create tablespace STU datafile 'E:\oracle\bakDB\stu.dbf' size 50M autoextend  on;
--用户
create user ucrm identified by javaWMF123 default tablespace STU;
--给用户授权
grant connect, resource, dba to ucrm;
--cmd窗口下执行导入命令语句
imp ucrm/java123456@127.0.0.1/orcl file=E:\oracle\bakDB\stu.dmp full=y;
或
imp ucrm/java123456 file=/opt/ucrm/dmp/ucrm20181018.dmp full=y ignore=y log=/home/jysp/app/jysp/product/11.2.0/dbhome_1/oradata/eoms/eoms.log;  --ok
imp ucrm/java123456 file=/opt/ucrm/dmp/ucrm20181018.dmp full=y ignore=y buffer=1024000000;
imp ucrm/java123456 file=/opt/ucrm/dmp/ucrm20181018.dmp full=y ignore=y buffer=1024000000 commit=y grants=n;  --ok
imp ucrm/java123456 file=/opt/ucrm/dmp/ucrm20181018.dmp fromuser=EOMS touser=EOMS ignore=y buffer=1024000000 commit=y;  --ok
nohup imp ucrm/java123456 file=/opt/ucrm/dmp/ucrm20181018.dmp full=y ignore=y buffer=5120000 commit=yes feedback=1000;

imp eoms/java123456@127.0.0.1/sp1000 file=/opt/ucrm/dmp/ucrm20181018.dmp  full=y ignore=y;

二、删除用户和表空间

1、删除用户，及级联关系也删除掉
drop user ucrm cascade;
2、删除表空间，及对应的表空间文件也删除掉
drop tablespace STU including contents and datafiles cascade constraint;

三、导出dmp

--使用full=y时，student必须具有dba权限或者具有exp_full_database权限；
exp student/123456@192.168.100.100:1521/orcl buffer=2048000 compress=n file=D:\xxx.dmp log=/home/xxx.log full=y;

exp student/123456@192.168.100.111:1521/orcl buffer=512000 compress=n file=D:\eoms1023.dmp log=D:\eoms1023.log owner=EOMS;

四、导入导出demo

//导入导出部分表
--导出部分表
exp STU/javaCODE123@192.168.1.199:1521/orcl buffer=1024000 compress=n rows=y indexes=y file=D:\STU20190307.dmp tables=(TA,TB,TC,TD)
--导入dmp文件
imp STU/javaCODE123 file=/home/oa/sql/STU20190307.dmp  fromuser=STU touser=STU ignore=y buffer=4096000 commit=y