Oracle 11g R2 用exp无法导出空表解决方案

Oracle 11g R2 用exp无法导出空表解决方案

问题描述

Oracle 11g R2在linux平台上，使用Oracle的exp备份导出命令，备份全量数据库，然后删除用户，之后再重新创建用户，再通过imp还原导入的方式进行数据库还原，发现新还原的数据库缺少了许多表。

---

使用的备份命令

exp hsap/handsome@gfdb file=20160104_01_01.dmp full=y

使用的还原命令

imp hsap/handsome@gfdb file=20160104_01_01.dmp full=y ignore=y

删除用户的命令

drop user hsap cascade;

创建用户的命令

create user hsap identified by "oracle" default tablespace hsap temporary tablespace TEMP profile DEFAULT;
grant select, insert, update, delete, references, alter, index on INCEXP to hsap;
grant select, insert, update, delete, references, alter, index on INCFIL to hsap;
grant select, insert, update, delete, references, alter, index on INCVID to hsap;
grant execute on SET_TABLESPACE to hsap;
grant aq_administrator_role to hsap with admin option;
grant dba to hsap with admin option;
grant mgmt_user to hsap;
grant create materialized view to hsap;
grant create table to hsap;
grant global query rewrite to hsap;
grant select any table to hsap;

设备环境

数据库： Oracle 11g R2
操作系统： Linux

出错分析

由于发现通过备份文件还原导入的库的表，绝大多数都有数据，仅有个别表没有数据，故怀疑备份导出的文件不全，仅备份了有数据的表。最后发现原因为：11G中有个新特性，当表无数据时，不分配segment，以节省空间，而使用exp命令时，无Segment的表不会被导出。

数据找回

由于备份出来的库不全，缺少了许多空表的定义。为了可以恢复数据。本人采用了最笨的方式……不过由于我拥有库的全量定义，仅仅是缺少了交易数据，但交易数据又在新备份出来的文件中。所以还原方式可以为：

1. 将全量的库还原导入新库中
2. 清空删除新库中所有表中的数据
3. 再将具有交易数据的库还原导入至新库中

贴出清空库所有数据的脚本：

-- 禁用所有外键约束
select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where constraint_type='R';
-- 截断使用表数据
select 'truncate table '||table_name||';' from user_tables;
-- 启用所有外键约束
select 'alter table '||table_name||' enable constraint '||constraint_name||';' from user_constraints where constraint_type='R';

后记：其实如果数据库开启了归档模式，应该是可以通过日志的方式找回的，不需要想笔者这样如此麻烦。

解决方案

方法一
insert一行，再rollback就产生segment（尚未验证）
点评：此种方式即使可行，也具有其局限性：
1、 新创建的表，依旧不会自动分配segment。
2、 每次通过还原方式导入的数据库，在备份之前，均需要进行此操作，已保证各表均分配了segment。
方法二
设置deferred_segment_creation 参数（已验证）

步骤1、修改deferred_segment_creation（默认为true）

alter system set deferred_segment_creation=false; 

步骤2、由于修改deferred_segment_creation并不会影响之前创建的表，仅会对之后创建的表生效，故需要对之前创建的表进行统一的分配segment处理。

1) 生成为空表分配segment的命令

select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0  ;

2) 执行上诉查询结果

alter table table_name allocate extent;  

上述操作完成之后就可以使用exp命令进行备份导出了。

注意事项

笔者有语

可以使用expdp和impdp命令进行oracle数据库备份还原，此命令可以导出空表，具体用法，已不属于本文范畴。