当前位置:首页 > Oracle > 正文内容

在Oracle11g及以上版本exp出空表的方法总结

管理员3年前 (2018-07-11)Oracle901

exp/imp这组导入导出工具,在早期的版本中就提供了,从自10g提供expdp/impdp之后,官方就停止了对前者的支持,但是为了保持版本的兼容,直到12c这个版本,还是提供了exp/imp这一组工具。

如果我们在早期的版本中习惯使用这组工具,会自然的在后续发行的版本中,继续延用它,因为这实在是太方便了,尤其是做一些小数据量的测试环境搭建时。但是,有时候我们会发现,导入的数据并少一些表,并且更多的时候,不知道丢失的那些表是什么用途,也不知道哪些业务模块会用到,这个情况下,我们可能会重新导一次,但是结果还是会少那些表,实际上,丢失的这些表,在导出时,就根本没有被导出来,而这些表就是”空表”,通过SELECT COUNT(*)来查询这些表,得到的结果一定是0,但是结果是0的表,并不一定没有被导出,所以这里提到的“空表”,实际上未分配空间的表。

通过如下语句,可以知道哪些表是空表,其中SEGMENT_CREATED表示是否已分配了空间

SQL> 
SQL> select table_name, segment_created
  2    from user_tables
  3   where segment_created = 'NO';
TABLE_NAME                     SEGMENT_CREATED
-------------------------- ---------------
AA_TITLE                       NO
………………
AC_INFO                        NO
44 rows selected

SQL>

造成上述问题的原因,我们可以在官方网站的新特性文档看到,也可以通过搜索引擎查到一些相关的技术文章。

从Oracle 11g开始,增加了一个 deferred_segment_creation 系统参数,它的默认值是true,从字面上我们可以猜测到,这是“延时创建段”的意思,实际的意义也是如此,意思就是当创建一个对象(表)时,是否马上给该表分配一定的空间,值为true时,表示不会马上分配空间,只会在有数据写入时,才会进行分配。

要解决这个问题,有两个方面需要考虑:一是对未来要创建的表,二是已经存在的表。下面我们分别给出解决的办法。

未来要创建的表,有三个办法来处理:

1、 全局修改上面所提到的参数:

SQL> show parameter deferred_segment_creation;
NAME                           TYPE        VALUE
------------------------- --------- ------------------------------
deferred_segment_creation  boolean     TRUE

SQL> alter system set deferred_segment_creation = false scope=both;
System altered

SQL> show parameter deferred_segment_creation;
NAME                           TYPE        VALUE
-------------------------- --------- -----------------------------
deferred_segment_creation  boolean     FALSE

SQL>

这个方法一劳永逸,但是需要有相应的权限才可以执行,它的影响是全局的,也就是说以后每个用户创建新表,都会使用这个新值,这也是推荐的方法。

2、 会话级别的修改上面提到的参数

SQL> alter session set deferred_segment_creation = false;
System altered
SQL> CREATE TABLE TEST(ID INT);
Table created

SQL>

这在 session 级别修改这个参数,只影响当前会话,其他的会话,包括已有的和新打开的,还是以原来的参数设置为准。

3、 在创建表时,增加相应选项:

SQL> CREATE TABLE TEST(ID INT) SEGMENT CREATION IMMEDIATE;
Table created

SQL>

每次创建新表,都要增加这个选项,在很多时候,都会执行一些已经整理好的SQL脚本,而其中的创建表的语句,并不包含这个选项,所以要配合2一起使用。

对已存在的表,也有两个办法自处理

1、 插入一行数,然后再回滚这个事务:

SQL> create table test(id int);
Table created
SQL> insert into test values(100);
1 row inserted
SQL> rollback;
Rollback complete

SQL>

这个操作就容易理解了,插入一行数据,数据库系统会为了存储这一行数据而去申请空间,然后执行回滚操作,表示这行数据我们对我们来说,并没有用途,注:只会回滚这刚刚insert的数据,不会回滚已分配的空间,这是一个隐含的动作。

如果有大量的表,或表上的非空字段太多,这个方法,很显然是一个慢速的方法,所以平时使用场景并不多见,也不推荐使用。

2、 手动分配一个空间

SQL> declare
  2      v_sql varchar(100);
  3  begin
  4      for c in (select table_name, segment_created
  5        from user_tables
  6       where segment_created = 'NO') loop
  7         v_sql := 'alter table ' || c.table_name || ' allocate extent';
  8         execute immediate v_sql;
  9      end loop;
 10  end;
 11  /
PL/SQL procedure successfully completed

SQL>

通过 alter table tname allocate extent 语句,可以直接给表tname分配一些空间,无论原来的表是否已分配了空间,并且可以使用上述代码块,批量的对当前用户下的所有“空表”进行处理。

打赏 支付宝打赏 微信打赏

    扫描二维码推送至手机访问。

    版权声明:本文由卖水果的net发布,如需转载请注明出处。

    转载请注明出处:http://www.msgde.net/oracle/oracle_segment_created.html

    分享给朋友:

    发表评论

    访客

    ◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。