ORA-1653 unable to extend table xxx by 128 in tablespace xxxxx

fengbao

update一个表的一行记录，报错：ORA-1653 unable to extend table xxx by 128 in  tablespace xxxxx
    表空间还有80G的free，initial extent 是250M，next extent是 1m ，pct_increase 为空。LOCAL管理，10.2.0.5，dba_free_space的最大bytes是64Mb
如果是正常的next extent 1m的话，应该是有足够的空间的，我现在怀疑是行迁移导致的 扩展了新的extent的大小是按照initial extent，所以导致的报错
update是逐行更新的，并不是每一次都报错


行迁移是会新分配extent还是在原有的extent上面增加block呢？

Liu Maclean(刘相兵

alter system set events '1653 trace name errorstack level 3';

触发你的错误

oradebug setmypid
oradebug tracefile_name;


上传TRACE

Liu Maclean(刘相兵

给出具体update的语句

给出下面的查询：

1. REM tablespace report
   
2. 
   
3. 
   
4. set linesize 200
   
5. 
   
6. 
   
7. select a.tablespace_name,
   
8.        round(a.bytes_alloc / 1024 / 1024) megs_alloc,
   
9.        round(nvl(b.bytes_free, 0) / 1024 / 1024) megs_free,
   
10.        round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) megs_used,
    
11.        round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_Free,
    
12.        100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_used,
    
13.        round(maxbytes / 1048576) Max
    
14.   from (select f.tablespace_name,
    
15.                sum(f.bytes) bytes_alloc,
    
16.                sum(decode(f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes)) maxbytes
    
17.           from dba_data_files f
    
18.          group by tablespace_name) a,
    
19.        (select f.tablespace_name, sum(f.bytes) bytes_free
    
20.           from dba_free_space f
    
21.          group by tablespace_name) b
    
22. where a.tablespace_name = b.tablespace_name(+)
    
23. union all
    
24. select h.tablespace_name,
    
25.        round(sum(h.bytes_free + h.bytes_used) / 1048576) megs_alloc,
    
26.        round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
    
27.              1048576) megs_free,
    
28.        round(sum(nvl(p.bytes_used, 0)) / 1048576) megs_used,
    
29.        round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
    
30.              sum(h.bytes_used + h.bytes_free)) * 100) Pct_Free,
    
31.        100 -
    
32.        round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
    
33.              sum(h.bytes_used + h.bytes_free)) * 100) pct_used,
    
34.        round(sum(f.maxbytes) / 1048576) max
    
35.   from sys.v_$TEMP_SPACE_HEADER h,
    
36.        sys.v_$Temp_extent_pool  p,
    
37.        dba_temp_files           f
    
38. where p.file_id(+) = h.file_id
    
39.    and p.tablespace_name(+) = h.tablespace_name
    
40.    and f.file_id = h.file_id
    
41.    and f.tablespace_name = h.tablespace_name
    
42. group by h.tablespace_name
    
43. ORDER BY 1
    
44. /
    
45. 
    
46. select sum(SPACE),TS_NAME from dba_recyclebin group by  ts_name;

复制代码

fengbao

TABLESPACE_NAME                MEGS_ALLOC  MEGS_FREE  MEGS_USED   PCT_FREE   PCT_USED        MAX
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
TBS_JTORDER_DB                     122840      82852      39988         67         33     122840
TBS_JTORDER_IDX                     40950      14744      26206         36         64      40950

SUM(SPACE) TS_NAME
---------- ------------------------------
  10601176 TBS_JTORDER_DB

Liu Maclean(刘相兵

purge recyclebin
后再次执行你的SQL;

fengbao

update语句：
update order_item t
         set status_cd        = '300000',
             finish_time      = u_task_act_date,
             last_modify_by   = u_user_id,
             last_modify_date = sysdate,
             finish_oper_date = sysdate
       where order_item_id in
             (select z_order_item_id
                from order_item_rel a
               where a.order_rel_type = '10'
                 and a_order_item_id in (u_order_item_id));

fengbao

检查表空间管理模式：
SQL> select EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces t where tablespace_name = 'TBS_JTORDER_DB';

EXTENT_MANAGEMENT ALLOCATION_TYPE SEGMENT_SPACE_MANAGEMENT
----------------- --------------- ------------------------
LOCAL             SYSTEM          AUTO

当处于SYSTEM模式下，为autoallocate，oracle会自己来决定extent的大小，一般在随着segment的增加，extent会逐渐变大；而uniform是强制规定tablespace中的extent的大小。

fengbao

查看dba_free_space情况：
SQL> select max(bytes)/1024/1024 from dba_free_space
  2         where   tablespace_name = 'TBS_JTORDER_DB';

MAX(BYTES)/1024/1024
--------------------
                  64     --当前可用的最大为64Mb

从上面的结果来看，有足够的64Mb空间可供使用，但为什么不能被分配呢？而且还有一个很奇怪的现象，一个剩余80G的表空间，最大的free_space才64Mb，可见碎片程度很高。


查看回收站：
SQL> select sum(SPACE),TS_NAME from dba_recyclebin group by  ts_name;

SUM(SPACE) TS_NAME
---------- ------------------------------
10601176   TBS_JTORDER_DB

10601176*8k=80g，可见回收站该表空间有80g，而dba_free_space剩余表空间为82g，因此可以判定64Mb的空闲空间属于回收站的空间，而当分配的时候，由于Oracle判断表空间还有足够的剩余空间（82-80=2g），因此不释放回收站的这些空间，因此应用程序在分配extent的时候报错。

--由于当时为了快速解决问题，没有统计更详细的信息，因此还没有实际的证据说明

参考：metalink：Doc ID 265253.1  对自动清理的解释（说的也不是太清楚）：
Objects in the Recycle Bin will be automatically purged by the space reclamation process if
    o A user creates a new table or adds data that causes their quota to be exceeded.
    o The tablespace needs to extend its file size to accommodate create/insert operations.

fengbao

清空回收站：
SQL>purge dba_recyclebin

SQL> select max(bytes)/1024/1024 from dba_free_space
  2         where   tablespace_name = 'TBS_JTORDER_DB';

MAX(BYTES)/1024/1024
--------------------
                  2579     --当前可用的最大为2579Mb
从上面可以看到，空间被合并了。

fengbao

总结：
    由于开启了回收站，在长期的使用过程中，不断的create/drop表，导致表空间的碎片程度很高，变成了很多小的空间，当由于DML操作需要分配额外大的extent时候，无法分配，因此报错。
    在以后的工作中需要定时对回收站进行清理。

lunar

总结的不错，O(∩_∩)O哈哈~