Oracle查看块是否被重用,Oracle 块重用

SQL> create table t2 as select * from dba_objects;

Table created.

SQL>  select table_name,num_rows,blocks,num_rows/blocks from dba_tables where table_name='T2' and owner='SCOTT';

TABLE_NAME                       NUM_ROWS     BLOCKS NUM_ROWS/BLOCKS

------------------------------ ---------- ---------- ---------------

T2

SQL> exec dbms_stats.gather_table_stats('SCOTT','T2');

PL/SQL procedure successfully completed.

SQL> select table_name,num_rows,blocks,num_rows/blocks from dba_tables where table_name='T2' and owner='SCOTT';

TABLE_NAME                       NUM_ROWS     BLOCKS NUM_ROWS/BLOCKS

------------------------------ ---------- ---------- ---------------

T2                                  72881       1065      68.4328638

SQL>  delete from t2 where rownum <=10000;

10000 rows deleted.

SQL> commit;

Commit complete.

SQL> select table_name,num_rows,blocks,num_rows/blocks from dba_tables where table_name='T2' and owner='SCOTT';

TABLE_NAME                       NUM_ROWS     BLOCKS NUM_ROWS/BLOCKS

------------------------------ ---------- ---------- ---------------

T2                                 72881       1065      68.4328638

SQL> exec dbms_stats.gather_table_stats('SCOTT','T2');

PL/SQL procedure successfully completed.

SQL>  select table_name,num_rows,blocks,num_rows/blocks from dba_tables where table_name='T2' and owner='SCOTT';

TABLE_NAME                       NUM_ROWS     BLOCKS NUM_ROWS/BLOCKS

------------------------------ ---------- ---------- ---------------

T2                                 62881       1065      59.0431925

SQL>

SQL> declare

i number;

begin

for i in 1..10000 loop

insert into t2(OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME)

values ('SCOTT', 'I_COBJ#', null, 30, 30, 'INDEX', to_date('07-07-2014 05:39:01', 'dd-mm-yyyy hh24:mi:ss'), to_date('07-07-2014 05:39:01', 'dd-mm-yyyy hh24:mi:ss'), '2014-07-07:05:39:01', 'VALID', 'N', 'N', 'N', 4, null);

end loop;

commit;

end;

/

PL/SQL procedure successfully completed.

SQL> select table_name,num_rows,blocks,num_rows/blocks from dba_tables where table_name='T2' and owner='SCOTT';

TABLE_NAME                       NUM_ROWS     BLOCKS NUM_ROWS/BLOCKS

------------------------------ ---------- ---------- ---------------

T2                                  62881       1065      59.0431925

SQL>  exec dbms_stats.gather_table_stats('SCOTT','T2');

PL/SQL procedure successfully completed.

SQL> select table_name,num_rows,blocks,num_rows/blocks from dba_tables where table_name='T2' and owner='SCOTT';

TABLE_NAME                       NUM_ROWS     BLOCKS NUM_ROWS/BLOCKS

------------------------------ ---------- ---------- ---------------

T2                                 72881       1065      68.4328638

这里可以看到数据库没有为新插入的数据分配新块，而是重用之前的数据块。这样节省了数据库的存储空间。这样也有一个问题，如果有大量的数据删除，没有新的数据插入，导致了大量的数据块空闲，如果是全表扫描的话，高水位下的许多块是无效的，做很多无用功。如果这种现象比较突出的话，则可以在业务空闲时段进行空间shrink或者表重建。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29289867/viewspace-2119071/，如需转载，请注明出处，否则将追究法律责任。