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. 具体实施步骤:

原服务器及数据库操作：

1）设置源数据库操作系统字符集参数与数据库保持一致

SQL> select userenv('language') from dual;
 
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.AL32UTF8

vi .bash_profile

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

2）授权

grant  connect,resource,dba to jinsb;

alter user jinsb identified by 1234;

conn jinsb/1234

3）解锁统计表

spool unlock_jinsb.sql；

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

spool off;

@unlock_jinsb.sql

4）分析表

spool analyze_jinsb.sql

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

spool off;

@analyze_jinsb.sql

5）验证表行数

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

select count(*) from EDC_ENROLL;

6）为空表分配区

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;

7）导出schema
 

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

 

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

scp root@192.168.1.192:/home/oracle/jinsb_exp.dmp ./

scp root@192.168.1.192:/home/oracle/jinsb_exp.log ./

scp jinsb_exp.dmp root@39.98.222.21:/home/oracle

scp jinsb_exp.log root@39.98.222.21:/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 root@192.168.1.192:/home/oracle/schema_dmp_and_log_file/*.dmp  ./apex_dmp_file

scp -r ./apex_dmp_file/*.dmp root@39.98.222.21:/home/oracle/apex_bk1/

批量赋权：

grant connect,resource,dba to teas_lc,teas_ivf,beidaebhou,YOUMEI,FXH,SJJGH

 

原文链接