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
区为单位分区事务数据库空间是碎片产生的主要因素之一;