清表后 表空间缩减情况

背景：

t_t_busi_main_presend 和 t_busi_main_presend3 表，是差不多的（t_busi_main_presend3清理前有92万数据，t_busi_main_presend是80万），只是多条t_busi_main_presend3记录可能汇到一条t_busi_main_presend中。

现在先清理t_busi_main_presend3将2013-01-01之前的数据都删掉，于是：

1.建tmp表

create table t_busi_main_presend3_tmp
as
select * from t_busi_main_presend3 where cjsj>=to_date('2013-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')

2.truncate table t_busi_main_presend3

3.insert into t_busi_main_presend3
select * from t_busi_main_presend3_tmp

完成后，HWM结果对比：

SQL> select count(1) from t_busi_main_presend;

  COUNT(1)
----------
    805803

SQL> select count(1) from t_busi_main_presend3;

  COUNT(1)
----------
     37961

SQL> 
SQL> select owner,segment_name,bytes,blocks,extents from dba_segments
  2  where segment_name = 'T_BUSI_MAIN_PRESEND';

OWNER                          SEGMENT_NAME                                                                          BYTES     BLOCKS    EXTENTS
------------------------------ -------------------------------------------------------------------------------- ---------- ---------- ----------
YDSOFT_YUXINGCHINASMS          T_BUSI_MAIN_PRESEND                                                               520093696      31744        132

SQL> 
SQL> select owner,segment_name,bytes,blocks,extents from dba_segments
  2  where segment_name = 'T_BUSI_MAIN_PRESEND3';

OWNER                          SEGMENT_NAME                                                                          BYTES     BLOCKS    EXTENTS
------------------------------ -------------------------------------------------------------------------------- ---------- ---------- ----------
YDSOFT_YUXINGCHINASMS          T_BUSI_MAIN_PRESEND3                                                               39845888       2432         52

SQL> 

 

SQL> select count(1) from t_busi_main_presend;

  COUNT(1)
----------
     54629

SQL> 
SQL> select owner,segment_name,bytes,blocks,extents from dba_segments
  2  where segment_name = 'T_BUSI_MAIN_PRESEND';

OWNER                          SEGMENT_NAME                                                                          BYTES     BLOCKS    EXTENTS
------------------------------ -------------------------------------------------------------------------------- ---------- ---------- ----------
YDSOFT_YUXINGCHINASMS          T_BUSI_MAIN_PRESEND                                                                48234496       2944         60

SQL> 

                 owner,                                          segment_name,              bytes,        blocks,     extents

1 YDSOFT_YUXINGCHINASMS             T_1C_MTBUF             14680064          896           28
1 YDSOFT_YUXINGCHINASMS             T_1C_MTBUF              5505024           336           20

1 YDSOFT_YUXINGCHINASMS             T_87_MTBUF               52428800         3200        65
1 YDSOFT_YUXINGCHINASMS             T_87_MTBUF               29032448         1772        43

1 YDSOFT_YUXINGCHINASMS             T_64_MTBUF               65536                  4               1
1 YDSOFT_YUXINGCHINASMS             T_64_MTBUF               65536                  4               1

1 YDSOFT_YUXINGCHINASMS             T_MT                              947912704       57856      183
1 YDSOFT_YUXINGCHINASMS             T_MT                              360710144       22016      113

1 YDSOFT_YUXINGCHINASMS             T_24_MTBUF               13631488          832           28
1 YDSOFT_YUXINGCHINASMS             T_24_MTBUF               196608              12               3

--这种是初始的一般表
1 YDSOFT_YUXINGCHINASMS             T_0_MTBUF                  64   K              4                 1
--这种是初始的分区表
2 YDSOFT_YUXINGCHINASMS             T_NEW                           128  K             8                 1