Oracle 11g 导出/导入空表的方法

当前位置：首页 > Oracle

来源：靑龍一笑的博客作者：靑龍一笑发布时间：2014-08-24 13:34:54 点击量：1713 评论：0

一般情况下，Oracle 数据库使用 exp 和 imp 命令来导出和导入数据。

exp system/manager owner=BLOG file=blog.dmp
imp system/manager fromuser=BLOG touser=BLOG file=D:\blog.dmp ignore=y

但是，在 Oracle 数据库中，当表无数据时，默认是不分配 segment，以节省空间。因此，exp 不能导出空表。
如果在 Oracle 安装时，就设置了 deferred_segment_creation 参数，即：

alter system set deferred_segment_creation=false;

那么无论是空表还是非空表，都会分配 segment，这样就可以导出空表。
如果在导出之前没有设置这个参数，那么，可以在空表中插入数据，再删除，则会产生 segment，这样也可以导出空表。
对于空表比较多的情况，事先又没有设置 deferred_segment_creation 参数，那么有两种方法：
方法一、使用 expdp 导出、impdp 导入
1、创建导出目录

[root@RicenOS ~]$ mkdir expdir
[root@RicenOS ~]$ sqlplus "/as sysdba"
SQL> create or replace directory expdir as '/home/oracle/expdir';
SQL> grant read, write on directory expdir to public;

2、使用 expdp 导出数据

[root@RicenOS ~]$ expdp system/manager schemas=BLOG dumpfile=blog.dmp directory=expdir

3、创建导入目录

D:\>md impdir
D:\>sqlplus "/as sysdba"
SQL> create directory impdir as 'D:\impdir';
SQL> grant read, write on directory impdir to public;

4、将导出的数据放到导入目录里
5、使用 impdp 导入数据

impdp system/manager dumpfile=blog.dmp directory=impdir remap_schema=BLOG:BLOG

方法二、写一个存储过程，将空表找出来并执行分配空间，代码如下：

DECLARE
v_table tabs.table_name%TYPE;
v_sql VARCHAR2(888);
v_q NUMBER;
v_alter VARCHAR2(1000);
CURSOR c1 IS
SELECT table_name tn FROM tabs;
TYPE c IS REF CURSOR;
c2 c;
BEGIN
DBMS_OUTPUT.PUT_LINE('以下为空数据表的表名:');
FOR r1 IN c1 LOOP
v_table := r1.tn;
v_sql := 'SELECT COUNT(*) q FROM ' || v_table;
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);
v_alter := 'alter table ' || v_table || ' allocate extent';
DBMS_OUTPUT.PUT_LINE(v_alter);
EXECUTE IMMEDIATE v_alter;
END IF;
END LOOP;
CLOSE c2;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error occurred');
END;
/

此时，使用 exp、imp 命令就可以导出和导入空表了。