验证:oracle delete 数据库空间是可以重新被使用的

SQL> create tablespace itdba_1 datafile '/u01/ora11g/itdba/oradata/d11g/itdba1.dbf' size 200M autoextend off;
Tablespace created.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/ora11g/itdba/oradata/d11g/system01.dbf
/u01/ora11g/itdba/oradata/d11g/sysaux01.dbf
/u01/ora11g/itdba/oradata/d11g/undotbs01.dbf
/u01/ora11g/itdba/oradata/d11g/users01.dbf
/u01/ora11g/itdba/oradata/d11g/example01.dbf
/u01/ora11g/itdba/oradata/d11g/itdba1.dbf
6 rows selected.
SQL> create table t_itdba_1 tablespace itdba_1 as select * from dba_extents;
Table created.

创建表空间,创建表。

SQL> insert into t_itdba_1 select /* +append */ * from t_itdba_1 nologging;
9883 rows created.
SQL> /
19766 rows created.
SQL> /
39532 rows created.
初始化数据
SQL>  select bytes/1024/1024 ,segment_name from dba_segments where segment_name='T_ITDBA_1';
BYTES/1024/1024 SEGMENT_NAME
--------------- ------------------------------
            104 T_ITDBA_1
Elapsed: 00:00:00.01
SQL> delete from T_ITDBA_1;
1265024 rows deleted.
Elapsed: 00:00:35.68
SQL> commit;
数据统计:

SQL>  select bytes/1024/1024 ,segment_name from dba_segments where segment_name='T_ITDBA_1';
BYTES/1024/1024 SEGMENT_NAME
--------------- ------------------------------
            104 T_ITDBA_1
数据存储空间没有变化:
SQL>  insert into t_itdba_1 select * from dba_extents nologging;
10087 rows created.
Elapsed: 00:00:12.84
SQL>  insert into t_itdba_1 select /* +append */ * from t_itdba_1 nologging;
10087 rows created.
Elapsed: 00:00:00.13
SQL> /
20174 rows created.
Elapsed: 00:00:00.18
SQL> /
SQL> select bytes/1024/1024 ,segment_name from dba_segments where segment_name='T_ITDBA_1';
BYTES/1024/1024 SEGMENT_NAME
--------------- ------------------------------
            104 T_ITDBA_1
数据的占用的空间的的确确没有增加,如果,但是为什么有的表却不断的在浪费空间,形成碎片呢?

SQL> update T_ITDBA_1 set OWNER='aaaaaaaaaaaaaaaaaaaaaaaaaaa';
1291136 rows updated.
Elapsed: 00:01:27.36
SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
SQL> select bytes/1024/1024 ,segment_name from dba_segments where segment_name='T_ITDBA_1';
BYTES/1024/1024 SEGMENT_NAME
--------------- ------------------------------
            136 T_ITDBA_1
SQL>  update T_ITDBA_1 set OWNER='aaaaaaaaaaaaaaaaaaaaaaaaab';
1291136 rows updated.
Elapsed: 00:01:14.79
SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
SQL> select bytes/1024/1024 ,segment_name from dba_segments where segment_name='T_ITDBA_1';

BYTES/1024/1024 SEGMENT_NAME
--------------- ------------------------------
            136 T_ITDBA_1
Elapsed: 00:00:00.06
SQL> update T_ITDBA_1 set SEGMENT_NAME='abcde000000000000000000000';
1291136 rows updated.
Elapsed: 00:01:32.36
SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
SQL> select bytes/1024/1024 ,segment_name from dba_segments where segment_name='T_ITDBA_1';
BYTES/1024/1024 SEGMENT_NAME
--------------- ------------------------------
            144 T_ITDBA_1
Elapsed: 00:00:00.06
SQL> 
更新会使用到更多的数据,那么也就会占用更多的空间。那么这个时候是否有有表链 这种情况呢?

SQL>  exec dbms_stats.gather_table_stats(user,'T_ITDBA_1');
PL/SQL procedure successfully completed.
Elapsed: 00:00:04.99
SQL> select table_name,
  2         num_rows,
  3         CHAIN_CNT,
  4         ROUND((CHAIN_CNT / num_rows) * 100, 2) as "RT%"
  5    from dba_tables
  6   where table_name = 'T_ITDBA_1';

TABLE_NAME                       NUM_ROWS  CHAIN_CNT        RT%
------------------------------ ---------- ---------- ----------
T_ITDBA_1                         1291136          0          0

Elapsed: 00:00:00.06
经过查询,没有产生链表!那么这种更新,很有可能数据直接转移到新的块上还是?这种现象到底什么原因呢?对于这个问题,我重新实验

SQL> drop table T_ITDBA_1;
Table dropped.
Elapsed: 00:00:02.24
SQL> create table t_itdba_1 tablespace itdba_1 as select * from dba_extents;
Table created.
Elapsed: 00:00:16.58
SQL> exec dbms_stats.gather_table_stats(user,'T_ITDBA_1');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.23
SQL> select bytes/1024 ,segment_name from dba_segments where segment_name='T_ITDBA_1';
BYTES/1024 SEGMENT_NAME
---------- ------------------------------
       896 T_ITDBA_1
Elapsed: 00:00:00.25
SQL> 
查询该表数据行于数据块之间的对应关系


     75159          6      18589              87
     75159          6      18620             110
     75159          6      18626             108
     75159          6      18635              76
     75159          6      18657              97
     75159          6      18574             129
     75159          6      18577             132
     75159          6      18582             120

 OBJECT_ID    FILE_ID   BLOCK_ID COUNT(BLOCK_ID)
---------- ---------- ---------- ---------------
     75159          6      18588             127
     75159          6      18598              90
     75159          6      18604              78
     75159          6      18616             104
     75159          6      18617             107
     75159          6      18627             105
数据很多,我把它存放到一张表里
SQL> create table t_itdba_01 as 
select object_id, file_id, block_id, count(block_id) countt
  2    3    from (select rowid,
               dbms_rowid.rowid_object(rowid) object_id,
               dbms_rowid.rowid_relative_fno(rowid) file_id,
               dbms_rowid.rowid_block_number(rowid) block_id
  4    5    6    7            FROM T_ITDBA_1)
  8   group by object_id, file_id, block_id;
Table created.
Elapsed: 00:00:00.55

SQL> update T_ITDBA_1 set SEGMENT_NAME='abcde000000000000000000000';
10294 rows updated.
Elapsed: 00:00:00.26
SQL> commit;
Commit complete.
Elapsed: 00:00:00.05
SQL>  exec dbms_stats.gather_table_stats(user,'T_ITDBA_1');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.21
SQL> select table_name,
  2         num_rows,
  3         CHAIN_CNT,
  4         ROUND((CHAIN_CNT / num_rows) * 100, 2) as "RT%"
  5    from dba_tables
  6   where table_name = 'T_ITDBA_1';

TABLE_NAME                       NUM_ROWS  CHAIN_CNT        RT%
------------------------------ ---------- ---------- ----------
T_ITDBA_1                           10294          0          0

Elapsed: 00:00:00.06
SQL> select bytes/1024 ,segment_name from dba_segments where segment_name='T_ITDBA_1';

BYTES/1024 SEGMENT_NAME
---------- ------------------------------
       960 T_ITDBA_1

Elapsed: 00:00:00.01
SQL> 
占用空间增加,创建映射表
SQL> create table t_itdba_02 as 
  2  select object_id, file_id, block_id, count(block_id) countt
  3    from (select rowid,
  4                 dbms_rowid.rowid_object(rowid) object_id,
  5                 dbms_rowid.rowid_relative_fno(rowid) file_id,
  6                 dbms_rowid.rowid_block_number(rowid) block_id
  7            FROM T_ITDBA_1)
  8   group by object_id, file_id, block_id;
Table created.
Elapsed: 00:00:00.51
SQL> 
比对数据

SQL> select a.block_id, b.countt - a.countt
  2    from t_itdba_01 a, t_itdba_02 b
  3   where a.block_id = b.block_id
  4     and (b.countt - a.countt) <> 0;

no rows selected

Elapsed: 00:00:00.00
SQL> 
占用的空间增加,使用的块数没增加,难道仅仅使用块的free部分,碎片到底怎么产生的,还是我测试力度不够! 也就是说,数据库什么时候会用到delete 部分的数据空间??
1 exten 分配产生,2 行连接,3 行迁移  

在分配上:做个实验

update T_ITDBA_1 set owner='2dfadafsfsafdsafdsf';

SQL> select segment_name,extent_id,block_id,bytes from dba_extents   where segment_name= 'T_ITDBA_1';
SEGMENT_NAME                    EXTENT_ID   BLOCK_ID      BYTES
------------------------------ ---------- ---------- ----------
T_ITDBA_1                               0      18560      65536
T_ITDBA_1                               1      18568      65536
T_ITDBA_1                               2      18576      65536
T_ITDBA_1                               3      18584      65536
T_ITDBA_1                               4      18592      65536
T_ITDBA_1                               5      18600      65536
T_ITDBA_1                               6      18608      65536
T_ITDBA_1                               7      18616      65536
T_ITDBA_1                               8      18624      65536
T_ITDBA_1                               9      18632      65536
T_ITDBA_1                              10      18640      65536

SEGMENT_NAME                    EXTENT_ID   BLOCK_ID      BYTES
------------------------------ ---------- ---------- ----------
T_ITDBA_1                              11      18648      65536
T_ITDBA_1                              12      18656      65536
T_ITDBA_1                              13      18664      65536
T_ITDBA_1                              14      18672      65536
SQL> select segment_name,extent_id,block_id,bytes,blocks from dba_extents   where segment_name= 'T_ITDBA_1'
  2  ;


SEGMENT_NAME                    EXTENT_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
T_ITDBA_1                               0      18560      65536          8
T_ITDBA_1                               1      18568      65536          8
T_ITDBA_1                               2      18576      65536          8
T_ITDBA_1                               3      18584      65536          8
T_ITDBA_1                               4      18592      65536          8
T_ITDBA_1                               5      18600      65536          8
T_ITDBA_1                               6      18608      65536          8
T_ITDBA_1                               7      18616      65536          8
T_ITDBA_1                               8      18624      65536          8
T_ITDBA_1                               9      18632      65536          8
T_ITDBA_1                              10      18640      65536          8


SEGMENT_NAME                    EXTENT_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
T_ITDBA_1                              11      18648      65536          8
T_ITDBA_1                              12      18656      65536          8
T_ITDBA_1                              13      18664      65536          8
T_ITDBA_1                              14      18672      65536          8
T_ITDBA_1                              15      18680      65536          8
T_ITDBA_1                              16      18688    1048576        128
rowid 没有变化,但是占用的空间却变大了
SQL> select object_id, file_id, block_id, count(block_id)
  2    from (select rowid,
  3                 dbms_rowid.rowid_object(rowid) object_id,
               dbms_rowid.rowid_relative_fno(rowid) file_id,
               dbms_rowid.rowid_block_number(rowid) block_id
          FROM T_ITDBA_1)
 group by object_id, file_id, block_id
  4    5    6    7    8  ;

 OBJECT_ID    FILE_ID   BLOCK_ID COUNT(BLOCK_ID)
---------- ---------- ---------- ---------------
     75159          6      18570             119
     75159          6      18581             122
     75159          6      18584             127<pre name="code" class="html">SQL> truncate table T_ITDBA_1;

Table truncated.

Elapsed: 00:00:00.53
SQL> select segment_name,extent_id,block_id,bytes,blocks from dba_extents   where segment_name= 'T_ITDBA_1';

SEGMENT_NAME                    EXTENT_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
T_ITDBA_1                               0      18560      65536          8

Elapsed: 00:00:00.02
SQL> 
SQL> 
SQL> insert into T_ITDBA_1 select * from dba_extents;

10066 rows created.

Elapsed: 00:00:02.47
SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
SQL> insert into T_ITDBA_1 select * from  T_ITDBA_1;

10066 rows created.

Elapsed: 00:00:00.04
SQL> /    

20132 rows created.

Elapsed: 00:00:00.05
SQL> /

40264 rows created.

Elapsed: 00:00:00.09
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL> select segment_name,extent_id,block_id,bytes,blocks from dba_extents   where segment_name= 'T_ITDBA_1';

SEGMENT_NAME                    EXTENT_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
T_ITDBA_1                               0      18560      65536          8
T_ITDBA_1                               1      18568      65536          8
T_ITDBA_1                               2      18576      65536          8
T_ITDBA_1                               3      18584      65536          8
T_ITDBA_1                               4      18592      65536          8
T_ITDBA_1                               5      18600      65536          8
T_ITDBA_1                               6      18608      65536          8
T_ITDBA_1                               7      18616      65536          8
T_ITDBA_1                               8      18624      65536          8
T_ITDBA_1                               9      18632      65536          8
T_ITDBA_1                              10      18640      65536          8

SEGMENT_NAME                    EXTENT_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
T_ITDBA_1                              11      18648      65536          8
T_ITDBA_1                              12      18656      65536          8
T_ITDBA_1                              13      18664      65536          8
T_ITDBA_1                              14      18672      65536          8
T_ITDBA_1                              15      18680      65536          8
T_ITDBA_1                              16      18688    1048576        128
T_ITDBA_1                              17      18816    1048576        128
T_ITDBA_1                              18      18944    1048576        128
T_ITDBA_1                              19      19072    1048576        128
T_ITDBA_1                              20      19200    1048576        128
T_ITDBA_1                              21      19328    1048576        128

22 rows selected.

Elapsed: 00:00:00.01
SQL> update T_ITDBA_1 set owner='0';

80528 rows updated.

Elapsed: 00:00:03.28
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL> select segment_name,extent_id,block_id,bytes,blocks from dba_extents   where segment_name= 'T_ITDBA_1';

SEGMENT_NAME                    EXTENT_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
T_ITDBA_1                               0      18560      65536          8
T_ITDBA_1                               1      18568      65536          8
T_ITDBA_1                               2      18576      65536          8
T_ITDBA_1                               3      18584      65536          8
T_ITDBA_1                               4      18592      65536          8
T_ITDBA_1                               5      18600      65536          8
T_ITDBA_1                               6      18608      65536          8
T_ITDBA_1                               7      18616      65536          8
T_ITDBA_1                               8      18624      65536          8
T_ITDBA_1                               9      18632      65536          8
T_ITDBA_1                              10      18640      65536          8

SEGMENT_NAME                    EXTENT_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
T_ITDBA_1                              11      18648      65536          8
T_ITDBA_1                              12      18656      65536          8
T_ITDBA_1                              13      18664      65536          8
T_ITDBA_1                              14      18672      65536          8
T_ITDBA_1                              15      18680      65536          8
T_ITDBA_1                              16      18688    1048576        128
T_ITDBA_1                              17      18816    1048576        128
T_ITDBA_1                              18      18944    1048576        128
T_ITDBA_1                              19      19072    1048576        128
T_ITDBA_1                              20      19200    1048576        128
T_ITDBA_1                              21      19328    1048576        128

22 rows selected.

Elapsed: 00:00:00.01
SQL> insert into T_ITDBA_1 select * from dba_extents;

10087 rows created.

Elapsed: 00:00:01.95
SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
SQL> select segment_name,extent_id,block_id,bytes,blocks from dba_extents   where segment_name= 'T_ITDBA_1';

SEGMENT_NAME                    EXTENT_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
T_ITDBA_1                               0      18560      65536          8
T_ITDBA_1                               1      18568      65536          8
T_ITDBA_1                               2      18576      65536          8
T_ITDBA_1                               3      18584      65536          8
T_ITDBA_1                               4      18592      65536          8
T_ITDBA_1                               5      18600      65536          8
T_ITDBA_1                               6      18608      65536          8
T_ITDBA_1                               7      18616      65536          8
T_ITDBA_1                               8      18624      65536          8
T_ITDBA_1                               9      18632      65536          8
T_ITDBA_1                              10      18640      65536          8

SEGMENT_NAME                    EXTENT_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
T_ITDBA_1                              11      18648      65536          8
T_ITDBA_1                              12      18656      65536          8
T_ITDBA_1                              13      18664      65536          8
T_ITDBA_1                              14      18672      65536          8
T_ITDBA_1                              15      18680      65536          8
T_ITDBA_1                              16      18688    1048576        128
T_ITDBA_1                              17      18816    1048576        128
T_ITDBA_1                              18      18944    1048576        128
T_ITDBA_1                              19      19072    1048576        128
T_ITDBA_1                              20      19200    1048576        128
T_ITDBA_1                              21      19328    1048576        128

22 rows selected.

Elapsed: 00:00:00.02
SQL>  insert into T_ITDBA_1 select * from  T_ITDBA_1 where owner<>'0';

10087 rows created.

Elapsed: 00:00:00.03
SQL> /

20174 rows created.

Elapsed: 00:00:00.06
SQL> /

40348 rows created.

Elapsed: 00:00:00.10
SQL> /

80696 rows created.

Elapsed: 00:00:00.21
SQL> /
/

161392 rows created.

Elapsed: 00:00:00.65
SQL> /
322784 rows created.

Elapsed: 00:00:02.09
SQL> commit; 
SP2-0042: unknown command "/commit" - rest of line ignored.
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL> select segment_name,extent_id,block_id,bytes,blocks from dba_extents   where segment_name= 'T_ITDBA_1';

SEGMENT_NAME                    EXTENT_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
T_ITDBA_1                               0      18560      65536          8
T_ITDBA_1                               1      18568      65536          8
T_ITDBA_1                               2      18576      65536          8
T_ITDBA_1                               3      18584      65536          8
T_ITDBA_1                               4      18592      65536          8
T_ITDBA_1                               5      18600      65536          8
T_ITDBA_1                               6      18608      65536          8
T_ITDBA_1                               7      18616      65536          8
T_ITDBA_1                               8      18624      65536          8
T_ITDBA_1                               9      18632      65536          8
T_ITDBA_1                              10      18640      65536          8

SEGMENT_NAME                    EXTENT_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
T_ITDBA_1                              11      18648      65536          8
T_ITDBA_1                              12      18656      65536          8
T_ITDBA_1                              13      18664      65536          8
T_ITDBA_1                              14      18672      65536          8
T_ITDBA_1                              15      18680      65536          8
T_ITDBA_1                              16      18688    1048576        128
T_ITDBA_1                              17      18816    1048576        128
T_ITDBA_1                              18      18944    1048576        128
T_ITDBA_1                              19      19072    1048576        128
T_ITDBA_1                              20      19200    1048576        128
T_ITDBA_1                              21      19328    1048576        128

SEGMENT_NAME                    EXTENT_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
T_ITDBA_1                              22      19456    1048576        128
T_ITDBA_1                              23      19584    1048576        128
T_ITDBA_1                              24      19712    1048576        128
T_ITDBA_1                              25      19840    1048576        128
T_ITDBA_1                              26      19968    1048576        128
T_ITDBA_1                              27      20096    1048576        128
T_ITDBA_1                              28      20224    1048576        128
T_ITDBA_1                              29      20352    1048576        128
T_ITDBA_1                              30      20480    1048576        128
T_ITDBA_1                              31      20608    1048576        128
T_ITDBA_1                              32      20736    1048576        128

SEGMENT_NAME                    EXTENT_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
T_ITDBA_1                              33      20864    1048576        128
T_ITDBA_1                              34      20992    1048576        128
T_ITDBA_1                              35      21120    1048576        128
T_ITDBA_1                              36      21248    1048576        128
T_ITDBA_1                              37      21376    1048576        128
T_ITDBA_1                              38      21504    1048576        128
T_ITDBA_1                              39      21632    1048576        128
T_ITDBA_1                              40      21760    1048576        128
T_ITDBA_1                              41      21888    1048576        128
T_ITDBA_1                              42      22016    1048576        128
T_ITDBA_1                              43      22144    1048576        128

SEGMENT_NAME                    EXTENT_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
T_ITDBA_1                              44      22272    1048576        128
T_ITDBA_1                              45      22400    1048576        128
T_ITDBA_1                              46      22528    1048576        128
T_ITDBA_1                              47      22656    1048576        128
T_ITDBA_1                              48      22784    1048576        128
T_ITDBA_1                              49      22912    1048576        128
T_ITDBA_1                              50      23040    1048576        128
T_ITDBA_1                              51      23168    1048576        128
T_ITDBA_1                              52      23296    1048576        128
T_ITDBA_1                              53      23424    1048576        128
T_ITDBA_1                              54      23552    1048576        128

SEGMENT_NAME                    EXTENT_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
T_ITDBA_1                              55      23680    1048576        128
T_ITDBA_1                              56      23808    1048576        128
T_ITDBA_1                              57      23936    1048576        128
T_ITDBA_1                              58      24064    1048576        128
T_ITDBA_1                              59      24192    1048576        128
T_ITDBA_1                              60      24320    1048576        128
T_ITDBA_1                              61      24448    1048576        128
T_ITDBA_1                              62      24576    1048576        128
T_ITDBA_1                              63      24704    1048576        128
T_ITDBA_1                              64      24832    1048576        128
T_ITDBA_1                              65      24960    1048576        128

SEGMENT_NAME                    EXTENT_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
T_ITDBA_1                              66      25088    1048576        128
T_ITDBA_1                              67      25216    1048576        128
T_ITDBA_1                              68      25344    1048576        128
T_ITDBA_1                              69      25472    1048576        128
T_ITDBA_1                              70        128    1048576        128

71 rows selected.

Elapsed: 00:00:00.01
SQL>  select bytes/1024 ,segment_name from dba_segments where segment_name='T_ITDBA_1';

BYTES/1024 SEGMENT_NAME
---------- ------------------------------
     57344 T_ITDBA_1

Elapsed: 00:00:00.01
SQL> delete from T_ITDBA_1 where owner='0';

80528 rows deleted.

Elapsed: 00:00:02.26
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL> select segment_name,extent_id,block_id,bytes,blocks from dba_extents   where segment_name= 'T_ITDBA_1';

SEGMENT_NAME                    EXTENT_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
T_ITDBA_1                               0      18560      65536          8
T_ITDBA_1                               1      18568      65536          8
T_ITDBA_1                               2      18576      65536          8
T_ITDBA_1                               3      18584      65536          8
T_ITDBA_1                               4      18592      65536          8
T_ITDBA_1                               5      18600      65536          8
T_ITDBA_1                               6      18608      65536          8
T_ITDBA_1                               7      18616      65536          8
T_ITDBA_1                               8      18624      65536          8
T_ITDBA_1                               9      18632      65536          8
T_ITDBA_1                              10      18640      65536          8

SEGMENT_NAME                    EXTENT_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
T_ITDBA_1                              11      18648      65536          8
T_ITDBA_1                              12      18656      65536          8
T_ITDBA_1                              13      18664      65536          8
T_ITDBA_1                              14      18672      65536          8
T_ITDBA_1                              15      18680      65536          8
T_ITDBA_1                              16      18688    1048576        128
T_ITDBA_1                              17      18816    1048576        128
T_ITDBA_1                              18      18944    1048576        128
T_ITDBA_1                              19      19072    1048576        128
T_ITDBA_1                              20      19200    1048576        128
T_ITDBA_1                              21      19328    1048576        128

SEGMENT_NAME                    EXTENT_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
T_ITDBA_1                              22      19456    1048576        128
T_ITDBA_1                              23      19584    1048576        128
T_ITDBA_1                              24      19712    1048576        128
T_ITDBA_1                              25      19840    1048576        128
T_ITDBA_1                              26      19968    1048576        128
T_ITDBA_1                              27      20096    1048576        128
T_ITDBA_1                              28      20224    1048576        128
T_ITDBA_1                              29      20352    1048576        128
T_ITDBA_1                              30      20480    1048576        128
T_ITDBA_1                              31      20608    1048576        128
T_ITDBA_1                              32      20736    1048576        128

SEGMENT_NAME                    EXTENT_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
T_ITDBA_1                              33      20864    1048576        128
T_ITDBA_1                              34      20992    1048576        128
T_ITDBA_1                              35      21120    1048576        128
T_ITDBA_1                              36      21248    1048576        128
T_ITDBA_1                              37      21376    1048576        128
T_ITDBA_1                              38      21504    1048576        128
T_ITDBA_1                              39      21632    1048576        128
T_ITDBA_1                              40      21760    1048576        128
T_ITDBA_1                              41      21888    1048576        128
T_ITDBA_1                              42      22016    1048576        128
T_ITDBA_1                              43      22144    1048576        128

SEGMENT_NAME                    EXTENT_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
T_ITDBA_1                              44      22272    1048576        128
T_ITDBA_1                              45      22400    1048576        128
T_ITDBA_1                              46      22528    1048576        128
T_ITDBA_1                              47      22656    1048576        128
T_ITDBA_1                              48      22784    1048576        128
T_ITDBA_1                              49      22912    1048576        128
T_ITDBA_1                              50      23040    1048576        128
T_ITDBA_1                              51      23168    1048576        128
T_ITDBA_1                              52      23296    1048576        128
T_ITDBA_1                              53      23424    1048576        128
T_ITDBA_1                              54      23552    1048576        128

SEGMENT_NAME                    EXTENT_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
T_ITDBA_1                              55      23680    1048576        128
T_ITDBA_1                              56      23808    1048576        128
T_ITDBA_1                              57      23936    1048576        128
T_ITDBA_1                              58      24064    1048576        128
T_ITDBA_1                              59      24192    1048576        128
T_ITDBA_1                              60      24320    1048576        128
T_ITDBA_1                              61      24448    1048576        128
T_ITDBA_1                              62      24576    1048576        128
T_ITDBA_1                              63      24704    1048576        128
T_ITDBA_1                              64      24832    1048576        128
T_ITDBA_1                              65      24960    1048576        128

SEGMENT_NAME                    EXTENT_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
T_ITDBA_1                              66      25088    1048576        128
T_ITDBA_1                              67      25216    1048576        128
T_ITDBA_1                              68      25344    1048576        128
T_ITDBA_1                              69      25472    1048576        128
T_ITDBA_1                              70        128    1048576        128

71 rows selected.

Elapsed: 00:00:00.02
SQL> insert into t_itdba_1 select * from t_itdba_1;                                                                     

645568 rows created.

Elapsed: 00:00:02.75
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL> select segment_name,extent_id,block_id,bytes,blocks from dba_extents   where segment_name= 'T_ITDBA_1';

SEGMENT_NAME                    EXTENT_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
T_ITDBA_1                               0      18560      65536          8
T_ITDBA_1                               1      18568      65536          8
T_ITDBA_1                               2      18576      65536          8
T_ITDBA_1                               3      18584      65536          8
T_ITDBA_1                               4      18592      65536          8
T_ITDBA_1                               5      18600      65536          8
T_ITDBA_1                               6      18608      65536          8
T_ITDBA_1                               7      18616      65536          8
T_ITDBA_1                               8      18624      65536          8
T_ITDBA_1                               9      18632      65536          8
T_ITDBA_1                              10      18640      65536          8

SEGMENT_NAME                    EXTENT_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
T_ITDBA_1                              11      18648      65536          8
T_ITDBA_1                              12      18656      65536          8
T_ITDBA_1                              13      18664      65536          8
T_ITDBA_1                              14      18672      65536          8
T_ITDBA_1                              15      18680      65536          8
T_ITDBA_1                              16      18688    1048576        128
T_ITDBA_1                              17      18816    1048576        128
T_ITDBA_1                              18      18944    1048576        128
T_ITDBA_1                              19      19072    1048576        128
T_ITDBA_1                              20      19200    1048576        128
T_ITDBA_1                              21      19328    1048576        128

SEGMENT_NAME                    EXTENT_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
T_ITDBA_1                              22      19456    1048576        128
T_ITDBA_1                              23      19584    1048576        128
T_ITDBA_1                              24      19712    1048576        128
T_ITDBA_1                              25      19840    1048576        128
T_ITDBA_1                              26      19968    1048576        128
T_ITDBA_1                              27      20096    1048576        128
T_ITDBA_1                              28      20224    1048576        128
T_ITDBA_1                              29      20352    1048576        128
T_ITDBA_1                              30      20480    1048576        128
T_ITDBA_1                              31      20608    1048576        128
T_ITDBA_1                              32      20736    1048576        128

SEGMENT_NAME                    EXTENT_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
T_ITDBA_1                              33      20864    1048576        128
T_ITDBA_1                              34      20992    1048576        128
T_ITDBA_1                              35      21120    1048576        128
T_ITDBA_1                              36      21248    1048576        128
T_ITDBA_1                              37      21376    1048576        128
T_ITDBA_1                              38      21504    1048576        128
T_ITDBA_1                              39      21632    1048576        128
T_ITDBA_1                              40      21760    1048576        128
T_ITDBA_1                              41      21888    1048576        128
T_ITDBA_1                              42      22016    1048576        128
T_ITDBA_1                              43      22144    1048576        128

SEGMENT_NAME                    EXTENT_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
T_ITDBA_1                              44      22272    1048576        128
T_ITDBA_1                              45      22400    1048576        128
T_ITDBA_1                              46      22528    1048576        128
T_ITDBA_1                              47      22656    1048576        128
T_ITDBA_1                              48      22784    1048576        128
T_ITDBA_1                              49      22912    1048576        128
T_ITDBA_1                              50      23040    1048576        128
T_ITDBA_1                              51      23168    1048576        128
T_ITDBA_1                              52      23296    1048576        128
T_ITDBA_1                              53      23424    1048576        128
T_ITDBA_1                              54      23552    1048576        128

SEGMENT_NAME                    EXTENT_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
T_ITDBA_1                              55      23680    1048576        128
T_ITDBA_1                              56      23808    1048576        128
T_ITDBA_1                              57      23936    1048576        128
T_ITDBA_1                              58      24064    1048576        128
T_ITDBA_1                              59      24192    1048576        128
T_ITDBA_1                              60      24320    1048576        128
T_ITDBA_1                              61      24448    1048576        128
T_ITDBA_1                              62      24576    1048576        128
T_ITDBA_1                              63      24704    1048576        128
T_ITDBA_1                              64      24832    1048576        128
T_ITDBA_1                              65      24960    1048576        128

SEGMENT_NAME                    EXTENT_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
T_ITDBA_1                              66      25088    1048576        128
T_ITDBA_1                              67      25216    1048576        128
T_ITDBA_1                              68      25344    1048576        128
T_ITDBA_1                              69      25472    1048576        128
T_ITDBA_1                              70        128    1048576        128
T_ITDBA_1                              71        256    1048576        128
T_ITDBA_1                              72        384    1048576        128
T_ITDBA_1                              73        512    1048576        128
T_ITDBA_1                              74        640    1048576        128
T_ITDBA_1                              75        768    1048576        128
T_ITDBA_1                              76        896    1048576        128

SEGMENT_NAME                    EXTENT_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
T_ITDBA_1                              77       1024    1048576        128
T_ITDBA_1                              78       1152    1048576        128
T_ITDBA_1                              79       1280    8388608       1024
T_ITDBA_1                              80       2304    8388608       1024
T_ITDBA_1                              81       3328    8388608       1024
T_ITDBA_1                              82       4352    8388608       1024
T_ITDBA_1                              83       5376    8388608       1024

84 rows selected.

Elapsed: 00:00:00.02
SQL> 


这个时候发现会有很多空块

SQL> select segment_name, extent_id, block_id, bytes, blocks
  2    from dba_extents
  3   where segment_name = 'T_ITDBA_1'
  4     and block_id not in (select dbms_rowid.rowid_block_number(rowid) block_id
  5                            FROM T_ITDBA_1);


SEGMENT_NAME                    EXTENT_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
T_ITDBA_1                               0      18560      65536          8
T_ITDBA_1                               2      18576      65536          8
T_ITDBA_1                               4      18592      65536          8
T_ITDBA_1                               6      18608      65536          8
T_ITDBA_1                               8      18624      65536          8
T_ITDBA_1                              10      18640      65536          8
T_ITDBA_1                              12      18656      65536          8
T_ITDBA_1                              14      18672      65536          8
T_ITDBA_1                              16      18688    1048576        128
T_ITDBA_1                              17      18816    1048576        128
T_ITDBA_1                              18      18944    1048576        128

SEGMENT_NAME                    EXTENT_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
T_ITDBA_1                              19      19072    1048576        128
T_ITDBA_1                              20      19200    1048576        128
T_ITDBA_1                              21      19328    1048576        128
T_ITDBA_1                              22      19456    1048576        128

区为单位分区事务数据库空间是碎片产生的主要因素之一;



郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。