表碎片会造成空间不足的问题？？

今天在群里面有人提出说 ：

 你要搞情况数据是不是实际上每天在增长，是不是有碎片产生造成的空间不足，如果确实是因为数据每天在增长，那你就要做好空间规划了，看每天增量是多少，多久需要增加一个数据文件，这都可以算出来的。

@广州-lqq 程序中如果在insert中大量使用append和parallel的提示，会造成碎片 

 

你去dba_free_space试图里查小于1M的空间有多少。一般来说，表的默认扩展extent大小是1M，所以基于这个查。如果你的表有特殊设置，那就要看那个设置的扩展值来查了。

不是很清楚这个原理，要好好查查，mark一下。我认为在extent management local的情况下可以不考虑上述情况

Introduction to Locally-Managed Tablespaces (文档 ID 93771.1)

You Asked Hi Tom, Could you tell how to find tablespace fragmentation (what is the sql statement) if not use tool. Thanks. and we said... Well, it depends on how you define "fragmentation". In my opinion, in version 8.1.5 (Oracle8i and up), fragmentation is an impossible situation to be in. My defininition of fragmentation is that you have many "small" holes (regions of contigous free space) that are too small to be the NEXT extent of any object. These holes of free space resulted from dropping some objects (or truncating them) and the resulting free extents cannot be used by any other object in that tablespace. This is a direct result of using a pctincrease that is not zero and having many wierd sized extents (every extent is a unique size and shape). In Oracle8i, we would all use locally managed tablespaces. These would use either UNIFORM sizing (my favorite) or our automatic allocation scheme. In either case -- it is pretty much impossible to get into a situation where you have unusable free space. To see if you suffer from "fragmentation", you can query DBA_FREE_SPACE (best to do an alter tablespace coalesce first to ensure all contigous free regions are made into 1 big free region). DBA_FREE_SPACE will report the size of all free extents. You would look for ANY free extent that is smaller then the smallest NEXT extent size for any object in that tablespace. Below I artifically introduce this issue by using a dictionary managed tablespace and objects with pctincrease=50. I create two tables and then allocate extents to them one after the other so that they are "interleaved". Then I drop one of the tables and find all of the free extents that are too small to hold the next extent for the smallest next extent in that tablespace. tkyte@TKYTE816> drop tablespace t including contents; Tablespace dropped. tkyte@TKYTE816> create tablespace t 2 datafile 'c:\temp\t.dbf' size 10m 3 reuse 4 / Tablespace created. tkyte@TKYTE816> create table t_t1 ( x int ) 2 storage ( initial 1k next 1k pctincrease 50 ) 3 tablespace t 4 / Table created. tkyte@TKYTE816> create table t_t2 ( x int ) 2 storage ( initial 1k next 1k pctincrease 50 ) 3 tablespace t 4 / Table created. tkyte@TKYTE816> alter table t_t1 allocate extent; Table altered. tkyte@TKYTE816> alter table t_t2 allocate extent; Table altered. ... (above 2 commands executed in order over and over) .... tkyte@TKYTE816> drop table t_t1; Table dropped. tkyte@TKYTE816> select * 2 from dba_free_space 3 where tablespace_name = 'T' 4 and bytes <= ( select min(next_extent) 5 from dba_segments 6 where tablespace_name = 'T') 7 order by block_id 8 / TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO ------------------------------ ---------- ---------- ---------- ---------- ------------ T 9 2 16384 2 9 T 9 6 8192 1 9 T 9 8 16384 2 9 T 9 12 24576 3 9 T 9 18 40960 5 9 T 9 28 81920 10 9 T 9 48 122880 15 9 T 9 78 163840 20 9 T 9 118 245760 30 9 T 9 178 368640 45 9 10 rows selected. tkyte@TKYTE816> spool off