今天遇到了一个的问题，一个分区表，当我truncate完一个分区，通过查看dba_segments.bytes竟然没有空间被释放：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 |
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 |
但是在我机器上的数据库中进行测试，却是有正常的结果：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 |
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中已经显示空间被释放：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
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> |