Recyclebin off导致ORA-1652错误

存储过程执行报错：

ORA-1652: unable to extend temp segment by 64 in tablespace                 TBS_REPORT

 

检查表空间使用率，发现表空间空闲很多啊

TABLESPACE_NAME                MEGS_TOTAL MEGS_ALLOC  MEGS_FREE  MEGS_USED   PCT_FREE   PCT_USED

------------------------------ ---------- ---------- ---------- ---------- ---------- ----------

TEMP                                    0  319450.84  315923.84       3527       98.9        1.1

TBS_STAT                           106470     106470      74532      31938         70         30

TBS_REPORT                         466838     466838      76310     390528      16.35      83.65

 

执行了几遍，存储过程还是报错，观察了一下，在第一步创建临时表的时候报错退出了，将语句拿出来单独执行，观察情况：

SQL> CREATE TABLE REPORT.REPORT_D_GROUP_DEV_TEMP1 tablespace tbs_report AS

  2      SELECT /*+PARALLEL(A,8),(B,8)*/A.*, B.CLUSTER_FLAG CALLING_TYPE

  3    FROM (SELECT DISTINCT T.USER_NO,

  4                          T.CUST_NO,

  5                          T.AREA_NO,

  6                          T.CITY_NO,

  7                          T.NET_FLAG,

  8                          T.IS_NEW_INNET,

  9                          T.IS_INNET,

 10                          T.IS_OUTNET

 11            FROM MID.MID_D_USER_FLAG T

 12           WHERE T.ACCT_DAY = '20110531'

 13             AND T.OPEN_MODE <> '1'

 14             AND T.NET_FLAG IS NOT NULL) A,

 15         (SELECT USER_NO, CALLING_TYPE, CLUSTER_FLAG

 16            FROM MID.MID_D_USER_CLUST T       

 17           WHERE T.ACCT_DAY = '20110531') B

 18   WHERE A.USER_NO = B.USER_NO(+);

          FROM MID.MID_D_USER_FLAG T

                   *

ERROR at line 11:

ORA-01652: unable to extend temp segment by 64 in tablespace TBS_REPORT

 

换一个表空间执行，表创建成功：

SQL> CREATE TABLE REPORT.REPORT_D_GROUP_DEV_TEMP1 tablespace tbs_stat AS

  2      SELECT /*+PARALLEL(A,8),(B,8)*/A.*, B.CLUSTER_FLAG CALLING_TYPE

  3    FROM (SELECT DISTINCT T.USER_NO,

  4                          T.CUST_NO,

  5                          T.AREA_NO,

  6                          T.CITY_NO,

  7                          T.NET_FLAG,

  8                          T.IS_NEW_INNET,

  9                          T.IS_INNET,

 10                          T.IS_OUTNET

 11            FROM MID.MID_D_USER_FLAG T

 12           WHERE T.ACCT_DAY = '20110531'

 13             AND T.OPEN_MODE <> '1'

 14             AND T.NET_FLAG IS NOT NULL) A,

 15         (SELECT USER_NO, CALLING_TYPE, CLUSTER_FLAG

 16            FROM MID.MID_D_USER_CLUST T       

 17           WHERE T.ACCT_DAY = '20110531') B

 18   WHERE A.USER_NO = B.USER_NO(+);

 

Table created.

 

SQL> select bytes from dba_segments where segment_name='REPORT_D_GROUP_DEV_TEMP1';

 

     BYTES

----------

1141899264

 

怀疑碎片严重，但这个表空间是uniform. size的，不存在碎片问题：

SQL> select tablespace_name,extent_management,allocation_type

     from dba_tablespaces where tablespace_name='TBS_REPORT';

 

TABLESPACE_NAME                EXTENT_MANAGEMENT ALLOCATION_TYPE

------------------------------ ----------------- ---------------

TBS_REPORT                     LOCAL             UNIFORM

 

有可能在执行的过程中，临时段产生的量过大，开了两个session观察执行的情况，但是没发现这种情况：

SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text

FROM v$session a, v$tempseg_usage b, v$sqlarea c

WHERE a.saddr = b.session_addr

AND c.address= a.sql_address

AND c.hash_value = a.sql_hash_value

ORDER BY b.tablespace, b.blocks;

在过程中blocks的数量大概只有100M不到。

 

 

检查dba_segments的占用情况，结果发现一个意外的情况：

SQL> select sum(bytes)/1024/1024/1024 from dba_data_files where tablespace_name='TBS_REPORT';

 

SUM(BYTES)/1024/1024/1024

-------------------------

            455.896484375

 

SQL> select sum(user_bytes)/1024/1024/1024 from dba_data_files where tablespace_name='TBS_REPORT';

 

SUM(USER_BYTES)/1024/1024/1024

------------------------------

                455.8408203125

 

SQL> select sum(bytes)/1024/1024/1024 from dba_segments where tablespace_name='TBS_REPORT';

 

SUM(BYTES)/1024/1024/1024

-------------------------

             455.26171875

 

SQL> select sum(bytes)/1024/1024/1024 from dba_free_space where tablespace_name='TBS_REPORT';

 

SUM(BYTES)/1024/1024/1024

-------------------------

            74.2333984375

 

 

dba_data_files中包含的总的空间小于已分配的空间（dba_segments）+空闲的空间（dba_free_space）

 

 

[ID 387562.1]

描述EXEC dbms_space_admin.tablespace_rebuild_bitmaps('TABLESPACE_NAME');可以修正。

我在测试环境测试了一下，发现还是不行：

SQL> EXEC dbms_space_admin.tablespace_rebuild_bitmaps('TBS_REPORT');

 

PL/SQL procedure successfully completed.

 

SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>

SQL> col total_bytes for 999999999999999

SQL> col used_bytes for 999999999999999

SQL> col free_bytes for 999999999999999

SQL> col diff_bytes for 999999999999999

SQL> select a.total_bytes,b.used_bytes,c.free_bytes,a.total_bytes-b.used_bytes-c.free_bytes diff_bytes

from

(select sum(user_bytes) total_bytes from dba_data_files where tablespace_name='TBS_REPORT') a,

(select sum(bytes) used_bytes from dba_segments where tablespace_name='TBS_REPORT') b,

(select sum(bytes) free_bytes from dba_free_space where tablespace_name='TBS_REPORT') c ;  4    5 

 

     TOTAL_BYTES       USED_BYTES       FREE_BYTES       DIFF_BYTES

---------------- ---------------- ---------------- ----------------

    403526647808     377166495744      30083645440      -3723493376

 

结果发现，原来是对于recyclebin的统计有问题，在dba_free_space计算了recyclebin的表的信息，在dba_segments也计算了，去掉这部分信息之后，就恢复正常了：

SQL> select a.total_bytes,b.used_bytes,c.free_bytes,a.total_bytes-b.used_bytes-c.free_bytes diff_bytes

  2  from

  3  (select sum(user_bytes) total_bytes from dba_data_files where tablespace_name='TBS_REPORT') a,

  4  (select sum(bytes) used_bytes from dba_segments where tablespace_name='TBS_REPORT' and segment_name not like 'BIN$%') b,

  5  (select sum(bytes) free_bytes from dba_free_space where tablespace_name='TBS_REPORT') c ;

 

     TOTAL_BYTES       USED_BYTES       FREE_BYTES       DIFF_BYTES

---------------- ---------------- ---------------- ----------------

    403526647808     373458731008      30067916800                0

 

 

问题回到原点了。

但是通过分析前面的dba_data_files.sum(user_bytes) , dba_free_space.sum(bytes) , dba_free_space.sum(bytes)

455.8408203125-455.26171875接近于74.2333984375

 

因为在之前被清理了，应该可以验证，在dba_free_space.sum(bytes)这个值基本等于回收站的空间：

select /*+ ordered use_nl(u) use_nl(fi) */

       sum(u.ktfbueblks*ts.blocksize)

from sys.recyclebin$ rb, sys.ts$ ts, sys.x$ktfbue u, sys.file$ fi

where ts.ts# = rb.ts#

  and rb.ts# = fi.ts#

  and u.ktfbuefno = fi.relfile#

  and u.ktfbuesegtsn = rb.ts#

  and u.ktfbuesegfno = rb.file#

  and u.ktfbuesegbno = rb.block#

  and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0 ;

也就是说dba_free_space.sum(bytes)这部分的空间实际上都是来自于recyclebin的空间

 

 

那就是意味着recyeclebin的空间不能被oracle使用，recyeclebin可能存在bug

查询metalink，果然查到了一个：

Bug 6977045 - ORA-1652 even though there is sufficient space in RECYCLE BIN [ID 6977045.8]

 

回头反思一下最近的操作，触发这个bug的原因，就是因为前一天晚上我关闭了 recyeclebin

Tue May 31 22:23:36 2011

ALTER SYSTEM SET recyclebin='OFF' SCOPE=BOTH SID='*';

这也解释了还有其他几个表空间也存在类似的问题的情况。

 

这个案例也提醒我们，在生产系统上操作还是不能太随意了。

其实我也是顺手关闭的recyclebin，因为当时想到在老库上recyclebin引发的几个问题，心血来潮就查了一下关了，关了之后也没有purge一下。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10867315/viewspace-714560/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10867315/viewspace-714560/