oracle导入导出学习

exp parfile=exp.txt

一：表模式 1.导出单张表

------------------------------- userid=scott/tiger

file=/home/alvin/expbk/emp.dmp tables=emp buffer=1000000

log=/home/alvin/expbk/exp_emp.log

导入单张表

------------------------------- userid=scott/tiger

file=/home/alvin/expbk/emp.dmp tables=emp buffer=1000000

log=/home/alvin/expbk/imp_emp.log

2.带条件导出数据

------------------------------- userid=scott/tiger

file=/home/alvin/expbk/emp.dmp tables=emp buffer=1000000

log=/home/alvin/expbk/exp_emp.log query='where deptno=30' statistics=none 导入

------------------------------- userid=scott/tiger

file=/home/alvin/expbk/emp.dmp tables=emp buffer=1000000

log=/home/alvin/expbk/imp_emp.log

3.导出表时忽略约束

------------------------------- userid=scott/tiger

file=/home/alvin/expbk/emp.dmp tables=emp buffer=1000000

log=/home/alvin/expbk/exp_emp.log constraints=n 导入

------------------------------- userid=scott/tiger

file=/home/alvin/expbk/emp.dmp tables=emp buffer=1000000

log=/home/alvin/expbk/imp_emp.log

4.导出表时忽略授权

------------------------------- userid=scott/tiger

file=/home/alvin/expbk/emp.dmp tables=emp buffer=1000000

log=/home/alvin/expbk/exp_emp.log grants=n 导入

------------------------------- userid=scott/tiger

file=/home/alvin/expbk/emp.dmp tables=emp buffer=1000000

log=/home/alvin/expbk/imp_emp.log

5.闪回导出(只能使用system用户) ------------------------------- userid=system/oracle tables=scott.emp

file=/home/alvin/exp_emp.dmp log=/home/alvin/expbk/exp_emp.log

flashback_time=\hh24:mi:ss')\ 导入

------------------------------- userid=system/oracle tables=scott.emp

file=/home/alvin/exp_emp.dmp log=/home/alvin/expbk/imp_emp.log

6.导出时显示反馈信息

------------------------------- userid=scott/tiger

file=/home/alvin/expbk/t01.dmp tables=emp buffer=1000000

log=/home/alvin/expbk/exp_t01.log feedback=1000 导入

------------------------------- userid=scott/tiger

file=/home/alvin/expbk/t01.dmp tables=emp buffer=1000000

log=/home/alvin/expbk/imp_t01.log feedback=1000

7.只导出元数据

------------------------------- userid=scott/tiger

file=/home/alvin/expbk/t01.dmp tables=t01 buffer=1000000

log=/home/alvin/expbk/exp_t01.log rows=n

compress=n 导入

------------------------------- userid=scott/tiger

file=/home/alvin/expbk/t01.dmp tables=t01 buffer=1000000

log=/home/alvin/expbk/imp_t01.log

追加导入

------------------------------- userid=scott/tiger

file=/home/alvin/expbk/t01.dmp tables=t01 buffer=1000000

log=/home/alvin/expbk/imp_t01.log ignore=y

导出多张表

------------------------------- userid=scott/tiger

file=/home/alvin/expbk/t01.dmp tables=t01,emp,dept buffer=1000000

log=/home/alvin/expbk/exp_t01.log 导入

------------------------------- userid=scott/tiger

file=/home/alvin/expbk/t01.dmp tables=emp,dept buffer=1000000

log=/home/alvin/expbk/imp_t01.log 导入

------------------------------- userid=scott/tiger

file=/home/alvin/expbk/t01.dmp #tables=emp,dept full=y

buffer=1000000

log=/home/alvin/expbk/imp_t01.log

二：用户模式 导出用户

------------------------------- userid=scott/tiger

file=/home/alvin/expbk/scott.dmp owner=scott buffer=1000000

log=/home/alvin/expbk/exp_scott.log

drop user scott cascade;

grant connect,resource to scott identified by tiger; grant create database link,create synonym to scott;

导入用户

-------------------------------- userid=scott/tiger

file=/home/alvin/expbk/scott.dmp full=y

buffer=1000000

log=/home/alvin/expbk/imp_scott.log

跨用户用户数据

-------------------------------- userid=system/oracle

file=/home/alvin/expbk/scott.dmp fromuser=scott touser=u1

#tables=dept,emp,salgrad buffer=1000000

log=/home/alvin/expbk/imp_scott.log

三：表空间模式 导出表空间

------------------------------- userid=system/oracle

file=/home/alvin/expbk/tbs254.dmp tablespaces=tbs254 buffer=1000000

log=/home/alvin/expbk/exp_tbs254.log

drop tablespace tbs254 including contents and datafiles cascade constraints;

create tablespace tbs254 datafile

'/opt/oracle/oradata/alvin/tbs254.dbf' size 10m;

导入表空间

------------------------------- userid=system/oracle

file=/home/alvin/expbk/tbs254.dmp full=y

buffer=1000000

log=/home/alvin/expbk/imp_tbs254.log

四：传输表表空间模式

alter tablespace tbs254 read only;

导出表空间的元数据

------------------------------- userid='sys/oracle as sysdba' file=/home/alvin/expbk/tbs254.dmp tablespaces=tbs254 TRANSPORT_TABLESPACE=y