验证：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

验证：oracledelete数据库空间是可以重新被使用的(一)