oracle使用数据导出与导入

最近突然需要在本地也建立一个与远端的服务器上相同的数据空间，就将服务器上的数据表导出来，导入到本地的数据空中

下面总结一下在此操作中的问题遇到的问题

命令的执行是在命令窗口下，不是sql的命令窗口下

下面以Scott 用户为例：

 exp scott/tiger@orcl file=d:\scott_back owner=scott 
 imp scott/tiger@orcl file=d:\scott_back.dmp full=y; 

我是以用户为目的导出的数据表

下面是进行导出数据表的操作时，可以的选择

1 将数据库TEST完全导出,用户名system 密码manager 导出到D:\daochu.dmp中
  exp system/manager@TEST file=d:\daochu.dmp full=y
2 将数据库中system用户与sys用户的表导出
  exp system/manager@TEST file=d:\daochu.dmp owner=(system,sys)
3 将数据库中的表table1 、table2导出
  exp system/manager@TEST file=d:\daochu.dmp tables=(table1,table2) 

找到导出的数据文件dmp文件之后，就是将数据文件导入到自己的本地的空间中

 imp scott/tiger@orcl file=d:\scott_back.dmp full=y; 

下面是查找到的Imp的用法

1. 导入一个完整数据库

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

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

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

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

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

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)

4. 导入一个表

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

5. 从多个文件导入

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

在此根据实际的情况进行导入 （如果经常出错，建议使用 ignore=y）

在进行导入时要确保自己的用户的权限，一般是读写的权限，具体看控制台的报错情况进行相应的权限grant

在此一般用户基本是可以的不会存在大的问题

但是在oracel11g上遇到了和原来的数据库的数据表缺少的问题

在网上查询得知是有些空的数据表是不进行导出的 

但是表的结构我们也想要这样就需要如下的操作：

一、不能导出空表的原因

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

2、设置deferred_segment_creation 参数为FALSE后，无论是空表还是非空表，都分配segment。

在sqlplus中，执行如下命令：

SQL>alter system set deferred_segment_creation=false;

查看：

SQL>show parameter deferred_segment_creation;

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

二、解决办法

可以使用手工为空表分配Extent的方式，来解决导出之前建立的空表的问题

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

使用上面的语句 会拼接处给空表分配Extent的语句

但是，有很多空表 在视图user_tables中的num_rows不等于0（原因：表中以前有数据，删除后oracle没有统计，视图user_tables中的数据没有更新），所以通过上面的方法并不能为所有的空表分配数据段，解决方法如下

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

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

执行完后，视图user_tables中的num_rows值会更新，此时再执行①，能够给所有的空表分配数据段

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

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

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

③执行后得到的结果执行完了之后再去执行②就没有object statictis are locked错误提示了

这样可以解决关于导出空的问题。

“只有DBA才能导入由其他DBA导出的文件”各种解决办法

当oracle导入的时候出现“只有 DBA 才能导入由其他 DBA 导出的文件”的时候通常有以下几种解决办法！

1：常见的是直接grant  dba to youuser到导入用户即可

 2：利用sysdba账户登录导入，但是要指定fromuser 和touser，因为默认是导入到登录用户中去（不可能导入的sysdba账户吧。。）

        此处注意转换（\'）的语法：imp \'sys/oracle11g as sysdba \' file=/home/oracle/20130906.dmp fromuser=yccw05 touser=yccwtest

3：利用date_only方式导入，进入oracle控制台（如果ssh登录数据库服务器的话 或者 本机cmd控制台）后依次输入命令来导入

      1）imp   （回车）

      2）sys  as sysdba  （此时让你输入sysdba的用户名）

      3）oracle11g  （输入口令）

      4）yes  （仅导入数据 (yes/no): no > yes）

      5）服务器：/home/oracle/20130906.dmp，本机：c:\20130906.dmp   （导入文件expdat.dmp >）

      6）20000   （输入插入缓冲区大小-最小为8192   ，任意输一个大于8192的数字即可）

      5）no   （只列出导入文件的内容 (yes/no): no  ，如果输入yes后面可能会报“ 参数 "SHOW" 在 data_only 模式下无效”）

      6）yes （由于对象已存在，忽略创建错误(yes/no): yes  ，导入之前最好删掉用户然后重新分配表空间和权限这样是最好的也不会提示这项，我习惯直接删除view/seq/table/produre/indexes/function等。省的分配空间。。。 ）

补充一下：

增量导出包括三种类型： 
　　（１）、“完全”增量导出（Complete） 
　　即备份三个数据库，比如： 
　　exp system/manager inctype=complete file=040731.dmp 
　　（２）、“增量型”增量导出 
　　备份上一次备份后改变的数据，比如： 
　　exp system/manager inctype=incremental file=040731.dmp 
　　（３）、“累积型”增量导出 
　　累计型导出方式是导出自上次“完全”导出之后数据库中变化了的信息。比如： 
　　exp system/manager inctype=cumulative file=040731.dmp 
　　数据库管理员可以排定一个备份日程表，用数据导出的三个不同方式合理高效的完成。