Using Oracle Blocks Efficiently

1.查找表空间不足10%的表

select owner,table_name,blocks,empty_blocks from dba_tables

where empty_blocks/(blocks+empty_blocks) < 0.1;

 

2.扩充extent

alter table hr.employee allocate extent;

alter table hr.employee allocate extent (size 10M);

 

3.收集blocks信息

 exec dbms_stats.gather_table_stats('用户名','表名');

 

4. 收集empty_blocks信息

analyze table 表名 compute statistics;

 

5.相关联的数据字典

 dba_tables

 

 select bytes,blocks,extents from dba_segments where owner='MARK'

and segment_name = 'T';

 

select  extent_id,block_id,bytes from dba_extents where owner='MARK'

and segment_name = 'T';

 

6.dbms_space包可计算segments的空余blocks

 

7.删除segments中空余blocks

 删除High-Level之上的空blocks

 alter table t deallocate unused;

删除High-Level之下的空blocks

 第一种方式imp,exp

 第二种方式 重建新表，删除老表,改新表名为老表名

 

8.收集索引信息

 execute dbms_stats.gather_index_stats('用户名','索引名');

 

9.收集后信息放入index_stats中

select name,(DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100 AS wastage from index_stats

 

当wastage 大于20%时，

执行index rebuild 或 index coalesce

 alter index oe.customer_pk rebuild

或

 alter index oe.customer_pk coalesce

 

10.监控索引

alter index hr.emp_name_ix monitoring uage;

 

select  index_name,used from v$object_usage

 

alter index hr.emp_name_ix nomonitoring usage;