程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> 驗證：oracledelete數據庫空間是可以重新被使用的

驗證：oracledelete數據庫空間是可以重新被使用的

編輯：Oracle教程

驗證：oracledelete數據庫空間是可以重新被使用的

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

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