» BUG:truncate分区后，segment大小没降低 OracleBlog.cn -天堂向左 DBA向右

BUG:truncate分区后，segment大小没降低

· 作者小荷||【转载时请务必以超链接形式标明文章原始出处和作者信息】
· 永久链接: http://www.oracleblog.cn/working-case/bug-can-not-release-segment-after-truncate-partition/

今天遇到了一个的问题，一个分区表，当我truncate完一个分区，通过查看dba_segments.bytes竟然没有空间被释放：

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for HPUX: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production

SQL> CREATE TABLE xxx
2 (aa varchar2(20),
3 bb number ) tablespace tt
4 PARTITION BY RANGE (bb)
5 (PARTITION xxx_p1
6 VALUES LESS THAN (10000),
7 PARTITION xxx_p2
8 VALUES LESS THAN (40000),
9 PARTITION xxx_p3
10 VALUES LESS THAN (80000),
11 PARTITION xxx_p4
12 VALUES LESS THAN (100000),
13 PARTITION xxx_max
14 values less than (maxvalue)
15 );

Table created.

SQL> select TABLESPACE_NAME from user_tab_partitions where table_name='XXX';

TABLESPACE_NAME
------------------------------
TT
TT
TT
TT
TT

SQL> select INITIAL_EXTENT,EXTENTS,BYTES from user_segments where segment_name='XXX';

INITIAL_EXTENT EXTENTS BYTES
-------------- ---------- ----------
   65536 1 65536
   65536 1 65536
   65536 1 65536
   65536 1 65536
   65536 1 65536

SQL> insert into xxx select 'aa',rownum from dba_objects;

97390 rows created.

SQL> insert into xxx select * from xxx;

97390 rows created.

SQL> insert into xxx select aa,rownum from xxx;

194780 rows created.

SQL> commit;

Commit complete.

SQL> select INITIAL_EXTENT,EXTENTS,BYTES from user_segments where segment_name='XXX';

INITIAL_EXTENT EXTENTS BYTES
-------------- ---------- ----------
   65536 7 458752
   65536 17 2097152
   65536 17 2097152
   65536 13 851968
   65536 17 2097152

SQL> alter table xxx truncate partition xxx_p1;

Table truncated.

SQL> select INITIAL_EXTENT,EXTENTS,BYTES from user_segments where segment_name='XXX';

INITIAL_EXTENT EXTENTS BYTES
-------------- ---------- ----------
   65536 7 458752 ######truncate该分区，空间没释放
   65536 17 2097152
   65536 17 2097152
   65536 13 851968
   65536 17 2097152

SQL> alter table xxx truncate partition xxx_p2;

Table truncated.

SQL> select INITIAL_EXTENT,EXTENTS,BYTES from user_segments where segment_name='XXX';

INITIAL_EXTENT EXTENTS BYTES
-------------- ---------- ----------
   65536 7 458752
   65536 17 2097152 ######truncate该分区，空间没释放
   65536 17 2097152
   65536 13 851968
   65536 17 2097152

但是在我机器上的数据库中进行测试，却是有正常的结果：

sys@ORALOCAL(10.1.19.14)> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

已用时间: 00: 00: 00.01
sys@ORALOCAL(10.1.19.14)>
sys@ORALOCAL(10.1.19.14)> CREATE TABLE xxx
2 (aa varchar2(20),
3 bb number )
4 PARTITION BY RANGE (bb)
5 (PARTITION xxx_p1
6 VALUES LESS THAN (10000),
7 PARTITION xxx_p2
8 VALUES LESS THAN (40000),
9 PARTITION xxx_p3
10 VALUES LESS THAN (80000),
11 PARTITION xxx_p4
12 VALUES LESS THAN (100000),
13 PARTITION xxx_max
14 values less than (maxvalue)
15 );

表已创建。

已用时间: 00: 00: 00.02
sys@ORALOCAL(10.1.19.14)> select INITIAL_EXTENT,EXTENTS,BYTES from user_segments where segment_name='XXX';

INITIAL_EXTENT EXTENTS BYTES
-------------- ---------- ----------
   65536 1 65536
   65536 1 65536
   65536 1 65536
   65536 1 65536
   65536 1 65536

已用时间: 00: 00: 00.06
sys@ORALOCAL(10.1.19.14)> insert into xxx select 'aa',rownum from dba_objects;

已创建31174行。

已用时间: 00: 00: 00.06
sys@ORALOCAL(10.1.19.14)> insert into xxx select * from xxx;

已创建31174行。

已用时间: 00: 00: 00.03

sys@ORALOCAL(10.1.19.14)> insert into xxx select aa,rownum from xxx;

已创建62348行。

已用时间: 00: 00: 00.03
sys@ORALOCAL(10.1.19.14)> /

已创建124696行。

已用时间: 00: 00: 00.08
sys@ORALOCAL(10.1.19.14)> commit;

提交完成。

已用时间: 00: 00: 00.02
sys@ORALOCAL(10.1.19.14)> select INITIAL_EXTENT,EXTENTS,BYTES from user_segments where segment_name='XXX';

INITIAL_EXTENT EXTENTS BYTES
-------------- ---------- ----------
   65536 10 655360
   65536 25 1638400
   65536 16 1048576
   65536 6 393216
   65536 7 458752

已用时间: 00: 00: 00.06

sys@ORALOCAL(10.1.19.14)> alter table xxx truncate partition xxx_p1;

表已截掉。

已用时间: 00: 00: 01.04
sys@ORALOCAL(10.1.19.14)> select INITIAL_EXTENT,EXTENTS,BYTES from user_segments where segment_name='XXX';

INITIAL_EXTENT EXTENTS BYTES
-------------- ---------- ----------
   65536 1 65536 ######truncate该分区，空间能释放
   65536 25 1638400
   65536 16 1048576
   65536 6 393216
   65536 7 458752

已用时间: 00: 00: 00.06
sys@ORALOCAL(10.1.19.14)> alter table xxx truncate partition xxx_p2;

表已截掉。

已用时间: 00: 00: 00.03
sys@ORALOCAL(10.1.19.14)> select INITIAL_EXTENT,EXTENTS,BYTES from user_segments where segment_name='XXX';

INITIAL_EXTENT EXTENTS BYTES
-------------- ---------- ----------
   65536 1 65536
   65536 1 65536 ######truncate该分区，空间能释放
   65536 16 1048576
   65536 6 393216
   65536 7 458752

查询了metalink后，发现原来这是oracle 9206的一个bug（而我自己的数据库是9201版本，故不存在这个问题）：

Bug No. 4142932
Filed 25-JAN-2005 Updated 07-MAY-2007
Product Oracle Server - Enterprise Edition Product Version 9.2.0.6
Platform HP-UX PA-RISC (64-bit) Platform Version No Data
Database Version 9.2.0.6 Affects Platforms Generic
Severity Severe Loss of Service Status Development to Q/A
Base Bug N/A Fixed in Product Version 9.2.0.6.99

Problem statement:

PSRC: DBA_SEGMENTS.EXTENTS WRONG FOR ASSM SEGMENT AFTER A “TRUNCATE”

从信息上看，应该是truncate之后数据字典的信息没有被更新，但是空间应该是被释放了的。尝试move tablespace后，发现确实该问题解决，在dba_segments.bytes中已经显示空间被释放：

SQL> select INITIAL_EXTENT,EXTENTS,BYTES from user_segments where segment_name='XXX';

INITIAL_EXTENT EXTENTS BYTES
-------------- ---------- ----------
   65536 7 458752
   65536 17 2097152
   65536 17 2097152
   65536 13 851968
   65536 17 2097152

SQL> ALTER TABLE xxx MOVE PARTITION xxx_p1 TABLESPACE tt;

Table altered.

SQL> select INITIAL_EXTENT,EXTENTS,BYTES from user_segments where segment_name='XXX';

INITIAL_EXTENT EXTENTS BYTES
-------------- ---------- ----------
   65536 1 65536 ######move tablespace后，数据显示正常
   65536 17 2097152
   65536 17 2097152
   65536 13 851968
   65536 17 2097152

SQL> ALTER TABLE xxx MOVE PARTITION xxx_p2 TABLESPACE tt;

Table altered.

SQL> select INITIAL_EXTENT,EXTENTS,BYTES from user_segments where segment_name='XXX';

INITIAL_EXTENT EXTENTS BYTES
-------------- ---------- ----------
   65536 1 65536
   65536 1 65536 ######move tablespace后，数据显示正常
   65536 17 2097152
   65536 13 851968
   65536 17 2097152

SQL>

· 【文章发布信息】发表于： 2007-09-26 @ 22:05:00 · ||分类： ..troubleshooting, Working case

OracleBlog.cn -天堂向左 DBA向右

BUG:truncate分区后，segment大小没降低

留条评论

ACOUG Member

分类

标签云

搜索

随机文章

最新回复

归档

订阅和更新

网站管理

访客位置分布

知识共享许可