ORACLE 导出空表和将表导入到指定表空间的解决方案

1、ORACLE 导出空表和将表导入到指定表空间解决方案背景Oracle 11g 使用 exp 时，默认无法导出空表； 导入时也无法指定表空间。导出空表的解决方法查看是否能导出空表show parameter deferred_segment_creationdeferred_segment_creation=TRUE 只有在这个参数设置为FALSE 后新建的表才能导出。alter system set deferred_segment_creation=false;解决方法给空表增加一条记录，然后再删除，然后就能导出了。导出表Exp xxx/xxxdbsid file=xxx.dmp owner

2、=(xxx,xxx);然后将xxx.dmp导入一个临时的用户A 中，将临时用户A sys登录，运行selectobject_name,CREATED,STATUSfromdba_objectswhereowner=XXXOBJECT_TYPE=TABLE andOBJECT_NAME NOT IN(select OBJECT_NAME from dba_objects where owner=A) ORDER BY OBJECT_NAME禁用所有外键约束AND运行 SQL,生成禁用所有外键的 SQL 预计selectaltertable|table_name|disableconstraint

3、|constraint_name|;from user_constraintsconstraint_type=R然后将结果复制下来运行即可。使用POWERDESIGN等工具生成测试数据或者手工生成测试数据使用 IMP 命令生成这些空表的创建语句Imp xxx/xxxdbsid show=y tables=(xxx,xxx) log=xxx.logxxx.log xxx.sql powerdesign powerdesign 的生成测试数据生成测试数据testdata.sql，生成测试数据前要先设置自动填充的值的模式，否则会生成很长的字符串。运行测试数据，然后删除测试数据，空表就会有数据段恢复外

4、键约束运行 SQL,生成启动所有外键的 SQL 预计select alter table |table_name| enable constraint |constraint_name|; from user_constraints where constraint_type=R然后将结果复制下来运行即可。导出所有表，这次导出的文件就包含了空表了将数据导出到指定表空间导出数据，最好指定导出某个用户的数据exp userid/pwddbsid file=xxx.dmp owner=(xxx ,xxx)创建表空间create tablespace XXXX nologgingdatafile +D

5、ATA/webdb/datafile/XXX.dbf size 1024mautoextend on next 100m maxsize 30810mextent management local解释：nologging 表示不用创建日志，由于新创建的表空间，无需日志+DATA/webdb/datafile/XXX.dbf 这个是存储设备路径授权用户使用该表空间alter user XXX quota unlimited on XXXX;oracle导出文件xxx.dmp中的表空间Vim xxx.dmp%s/TABLESPACE XXX/XXXX/g导入Imp xxx/xxxdbsid fro

6、muser=xxx touser=xxx检查表空间的表以用户身份登录select tablespace_name from user_tables where table_name = tabname查询空表的另外一种方法SET SERVEROUTPUT ON;exec dbms_output.enable(200000);DECLAREv_table dba_objects.object_name%TYPE; v_sql VARCHAR2(888);v_q NUMBER; CURSOR c1 ISSELECT object_name tn FROM dba_objects where own

7、er=xxxx and object_type=TABLE; TYPE c IS REF CURSOR;c2 c; BEGINDBMS_OUTPUT.PUT_LINE(empty table:);FOR r1 IN c1LOOPv_table :=r1.tn;v_sql :=SELECT count(*) q FROM xxx.|v_table| where rownum = 1; OPEN c2 FOR v_sql;LOOPFETCH c2 INTO v_q;EXIT WHEN c2%NOTFOUND; IF v_q=0 THENDBMS_OUTPUT.PUT_LINE(v_table);

8、END IF;END LOOP; CLOSE c2;END LOOP; EXCEPTIONWHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(Error occurred); END;/解析Xxx 代表用户名。需要用DBA 角色登录。修改数据库字符集重建数据库，再导入数据库数据的方式来转换，或通过ALTER DATABASE CHARACTER SET 语句修改字符集，但创建数据库后修改字符集是有限制的，只有新的字符集是当前字符UTF8US7ASCII 用ALTER DATABASE CHARACTER SET 。在 Export 过程中，如果源数据库字符集与Expor

9、t Export 用户会话字符集的ID 例:如果源数据库使用ZHS16GBK，而Export 用户会话字符集使用US7ASCII，由于ZHS16GBK 是16位字符集,而 US7ASCII 是7位字符集，这个转换过程中，中文字符在US7ASCII 中不能够找到对等的字符，所以所有中文字符都会丢失而变成“? ”形式，这样转换后生成的Dmp 文件已经发生了数据丢失。因此如果想正确导出源数据库数据，则Export 过程中用户会话字符集应等于源数据库字符集或是源数据库字符集的超集查看数据字符集Select * from v$NLS_PARAMETERS;Select * from nls_databa

10、se_parameters;查看 sessionSelect * from nls_session_parameters;客户端字符集设置NLS_LANG 参数格式NLS_LANG=_. Language:显示oracle 消息,校验，日期命名 character 例如：NLS_LANG=AMERICAN_AMERICA.US7ASCIIAMERICAN 是语言，AMERICA 是地区，US7ASCII 客户端字符集设置方法$NLS_LANG=“simplified chinese”_china.zhs16gbk$export NLS_LANG编辑oracle 用户的profile 文件导入数据设置字符集和目标数据库字符集NLS_LANG=AMERICAN_AMERICA.ZHS16GBKexport NLS_LANGZHS16GBK 到