解决oracle11g空表导不出来

 

方法一:

Select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0; 

在plsql执行上面查询语句 

将查询结果复制出来在plsql执行就可以了

 

 方法二:

--导出前执行这个

DECLARE
  I INTEGER;
  B VARCHAR2(200);
BEGIN
  FOR C IN (select 'alter table '||table_name||' allocate extent' as txt
            from user_tables A where A.TEMPORARY='N' AND (A.NUM_ROWS = 0 OR A.NUM_ROWS IS NULL)
            AND NOT EXISTS (SELECT 1 FROM USER_EXTENTS B
                           WHERE B.SEGMENT_NAME=A.table_name
                           AND B.SEGMENT_TYPE LIKE 'TABLE%')
            ) LOOP
    DBMS_OUTPUT.PUT_LINE(C.txt);
    EXECUTE IMMEDIATE C.txt;
  END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_line(SQLERRM);
END;
/