ORACLE 导出空表和将表导入到指定表空间

背景
Oracle 11g使用exp时，默认无法导出空表；
导入时也无法指定表空间。

导出空表的解决方法
查看是否能导出空表
show parameter deferred_segment_creation
deferred_segment_creation=TRUE表示空表不创建数据段，这是就不能导出表了。
只有在这个参数设置为FALSE后新建的表才能导出。


解决方法
给空表增加一条记录，然后再删除，然后就能导出了。
 导出表
Exp xxx/xxx@dbsid file=xxx.dmp owner=(xxx,xxx);
 然后将xxx.dmp导入一个临时的用户A中，
 将临时用户A的表和原用户表比较，查询所有空表，
用sys登录，运行
select object_name,CREATED,STATUS from dba_objects where owner='XXX'  AND OBJECT_TYPE='TABLE' and OBJECT_NAME NOT IN
(select OBJECT_NAME from dba_objects where owner='A'  ) ORDER BY OBJECT_NAME

 禁用所有外键约束
运行SQL,生成禁用所有外键的SQL预计
select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where constraint_type='R'
然后将结果复制下来运行即可。

 使用POWERDESIGN等工具生成测试数据或者手工生成测试数据
使用IMP命令生成这些空表的创建语句
Imp xxx/xxx@dbsid show=y tables=(xxx,xxx) log=xxx.log
然后将xxx.log改成xxx.sql导入到powerdesign中，再利用powerdesign的生成测试数据生成测试数据testdata.sql，生成测试数据前要先设置自动填充的值的模式，否则会生成很长的字符串。

 运行测试数据，然后删除测试数据，空表就会有数据段
 恢复外键约束
运行SQL,生成启动所有外键的SQL预计
select 'alter table '||table_name||' enable constraint '||constraint_name||';' from user_constraints where constraint_type='R'
然后将结果复制下来运行即可。
 导出所有表，这次导出的文件就包含了空表了




将数据导出到指定表空间

 导出数据，最好指定导出某个用户的数据
exp userid/pwd@dbsid file=xxx.dmp owner=(xxx ,xxx)

 创建表空间
create tablespace XXXX
nologging
datafile '+DATA/webdb/datafile/XXX.dbf'
size 1024m
autoextend on
next 100m
maxsize 30810m
extent 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/xxx@dbsid fromuser=xxx touser=xxx


 检查表空间的表
以用户身份登录
select tablespace_name from user_tables where table_name = 'tabname'


查询空表的另外一种方法
SET SERVEROUTPUT ON;
exec dbms_output.enable(200000);

DECLARE
v_table dba_objects.object_name%TYPE;
v_sql VARCHAR2(888);
v_q NUMBER;
CURSOR c1 IS
SELECT object_name tn FROM dba_objects where owner='xxxx' and object_type='TABLE';
TYPE c IS REF CURSOR;
c2 c;
BEGIN
    DBMS_OUTPUT.PUT_LINE('empty table:');
    FOR r1 IN c1 LOOP
        v_table :=r1.tn;
        v_sql :='SELECT count(*) q FROM xxx.'||v_table||' where rownum = 1';
        OPEN c2 FOR v_sql;
        LOOP
            FETCH c2 INTO v_q;
                EXIT WHEN c2%NOTFOUND;
                IF v_q=0 THEN
            DBMS_OUTPUT.PUT_LINE(v_table);
                END IF;
        END LOOP;
        CLOSE c2;
    END LOOP;
    EXCEPTION
    WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error occurred');
END;
/

解析
Xxx代表用户名。
需要用DBA角色登录。