关闭回收站后遭遇ORA-01652

原创黄宸宁 2014-02-23

622

周五听起熊哥说起一个案例，一个客户的表空间查看dba_free_space有剩余空间，但是在创建表的时候，始终报ORA-01652错误，第一反应是跟回收站有关，记得过去看过从10g开始dba_free_space包含了回收站这部分内容，所以觉得这个问题应该跟回收站有一定关系。今天在自己的环境重现了下这个问题，并分析了相关的原因。
1.实验准备，创建一个小的表空间，存放一张几乎占满整个表空间的表

11:19:19 HCN@HCN>select segment_name,bytes/1024/1024 from user_segments where segment_type='TABLE';
SEGMENT_NAME                             BYTES/1024/1024
---------------------------------------- ---------------
SALES                                              .0625
T_MONITOR                                          .6875
T_MONITOR1                                             2
CONTROLSNAP                                            6
CONTROLSNAP1                                           6
ENQ_TEST                                           .0625
TEST1                                                  6
TEST                                                   9
TEST_LONG                                              3
TEST_LOB                                               7
ROWIDOLD                                               2
ROWIDNEW                                               2
EXPDPFULL                                              6
13 rows selected.
11:19:22 HCN@HCN>
11:25:08 SYS@HCN>
11:25:08 SYS@HCN>create tablespace TESTTBS datafile '/oradata/HCN/testtbs.01' size 10m;     ---创建测试表空间，大小10M
Tablespace created.
11:26:02 SYS@HCN>
11:30:05 HCN@HCN>
11:26:10 HCN@HCN>
11:26:10 HCN@HCN>create table recyctest tablespace testtbs as select * from test;
Table created.
11:27:29 HCN@HCN>select segment_name,bytes/1024/1024,tablespace_name from user_segments where segment_name=upper('recyctest');   ---创建测试表，大小9M
SEGMENT_NAME                             BYTES/1024/1024 TABLESPACE_NAME
---------------------------------------- --------------- ------------------------------
RECYCTEST                                              9 TESTTBS
11:28:03 HCN@HCN>
11:30:05 HCN@HCN>
11:30:05 HCN@HCN>select tablespace_name, sum(bytes)/1024/1024
11:30:06   2    from dba_free_space
11:30:06   3   where tablespace_name = upper('testtbs')
11:30:06   4   group by tablespace_name;
TABLESPACE_NAME                SUM(BYTES)/1024/1024
------------------------------ --------------------
TESTTBS                                       .9375
11:30:08 HCN@HCN>
-----TESTTBS剩余空间为0.9M
11:33:59 SYS@HCN>
11:33:59 SYS@HCN>show parameter recyclebin
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      on
11:34:16 SYS@HCN>
11:34:17 SYS@HCN>复制

2.删除表

11:30:08 HCN@HCN>
11:30:09 HCN@HCN>SELECT * FROM USER_RECYCLEBIN;      
no rows selected
11:36:05 HCN@HCN>
11:36:05 HCN@HCN>drop table recyctest;
Table dropped.
11:36:15 HCN@HCN>
11:36:16 HCN@HCN>SELECT * FROM USER_RECYCLEBIN;      ----删除后放入回收站
OBJECT_NAME                    ORIGINAL_NAME                    OPERATION TYPE                      TS_NAME                        CREATETIME          DROPTIME               DROPSCN
------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ----------
PARTITION_NAME                   CAN CAN    RELATED BASE_OBJECT PURGE_OBJECT      SPACE
-------------------------------- --- --- ---------- ----------- ------------ ----------
BIN$8wuOp7on553gQKjAFVoVRA==$0 RECYCTEST                        DROP      TABLE                     TESTTBS                        2014-02-23:11:27:29 2014-02-23:11:36:15    1667471
                                 YES YES      53728       53728        53728       1152
11:36:20 HCN@HCN>
11:36:20 HCN@HCN>
11:36:34 HCN@HCN>
11:36:34 HCN@HCN>
11:36:34 HCN@HCN>select tablespace_name, sum(bytes)/1024/1024
11:36:35   2    from dba_free_space
11:36:35   3   where tablespace_name = upper('testtbs')
11:36:35   4   group by tablespace_name;
TABLESPACE_NAME                SUM(BYTES)/1024/1024
------------------------------ --------------------
TESTTBS                                      9.9375              -----空闲空间
11:36:37 HCN@HCN>
11:36:37 HCN@HCN>复制

3.关闭回收站功能

11:37:29 SYS@HCN>
11:37:29 SYS@HCN>ALTER SYSTEM SET recyclebin = OFF; 
System altered.
11:37:30 SYS@HCN>
11:37:31 SYS@HCN>
11:37:31 SYS@HCN>
11:37:04 HCN@HCN>
11:37:05 HCN@HCN>show parameter recyclebin
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      OFF
11:37:45 HCN@HCN>复制

4.新建表，使用之前创建的小表空间，用10046事件跟踪该过程

11:38:20 HCN@HCN>
11:38:21 HCN@HCN>ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
Session altered.
11:39:44 HCN@HCN>
创建表
11:39:44 HCN@HCN>
11:39:45 HCN@HCN>create table recyctest1 tablespace testtbs as select * from test;
create table recyctest1 tablespace testtbs as select * from test
                                                            *
ERROR at line 1:
ORA-01652: unable to extend temp segment by 8 in tablespace TESTTBS
------报错
11:40:28 HCN@HCN>
11:40:29 HCN@HCN>ALTER SESSION SET EVENTS '10046 trace name context off';
Session altered.
11:44:45 HCN@HCN>
11:45:12 HCN@HCN>SELECT d.VALUE || '/' || LOWER(RTRIM(i.INSTANCE, CHR(0))) || '_ora_' ||
11:45:12   2         p.spid || '.trc' AS "trace_file_name"
11:45:12   3    FROM (SELECT p.spid
11:45:12   4            FROM v$mystat m, v$session s, v$process p
11:45:12   5           WHERE m.statistic# = 1
11:45:12   6             AND s.SID = m.SID
11:45:12   7             AND p.addr = s.paddr) p,
11:45:12   8         (SELECT t.INSTANCE
11:45:12   9            FROM v$thread t, v$parameter v
11:45:12  10           WHERE v.NAME = 'thread'
11:45:12  11             AND (v.VALUE = 0 OR t.thread# = TO_NUMBER(v.VALUE))) i,
11:45:12  12         (SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') d;
trace_file_name
--------------------------------------------------------------------------------
/u01/app/admin/HCN/udump/hcn_ora_7278.trc
11:45:13 HCN@HCN>
11:45:14 HCN@HCN>复制

5.打开回收站功能，并重新开一个会话创建表，用10046事件跟踪该过程

11:48:29 SYS@HCN>
11:48:29 SYS@HCN>ALTER SYSTEM SET recyclebin = on;
System altered.
11:49:04 SYS@HCN>show parameter recyclebin
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      ON
11:53:11 SYS@HCN>
11:53:12 SYS@HCN>
11:53:22 HCN@HCN>
11:53:22 HCN@HCN>ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
Session altered.
11:53:35 HCN@HCN>
11:53:36 HCN@HCN>create table recyctest1 tablespace testtbs as select * from test;
Table created.              -----建表成功
11:53:45 HCN@HCN>
11:53:46 HCN@HCN>ALTER SESSION SET EVENTS '10046 trace name context off';
Session altered.
11:53:56 HCN@HCN>
11:53:57 HCN@HCN>SELECT d.VALUE || '/' || LOWER(RTRIM(i.INSTANCE, CHR(0))) || '_ora_' ||
11:54:02   2         p.spid || '.trc' AS "trace_file_name"
11:54:02   3    FROM (SELECT p.spid
11:54:02   4            FROM v$mystat m, v$session s, v$process p
11:54:02   5           WHERE m.statistic# = 1
11:54:02   6             AND s.SID = m.SID
11:54:03   7             AND p.addr = s.paddr) p,
11:54:03   8         (SELECT t.INSTANCE
11:54:03   9            FROM v$thread t, v$parameter v
11:54:03  10           WHERE v.NAME = 'thread'
11:54:03  11             AND (v.VALUE = 0 OR t.thread# = TO_NUMBER(v.VALUE))) i,
11:54:03  12         (SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') d;
trace_file_name
--------------------------------------------------------------------------------
/u01/app/admin/HCN/udump/hcn_ora_8419.trc
11:54:05 HCN@HCN>
11:54:05 HCN@HCN>
11:54:05 HCN@HCN>
11:55:39 HCN@HCN>
11:55:39 HCN@HCN>
11:55:39 HCN@HCN>SELECT * FROM USER_RECYCLEBIN;    ---回收站内的对象已经被清除
no rows selected
11:55:41 HCN@HCN>复制

从上面的实验结果可以看到：
1.当回收站功能关闭以后，就算回收站内有对象，在新建表的时候，不会重用这部分空间
2.当回收站功能开启以后，在新建表的时候如果表空间空间不足，会去重用回收站内对象的空间，会从下面的10046中发现这部分操作
下面是对以上两个阶段的10046报告进行分析时，发现在回收站功能开启的情况下，当表空间的实际使用空间不足时，会对回收站中的对象进行的操作：

********************************************************************************
select obj#, type#, flags, related, bo, purgeobj, con#    
from
 RecycleBin$    where ts#=:1 and to_number(bitand(flags, 16)) = 16    order 
  by dropscn
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          2          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          2          0           1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT ORDER BY (cr=2 pr=0 pw=0 time=98 us)
      1   TABLE ACCESS BY INDEX ROWID RECYCLEBIN$ (cr=2 pr=0 pw=0 time=54 us)
      1    INDEX RANGE SCAN RECYCLEBIN$_TS (cr=1 pr=0 pw=0 time=31 us)(object id 705)
********************************************************************************
drop table "HCN"."BIN$8wuOp7on553gQKjAFVoVRA==$0" purge ---删除回收站中的对象
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.00          1          1          1           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.01       0.00          1          1          1           0
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                         1        0.00          0.00
********************************************************************************复制

顺带附上dba_free_space视图的定义，就会明白为什么在该视图显示有剩余空间的时候，在创建新表时会报错

create or replace view dba_free_space
(tablespace_name, file_id, block_id, bytes, blocks, relative_fno)
as
select ts.name, fi.file#, f.block#,
       f.length * ts.blocksize, f.length, f.file#
from sys.ts$ ts, sys.fet$ f, sys.file$ fi
where ts.ts# = f.ts#
  and f.ts# = fi.ts#
  and f.file# = fi.relfile#
  and ts.bitmapped = 0
union all
select /*+ ordered use_nl(f) use_nl(fi) */
       ts.name, fi.file#, f.ktfbfebno,
       f.ktfbfeblks * ts.blocksize, f.ktfbfeblks, f.ktfbfefno
from sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fi
where ts.ts# = f.ktfbfetsn
  and f.ktfbfetsn = fi.ts#
  and f.ktfbfefno = fi.relfile#
  and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
union all
select /*+ ordered use_nl(u) use_nl(fi) */
       ts.name, fi.file#, u.ktfbuebno,
       u.ktfbueblks * ts.blocksize, u.ktfbueblks, u.ktfbuefno
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
union all
select ts.name, fi.file#, u.block#,
       u.length * ts.blocksize, u.length, u.file#
from sys.ts$ ts, sys.uet$ u, sys.file$ fi, sys.recyclebin$ rb    -----在计算空闲空间的时候，实际上是把回收站中的空间算成了空余空间
where ts.ts# = u.ts#
  and u.ts# = fi.ts#
  and u.segfile# = fi.relfile#
  and u.ts# = rb.ts#
  and u.segfile# = rb.file#
  and u.segblock# = rb.block#
  and ts.bitmapped = 0;复制

oracle

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

关闭回收站后遭遇ORA-01652

评论

相关阅读