当前位置: 首页 > Oracle, oracle 11g > 正文

ORACLE 11gR2新特性延迟段创建导致EXP导不出空表

很多数据库都有存在空表的情况,较多的空表会占用大量的磁盘空间,ORACLE 在11gR2版本推出延迟段创建新特性,所谓延迟段创建,顾名思义就是在创建一张新空表的时候,ORACLE默认不会为这张空表分配段(SEGMENTS),也就是不会为这张空表分配空间,这样就避免了空表占用空间的情况,如下实验:

SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> CREATE TABLE T_TEST_1(ID NUMBER,NAME VARCHAR2(10));

表已创建。
SQL> SELECT SEGMENT_NAME FROM USER_SEGMENTS WHERE SEGMENT_NAME='T_TEST_1';

未选定行

默认情况下ORACLE没有为空表(T_TEST_1)分配空间,如果查看过ORACLE11gR2官方文档关于CREATE TABLE语法的人可能会看到SEGEMENT CREATION信息,如下:

当SEGEMENT CREATION为IMMEDIATE的情况下,ORACLE在建表的时候,会为表建立段(SEGMENTS),当SEGEMENT CREATION为DEFERRED的情况下,ORACLE不会为空表建立段,下面分别演示下这两种情况的效果。

SQL> CREATE TABLE T_TEST_2(ID NUMBER,NAME VARCHAR2(10))
2  SEGMENT CREATION IMMEDIATE;

表已创建。
SQL> CREATE TABLE T_TEST_3(ID NUMBER,NAME VARCHAR2(10))
  2  SEGMENT CREATION DEFERRED;

表已创建。
SQL> SELECT SEGMENT_NAME FROM USER_SEGMENTS WHERE SEGMENT_NAME LIKE'T_TEST%';

SEGMENT_NAME
-------------
T_TEST_2

可以看到,在SEGEMENT CREATION为IMMEDIATE的情况下,ORACLE为T_TEST_2建立了段,在SEGEMENT CREATION为DEFERRED的情况下,ORACLE没有为表T_TEST_3建立段,当向没有分配段的空表中插入信息时,ORACLE会自动为空表建立段。

SQL> INSERT INTO T_TEST_1 VALUES(1,'STREAM');

已创建 1 行。
SQL> SELECT SEGMENT_NAME FROM USER_SEGMENTS WHERE SEGMENT_NAME LIKE'T_TEST%';

SEGMENT_NAME
------------
T_TEST_1
T_TEST_2

也可以用ALLOCATE EXTENT的方式来为空表建立段信息。

SQL> ALTER TABLE T_TEST_3 ALLOCATE EXTENT;

表已更改。
SQL> SELECT SEGMENT_NAME FROM USER_SEGMENTS WHERE SEGMENT_NAME LIKE'T_TEST%';

SEGMENT_NAME
------------
T_TEST_1
T_TEST_2
T_TEST_3

虽然延迟段创建避免了空表占用空间的问题,但是也为DBA带点小麻烦,这就是在EXP导出数据的时候,虽然空表的信息也存在数据库字典内,但是ORACLE不会导出未分配段的空表,这样在使用EXP做数据迁移的时候,就会遇到点小问题。

SQL> CREATE USER dbdream IDENTIFIED BY dbdream DEFAULT TABLESPACE USERS;

用户已创建。
SQL> GRANT CONNECT,RESOURCE TO DBDREAM;

授权成功。
SQL> CREATE TABLE T_TEST_1(ID NUMBER,NAME VARCHAR2(10));

表已创建。
SQL> CREATE TABLE T_TEST_2(ID NUMBER,NAME VARCHAR2(10))
2  SEGMENT CREATION IMMEDIATE;

表已创建。
D: >exp dbdream/dbdream file=d:dbdream.dmp

Export: Release 11.2.0.1.0 - Production on 星期一 2月 13 11:35:22 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
即将导出指定的用户...
...
. . 正在导出表                        T_TEST_2导出了           0 行
...
成功终止导出, 没有出现警告。

EXP只能导出已经分配段的表,要导出未分配段的空表EXP就无能为力了,要导出未分配段的空表就需要使用数据泵(EXPDP),使用EXPDP可以导出未分配段的空表。

SQL> CREATE DIRECTORY D_TEST AS 'D:T_TEST';

目录已创建。
SQL> GRANT READ,WRITE ON DIRECTORY D_TEST TO DBDREAM;

授权成功。
D: >expdp dbdream/dbdream directory=D_TEST dumpfile=dbdream.dmp

Export: Release 11.2.0.1.0 - Production on 星期一 2月 13 11:50:00 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
正在使用 BLOCKS 方法进行估计...
...
. . 导出了 "DES"."T_TEST_1"                                0 KB       0 行
. . 导出了 "DES"."T_TEST_2"                                0 KB       0 行
...
作业 "DES"."SYS_EXPORT_SCHEMA_01" 已于 11:50:47 成功完成

如果非要用EXP做迁移,而且所有空表也都需要迁移,那么就需要使用上文提到的利于ALLOCATE EXTENT创建段的方法。在做EXP操作之前,先使用ALLOCATE EXTENT的方法为空表分配段信息。

SQL> DECLARE
  2  V_COUNT NUMBER;
  3  BEGIN
  4  FOR I IN (SELECT TABLE_NAME FROM USER_TABLES) LOOP
  5  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || I.TABLE_NAME INTO V_COUNT;
  6  IF V_COUNT = 0 THEN
  7  EXECUTE IMMEDIATE 'ALTER TABLE ' || I.TABLE_NAME || ' ALLOCATE EXTENT';
  8  END IF;
  9  END LOOP;
 10  END;
 11  /

PL/SQL 过程已成功完成。
SQL> SELECT SEGMENT_NAME FROM USER_SEGMENTS WHERE SEGMENT_NAME LIKE'T_TEST%';

SEGMENT_NAME
------------
T_TEST_1
T_TEST_2

然后在用EXP导出数据,这样空表就可以被导出了。

D: >exp dbdream/dbdream file=d:dbdream.dmp
Export: Release 11.2.0.1.0 - Production on 星期一 2月 13 11:58:03 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
即将导出指定的用户...
...
. . 正在导出表                          T_TEST导出了           0 行
. . 正在导出表                        T_TEST_2导出了           0 行
...
成功终止导出, 没有出现警告。

本文固定链接: http://www.dbdream.com.cn/2012/02/oracle-11gr2%e6%96%b0%e7%89%b9%e6%80%a7%e5%bb%b6%e8%bf%9f%e6%ae%b5%e5%88%9b%e5%bb%ba%e5%af%bc%e8%87%b4exp%e5%af%bc%e4%b8%8d%e5%87%ba%e7%a9%ba%e8%a1%a8/ | 信春哥,系统稳,闭眼上线不回滚!

该日志由 dbdream 于2012年02月13日发表在 Oracle, oracle 11g 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: ORACLE 11gR2新特性延迟段创建导致EXP导不出空表 | 信春哥,系统稳,闭眼上线不回滚!
关键字:

ORACLE 11gR2新特性延迟段创建导致EXP导不出空表:等您坐沙发呢!

发表评论

快捷键:Ctrl+Enter