Oracle exp导出数据库表，表结构，表数据不全问题

原因： 在oracle11g有个特征，如果某个表从来没有被使用过，则数据库系统不会给这个表分配段，只在数据字典中保存其结构。这样，从来没有被使用过的表使用exp命令就不会被导出。

说明：设置deferred_segment_creation 参数为FALSE后，无论是空表还是非空表，都分配segment。该值设置后只对后面新增的表产生作用，对之前建立的空表不起作用。

解决办法：

1.解锁统计表

在执行 analyze table tablename compute statistics 时，oracle会报  object statictis are locked (这些表的统计被锁了),通过下面的方式解锁

拼凑解锁执行语句：select 'exec dbms_stats.unlock_table_stats('||'''JXDEMO'''||','''||table_name||''');' from user_tables 

在SQLPLUS：执行拼凑出来的SQL语句;

（得到的结果需要在sqlplus上执行，在pl/sql developer 中的sql窗口执行报错）

2.手动更新统计表信息

exp导出数据库表时，需要参照数据库表统计信息，数据库统计数据可能不是最新的，需要我们手动更新，涉及的数据库表如下，统计信息包括：行，列，索引等。analyze table tablename compute statistics  等同于 analyze table tablename compute statistics for table for all indexes for all columns。

for table的统计信息存在于视图：user_tables 、all_tables、dba_tables
for all indexes的统计信息存在于视图： user_indexes 、all_indexes、dba_indexes
for all columns的统计信息存在于试图：user_tab_columns、all_tab_columns、dba_tab_columns

拼凑统计表执行语句：select 'analyze table '||table_name||' compute statistics;' from user_tables

在SQLPLUS中：执行拼凑出来的SQL语句；

执行完后，视图user_tables中的num_rows值等会更新。

3.为空表分配区

拼凑执行分区SQL语句：

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

在SQLPLUS中：执行拼凑出来的SQL语句；

4. 具体实施步骤:

注意：exp file参数文件名称区分大小写，可能报错：IMP-00002: failed to open teas_ivf_exp.dmp for read源数据库

1）授权

grant  connect,resource,dba to jinsb;

alter user jinsb identified by 1234;

conn jinsb/1234

2）解锁统计表

spool unlock_jinsb.sql；

select 'exec dbms_stats.unlock_table_stats('||'''JINSB'''||','''||table_name||''');' from user_tables;

spool off;

@unlock_jinsb.sql

3）分析表

spool analyze_jinsb.sql

select 'analyze table '||table_name||' compute statistics;' from user_tables;

spool off;

@analyze_jinsb.sql

4）验证表行数

select table_name,num_rows,tablespace_name from user_tables where table_name='EDC_ENROLL';

select count(*) from EDC_ENROLL;

5）为空表分配区

spool extent_jinsb.sql;

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

spool off;

@extent_jinsb.sql;

6）导出schema
 

exp jinsb/1234 file=jinsb_exp.dmp log=jinsb_exp.log

 

ssh传输导出文件到新服务器：

scp [email protected]:/home/oracle/jinsb_exp.dmp ./

scp [email protected]:/home/oracle/jinsb_exp.log ./

scp jinsb_exp.dmp [email protected]:/home/oracle

scp jinsb_exp.log [email protected]:/home/oracle

目标数据库

1）创建用户分配权限

create user jinsb identified by 1234;

grant  connect,resource,dba to jinsb;

2）导入数据到schema

imp jinsb/1234 file=jinsb_exp.dmp fromuser=jinsb touser=jinsb

可能遇到的批量操作命令：

批量复制文件：scp -r [email protected]:/home/oracle/schema_dmp_and_log_file/*.dmp  ./apex_dmp_file

                           scp -r ./apex_dmp_file/*.dmp [email protected]:/home/oracle/apex_bk1/

批量赋权：grant connect,resource,dba to teas_lc,teas_ivf,beidaebhou,YOUMEI,FXH,SJJGH

 

原文链接