表扩展失败(ORA-01653)后的空间管理问题【THE SPACE MANAGEMENT PROBLEM OF THE TABLE EXTEND FAILD 】-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

表扩展失败(ORA-01653)后的空间管理问题【THE SPACE MANAGEMENT PROBLEM OF THE TABLE EXTEND FAILD 】

简介:
 这两天在公司做ORACLE10G的DATAGUARD测试的时候,发现表扩展报错后,后续的一些空间问题。
    测试环境中,建了一个500M的TABLESPACE命名为TEST。
    导入一张表(TEST_ALL)有280多万的数据,然后建立了有建立了一个同样结构的表TEST1,进行批量插入操作。
   
SQL> insert into test1 select * from test_all;

ORA-01653: unable to extend table TEST.TEST1 by 1024 in tablespace TEST
   
   这是个较为常见的错误,就是TABLESPACE没有空间了。以下为ORACLE给出的错误解释和解决方法。

10635, 00000, "Invalid segment or tablespace type"
// *Cause: Cannot shrink the segment because it is not in auto segment space 
//       managed tablespace or it is not a data, index or lob segment.
// *Action: Check the tablespace and segment type and reissue the statement
 
   因为我的表空间没有设置自动增长,所以会有错误报出。但是接下来的问题就很有意思了。我们发现虽然插入操作失败,但是其所占的空间并没有自动收回!我的测试环境中,TEST1表一直占用184M左右的表空间。并且无论我发ROLLBACK,COMMIT,关闭当前SESSION甚至重新启动数据库,这个TEST1表一直会占用着184M的表空间!
   我只能手工TRUNCATE TABLE或者MOVE的方法来消减这张表占用的空间。
   有意思的是,当我利用ORACLE10G的新方法来试图回缩所占用的空间时,ORACLE报错并且错的让我“找不到北”。如下:
  
SQL> alter table test1 shrink space cascade;

alter table test1 shrink space cascade

ORA-10635: Invalid segment or tablespace type
   
   这个10635错误也是一个比较典型的错误,ORACLE给出的官方解释:
  
10635, 00000, "Invalid segment or tablespace type"
// *Cause: Cannot shrink the segment because it is not in auto segment space 
//         managed tablespace or it is not a data, index or lob segment.
// *Action: Check the tablespace and segment type and reissue the statement
     
    当时我以为自己没有使用ORACLE的ASSM,但是经过确认自己建表空间的SQL,发现我使用的是ASSM的,但还是害怕有问题,对另外一张TEST_ALL的表进行操作,结果正常。
   
  
SQL> alter table test.test_all shrink space cascade;

Table altered

Executed in 10.859 seconds
    
      看上去ORACLE报的这个错误也“驴唇不对马嘴”了。让我已经久已不用的MOVE命令关键时刻还是有点作用的。
      这占用的表空间只是会造成空间的浪费吗,还是有其他副作用的。我先后做了两个COUNT的SQL,其显示的执行结果比较清楚的说明了这个问题。
    第一个COUNT的SQL是没有执行TRUNCATE TABLE命令前的,如下

SQL> select count(*) from test1;

  COUNT(*)
----------
         0


Execution Plan
----------------------------------------------------------
Plan hash value: 3896847026

--------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |  5135   (1)| 00:01:02 |
|   1 |  SORT AGGREGATE    |       |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST1 |     1 |  5135   (1)| 00:01:02 |
--------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          6  recursive calls
          2  db block gets
      23641  consistent gets
          0  physical reads
        228  redo size
        410  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

      第二个COUNT的SQL是在执行MOVE TABLE后来完成的
SQL> select /*truncate*/count(*) from test1;

  COUNT(*)
----------
         0


Execution Plan
----------------------------------------------------------
Plan hash value: 3896847026

--------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |       |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST1 |     1 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        410  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

      第二句要明显好于第一句。性能上还有有差别的。即TEST1表的HWM是上升的。
      接下来另外一个问题就出现了:如果我可以继续插入的话,空间占用会持续增加吗即HWM会持续增加吗?如果是的话,这样导致的性能问题就大了。然后我正常插入了32000条数据,空间占用没有变化。为了防止是因为空间限制造成空间占用不增加,我调大了表空间到600M,结果相同。即空间占用仍为184M。还好,ORACLE没有犯错误。现在比较两种情况。
    情况一:在不回缩空间占用的情况下,执行COUNT(*)
  
SQL> SELECT /*320000*/ COUNT(*) FROM TEST1 WHERE OWNER='SYS';

  COUNT(*)
----------
    140216


Execution Plan
----------------------------------------------------------
Plan hash value: 3896847026

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |    17 |  5144   (1)| 00:01:02 |
|   1 |  SORT AGGREGATE    |       |     1 |    17 |            |          |
|*  2 |   TABLE ACCESS FULL| TEST1 |   158K|  2628K|  5144   (1)| 00:01:02 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OWNER"='SYS')

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          6  recursive calls
          1  db block gets
      23639  consistent gets
          0  physical reads
        176  redo size
        413  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
    
      第二种情况,先进行空间收缩,然后进行COUNT查询。这次使用SHRINK操作,ORACLE没有报错。回缩后,TEST1表空间占用31.8M
  
SQL> SELECT /*shrinked*/ count(*) from test1 where owner='SYS';

  COUNT(*)
----------
    140216


Execution Plan
----------------------------------------------------------
Plan hash value: 3896847026

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |    17 |   890   (2)| 00:00:11 |
|   1 |  SORT AGGREGATE    |       |     1 |    17 |            |          |
|*  2 |   TABLE ACCESS FULL| TEST1 |   137K|  2288K|   890   (2)| 00:00:11 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OWNER"='SYS')

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       4008  consistent gets
          0  physical reads
          0  redo size
        413  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

      不难发现性能上,第二句还是有非常大的优势的。
   
      综上所述
      如果我们在操作(INSERT)某个表,因为空间扩张失败后,需要认真执行空间的回缩处理。虽然在我们看来,失败了回滚即可。但是其HWM却上去后就没有下来。也许这是ORACLE在并发处理情况下要求快速相应的一种策略。但是也会留下性能忧患的伏笔。周期性的执行空间管理还是非常非常有必要的。
      但是对于ORACLE的SHRINK操作,只有对批量INSERT某个空表而造成空间扩展失败后的表进行回缩时报ORA-10635错来看,这应该是个BUG。(因为如果直接对某个空表或有原表中有些数据然后进行INSERT操作造成空间扩展失败的表执行SHRINK操作都没有问题)
   
    要多想些,再多想些 -:)

    --------------------系统环境
    OS: REDHAT AS4 U5
    ORACLE:10.2.0.1
    

    欢迎指正。

本文转自Be the miracle!博客51CTO博客,原文链接http://blog.51cto.com/miracle/58265如需转载请自行联系原作者


Larry.Yue

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章
最新文章
相关文章