一、先来看看目前的空间状况，其中TDWCMSDB 的FSFI只有13，其中好几个表的extent数量都在200以上，例子是177个


SQL> select tablespace_name,sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)))) FSFI
     FROM dba_free_space
     group by tablespace_name order by 1;
 
  TABLESPACE_NAME                      FSFI
------------------------------ ----------
SYSAUX                         29.2920889
SYSTEM                         84.0498358
TDCMS_DATA                            100
TDMC_DATA                             100
TDMDOLOG_DATA                  29.2747955
TDSP_DATA                             100
TDTM_DATA                             100
TDWCMSDB                       13.3218369
TDWLDB_DATA                    50.3387017
TDWLDB_IDX                            100
TDWL_DATA                             100

TABLESPACE_NAME                      FSFI
------------------------------ ----------
TDWNET_DATA                           100
TDYWDSDB                       84.0512657
UNDOTBS1                       19.8632222
USERS                          56.1681666


SQL>  SELECT segment_name,
  2               SUM(bytes)/1024/1024 "SizeM",
  3              SUM(blocks)  blocks,
  4               COUNT(1) extent_count,
  5         MIN(bytes)/1024/1024 "inital_Extent(M)",
  6              MAX(bytes)/1024/1024 "max_Extent(M)"
  7         FROM dba_extents
  8              WHERE segment_name = 'TDWCMS_CHANNELITEM_EXCEPTION'
  9              AND owner = 'TDWCMSDB'
 10        group by segment_name  
 11  /

SEGMENT_NAME                   SizeM     BLOCKS EXTENT_COUNT inital_Extent(M) max_Extent(M)
------------------------- ---------- ---------- ------------ ---------------- -------------
TDWCMS_CHANNELITEM_EXCEPT        812     103936          177            .0625             8
ION


SQL> exec show_space('TDWCMS_CHANNELITEM_EXCEPTION','TDWCMSDB')
Total Blocks  ..........................103936
Total Bytes   ..........................851443712
Total MBytes  ..........................812
Unused Blocks ..........................0
Unused Bytes  ..........................0
Unused KBytes ..........................0
Used Blocks   ..........................103936
Used Bytes    ..........................851443712
Used KBytes   ..........................831488
Last Used Ext FileId....................7
Last Used Ext BlockId...................1850889
Last Used Block.........................1024
The segment is analyzed below
FS1 Blocks (0-25)   ....................3
FS2 Blocks (25-50)  ....................1
FS3 Blocks (50-75)  ....................1
FS4 Blocks (75-100) ....................571
Unformatted Blocks  ....................0
Full Blocks         ....................102851

PL/SQL procedure successfully completed.


二、执行move table 操作
满的数据块 站总块数比例103936/102851=99% , 这样move后，变化应该不大。因为extent比较小，可以exp compress 处理

SQL> alter table tdwcmsdb.TDWCMS_CHANNELITEM_EXCEPTION move;

Table altered.

SQL> exec show_space('TDWCMS_CHANNELITEM_EXCEPTION','TDWCMSDB')
Total Blocks  ..........................104704
Total Bytes   ..........................857735168
Total MBytes  ..........................818
Unused Blocks ..........................899
Unused Bytes  ..........................7364608
Unused KBytes ..........................7192
Used Blocks   ..........................103805
Used Bytes    ..........................850370560
Used KBytes   ..........................830440
Last Used Ext FileId....................18
Last Used Ext BlockId...................172544
Last Used Block.........................125
The segment is analyzed below
FS1 Blocks (0-25)   ....................0
FS2 Blocks (25-50)  ....................0
FS3 Blocks (50-75)  ....................0
FS4 Blocks (75-100) ....................0
Unformatted Blocks  ....................0
Full Blocks         ....................103292

PL/SQL procedure successfully completed.



SQL>  SELECT segment_name,
                 SUM(bytes)/1024/1024 "SizeM",
                SUM(blocks)  blocks,
                 COUNT(1) extent_count,
           MIN(bytes)/1024/1024 "inital_Extent(M)",
                MAX(bytes)/1024/1024 "max_Extent(M)"
           FROM dba_extents
                WHERE segment_name = 'TDWCMS_CHANNELITEM_EXCEPTION'
                AND owner = 'TDWCMSDB'
         group by segment_name  
   /

SEGMENT_NAME                   SizeM     BLOCKS EXTENT_COUNT inital_Extent(M) max_Extent(M)
------------------------- ---------- ---------- ------------ ---------------- -------------
TDWCMS_CHANNELITEM_EXCEPT        818     104704          174            .0625             8
ION

先试试收缩表段，效果是释放了Unused Blocks，降低了段高水位， extent的数量只减少了3个
SQL>       alter table tdwcmsdb.TDWCMS_CHANNELITEM_EXCEPTION enable row movement;
alter table tdwcmsdb.TDWCMS_CHANNELITEM_EXCEPTION shrink space cascade;
alter table tdwcmsdb.TDWCMS_CHANNELITEM_EXCEPTION disable row movement;
Table altered.

SQL>
Table altered.

SQL>

Table altered.

SQL>
SQL>
SQL>
SQL>  SELECT segment_name,
  2               SUM(bytes)/1024/1024 "SizeM",
  3              SUM(blocks)  blocks,
  4               COUNT(1) extent_count,
  5         MIN(bytes)/1024/1024 "inital_Extent(M)",
  6              MAX(bytes)/1024/1024 "max_Extent(M)"
  7         FROM dba_extents
  8              WHERE segment_name = 'TDWCMS_CHANNELITEM_EXCEPTION'
  9              AND owner = 'TDWCMSDB'
 10        group by segment_name  
 11        
SQL> /

SEGMENT_NAME                   SizeM     BLOCKS EXTENT_COUNT inital_Extent(M) max_Extent(M)
------------------------- ---------- ---------- ------------ ---------------- -------------
TDWCMS_CHANNELITEM_EXCEPT        811     103808          174            .0625             8
ION


SQL> exec show_space('TDWCMS_CHANNELITEM_EXCEPTION','TDWCMSDB')
Total Blocks  ..........................103808
Total Bytes   ..........................850395136
Total MBytes  ..........................811
Unused Blocks ..........................3
Unused Bytes  ..........................24576
Unused KBytes ..........................24
Used Blocks   ..........................103805
Used Bytes    ..........................850370560
Used KBytes   ..........................830440
Last Used Ext FileId....................18
Last Used Ext BlockId...................172544
Last Used Block.........................125
The segment is analyzed below
FS1 Blocks (0-25)   ....................0
FS2 Blocks (25-50)  ....................0
FS3 Blocks (50-75)  ....................0
FS4 Blocks (75-100) ....................0
Unformatted Blocks  ....................0
Full Blocks         ....................103292

PL/SQL procedure successfully completed.




三、执行exp compress 处理，合并extent
exp \'/ as sysdba\' file=tdwcmsdb.dmp compress=Y grants=Y indexes=Y owner=tdwcmsdb;
imp \'/ as sysdba \' file=tdwcmsdb.dmp commit=Y buffer=64000 full=Y IGNORE=Y

exp/imp之后会合并extent，情况如下：
tdwcmdb表空间的fsfi值变到33了， extent数据量明显减少到18个

SQL> @/home/oracle/maintain/sql/fsfi.sql
Connected.
SYSAUX                         27.4839711
SYSTEM                         84.0697033
TDCMS_DATA                            100
TDMC_DATA                             100
TDMDOLOG_DATA                  33.8016191
TDSP_DATA                             100
TDTM_DATA                             100
TDWCMSDB                       33.2900668
TDWLDB_DATA                    50.3387017
TDWLDB_IDX                            100
TDWL_DATA                             100
TDWNET_DATA                           100
TDYWDSDB                       84.0512657
UNDOTBS1                       16.5380801
USERS                          66.8513744

SQL>  exec show_space('TDWCMS_CHANNELITEM_EXCEPTION','TDWCMSDB')
Total Blocks  ..........................104448
Total Bytes   ..........................855638016
Total MBytes  ..........................816
Unused Blocks ..........................1024
Unused Bytes  ..........................8388608
Unused KBytes ..........................8192
Used Blocks   ..........................103424
Used Bytes    ..........................847249408
Used KBytes   ..........................827392
Last Used Ext FileId....................7
Last Used Ext BlockId...................1297289
Last Used Block.........................1024
The segment is analyzed below
FS1 Blocks (0-25)   ....................0
FS2 Blocks (25-50)  ....................0
FS3 Blocks (50-75)  ....................0
FS4 Blocks (75-100) ....................163
Unformatted Blocks  ....................0
Full Blocks         ....................102855

SQL>  SELECT segment_name,
                 SUM(bytes)/1024/1024 "SizeM",
                SUM(blocks)  blocks,
                 COUNT(1) extent_count,
           MIN(bytes)/1024/1024 "inital_Extent(M)",
                MAX(bytes)/1024/1024 "max_Extent(M)"
           FROM dba_extents
                WHERE segment_name = 'TDWCMS_CHANNELITEM_EXCEPTION'
                AND owner = 'TDWCMSDB'
         group by segment_name  
   /

SEGMENT_NAME                   SizeM     BLOCKS EXTENT_COUNT inital_Extent(M) max_Extent(M)
------------------------- ---------- ---------- ------------ ---------------- -------------
TDWCMS_CHANNELITEM_EXCEPT        816     104448          18                 8            64
ION




改造后的脚本：
show_space.sql
 
SQL>  exec show_space('TDWCMS_PRODUCT_DAY','TDWCMSDB')
Total Blocks  ..........................885888
Total Bytes   ..........................7257194496
Total MBytes  ..........................6921
Unused Blocks ..........................0
Unused Bytes  ..........................0
Unused KBytes ..........................0
Used Blocks   ..........................885888
Used Bytes    ..........................7257194496
Used KBytes   ..........................7087104
Last Used Ext FileId....................7
Last Used Ext BlockId...................1654793
Last Used Block.........................8192
The segment is analyzed below
FS1 Blocks (0-25)   ....................1
FS2 Blocks (25-50)  ....................0
FS3 Blocks (50-75)  ....................0
FS4 Blocks (75-100) ....................240
Unformatted Blocks  ....................3832
Full Blocks         ....................880585
The extent is analyzed below
Extent Count   .........................110
Inital Extent MBytes ...................18
Max Extent MBytes ......................64
Average Extent MBytes ..................62.92

PL/SQL procedure successfully completed.