oracle exp/imp数据备份

1、Oracle11g默认对空表不分配segment，故使用exp导出Oracle11g数据库时，空表不会导出。

解决办法：设置deferred_segment_creation 参数为FALSE后，无论是空表还是非空表，都分配segment。

在 sqlplus 中，执行如下命令：

SQL>alter system set deferred_segment_creation=false;

查看：

SQL>show parameter deferred_segment_creation;

注：该值设置后只对修改后新增的表产生作用，对之前建立的空表不起作用。

2、下面介绍的是导入导出的实例。

数据导出：

增加参数：statistics=none 可以避免导出问题数据

将数据库TEST完全导出,用户名system 密码manager 导出到D:daochu.dmp中

exp system/manager@TEST file=d:daochu.dmp full=y

将数据库中system用户与sys用户的表导出

exp system/manager@TEST file=d:daochu.dmp owner=(system,sys) statistics=none

将数据库中的表inner_notify、notify_staff_relat导出

exp aichannel/aichannel@TESTDB2 file= d:datanewsmgnt.dmp tables=(inner_notify,notify_staff_relat)

将数据库中的表table1中的字段filed1以"00"打头的数据导出

exp system/manager@TEST file=d:daochu.dmp tables=(table1) query=" where filed1 like '00%'"

上面是常用的导出，对于压缩，既用winzip把dmp文件可以很好的压缩。也可以在上面命令后面 加上 compress=y 来实现。

数据导入：

将备份数据库文件中的数据导入指定的数据库SampleDB 中,如果 SampleDB 已存在该表,则不再导入;

imp system/manager@TEST file=E:/sampleDB.dmp full=y ignore=y

imp system/manager@TEST file=E:/sampleDB.dmp full=y ignore=y buffer=819200

将d:/daochu.dmp中的表table1 导入

imp system/manager@TEST file=E:/sampleDB.dmp tables=(table1)

导入一个完整数据库

imp system/manager file=bible_db log=dible_db full=y ignore=y

导入一个或一组指定用户所属的全部表、索引和其他对象

imp system/manager file=seapark log=seapark fromuser=seapark

imp system/manager file=seapark log=seapark fromuser=(seapark,amy,amyc,harold)

将一个用户所属的数据导入另一个用户

imp system/manager file=tank log=tank fromuser=seapark touser=seapark_copy

imp system/manager file=tank log=tank fromuser=(seapark,amy) touser=(seapark1, amy1)

导入一个表

imp system/manager file=tank log=tank fromuser=seapark TABLES=(a,b)

从多个文件导入

imp system/manager file=(paycheck_1,paycheck_2,paycheck_3,paycheck_4) log=paycheck, filesize=1G full=y

增量导入

imp system./manager inctype= RECTORE FULL=Y FILE=A

不少情况下要先将表彻底删除，然后导入。

注：正常情况下，安装oracle会在环境变量中添加oracle安装目录\bin的全局路径，因此imp/exp命令可直接在dos中执行

“system/manager@TEST” 中的test不是实例名，是tnsnames.ora中配置的数据库名

数据泵导入导出：

一、创建逻辑目录，该命令不会在操作系统创建真正的目录，最好以system等管理员创建。

create directory dpdata1 as 'd:\test\dump';

二、查看管理理员目录（同时查看操作系统是否存在，因为Oracle并不关心该目录是否存在，如果不存在，则出错）

select * from dba_directories;

三、给scott用户赋予在指定目录的操作权限，最好以system等管理员赋予。

grant read,write on directory dpdata1 to scott;

四、导出数据

1)按用户导

expdp scott/tiger@orcl schemas=scott dumpfile=expdp.dmp DIRECTORY=dpdata1;

2)并行进程parallel

expdp scott/tiger@orcl directory=dpdata1 dumpfile=scott3.dmp parallel=40 job_name=scott3

3)按表名导

expdp scott/tiger@orcl TABLES=emp,dept dumpfile=expdp.dmp DIRECTORY=dpdata1;

4)按查询条件导

expdp scott/tiger@orcl directory=dpdata1 dumpfile=expdp.dmp Tables=emp query='WHERE deptno=20';

5)按表空间导

expdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=temp,example;

6)导整个数据库

expdp system/manager DIRECTORY=dpdata1 DUMPFILE=full.dmp FULL=y;

五、还原数据

1)导到指定用户下

impdp scott/tiger DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=scott;

2)改变表的owner

impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp TABLES=scott.dept REMAP_SCHEMA=scott:system;

3)导入表空间

impdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=example;

4)导入数据库

impdp system/manager DIRECTORY=dump_dir DUMPFILE=full.dmp FULL=y;

5)追加数据

impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=system TABLE_EXISTS_ACTION

--删除空的表空间，但是不包含物理文件

drop tablespace tablespace_name;

--删除非空表空间，但是不包含物理文件

drop tablespace tablespace_name including contents;

--删除空表空间，包含物理文件

drop tablespace tablespace_name including datafiles;

--删除非空表空间，包含物理文件

drop tablespace tablespace_name including contents and datafiles;

--如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段，就要加上CASCADE CONSTRAINTS

drop tablespace tablespace_name including contents and datafiles CASCADE CONSTRAINTS;