Oracle 个人技巧 -- Oracle HWM

字数 3833阅读 985评论 0赞 5

REM This is an example SQL*Plus Script to find tables fragmentated below high water mark  
  
set heading off verify off echo off   
Spool fragment.txt  
  
REM The below queries gives information about the size of the table with respect to the High water Mark   
REM note that BLOCKS*8192 is BLOCKS times the block size: 8192. Substitue your DB blocksize.  
REM SELECT BLOCKS*8192/1024/1024 FROM DBA_TABLES WHERE TABLE_NAME='' and owner='' ;   
REM The below queries gives the actual size in MB used by the table in terms of data .   
REM SELECT NUM_ROWS*AVG_ROW_LEN/1024/1024 FROM DBA_TABLES WHERE TABLE_NAME='' and owner=' enable row movement;  
alter table  shrink space cascade;  
alter table  disable row movement;  
  
exec dbms_stats.gather_table_stats(‘BAAL’,'T’,cascade=>true);  
  
Exec dbms_stats.gather_schema_stats( ownname => 'WILLSON', options => 'GATHER AUTO', estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for all indexed columns ', degree => 6 )  
  
查看表用了多个block  
SQL> select sum(blocks) from dba_extents where  
segment_name='AA';  
  
SUM(BLOCKS)  
  
查看AA 高水位线  
  
SQL> select BLOCKS from dba_tables where table_name='AA';  
  
BLOCKS  
----------  
6312  
  
alter table aa allocate extent(instance 10);  
  
SQL> analyze table aa COMPUTE STATISTICS;  
  
  
查看高水位线   
  
set serveroutput on  
declare   
TOTAL_BLOCKS number;   
TOTAL_BYTES number;   
UNUSED_BLOCKS number;   
UNUSED_BYTES number;   
LAST_USED_EXTENT_FILE_ID number;   
LAST_USED_EXTENT_BLOCK_ID number;   
LAST_USED_BLOCK number;   
begin   
dbms_space.unused_space(   
'WILLSON',   
'AA',   
'TABLE',   
TOTAL_BLOCKS,   
TOTAL_BYTES,   
UNUSED_BLOCKS,   
UNUSED_BYTES,   
LAST_USED_EXTENT_FILE_ID,   
LAST_USED_EXTENT_BLOCK_ID,   
LAST_USED_BLOCK);   
dbms_output.put_line('OBJECT_NAME = FREELIST_T');   
dbms_output.put_line('-----------------------------------');   
dbms_output.put_line('TOTAL_BLOCKS = '||TOTAL_BLOCKS);   
dbms_output.put_line('UNUSED_BLOCKS = '||UNUSED_BLOCKS);   
dbms_output.put_line('LAST_USED_EXTENT_BLOCK_ID = '||LAST_USED_EXTENT_BLOCK_ID);   
dbms_output.put_line('LAST_USED_BLOCK = '||LAST_USED_BLOCK);   
end;   
/   
  
exec DBMS_STATS.GATHER_TABLE_STATS (ownname=>'willson',tabname=>'AA',estimate_percent=>100);   
  
select num_rows,blocks,empty_blocks from user_tables where table_name='AA';  
BLOCKS 列代表该表中曾经使用过得数据库块的数目，即水线。EMPTY_BLOCKS 代表分配给该表，但是在水线以上的数据库块，即从来没有使用的数据块.  
  
  
set serveroutput on  
declare   
v_unformatted_blocks number;   
v_unformatted_bytes number;   
v_fs1_blocks number;   
v_fs1_bytes number;   
v_fs2_blocks number;   
v_fs2_bytes number;   
v_fs3_blocks number;   
v_fs3_bytes number;   
v_fs4_blocks number;   
v_fs4_bytes number;   
v_full_blocks number;   
v_full_bytes number;   
  
begin   
dbms_space.space_usage (   
'WILLSON',   
'AA',   
'TABLE',   
v_unformatted_blocks,   
v_unformatted_bytes,   
v_fs1_blocks,   
v_fs1_bytes,   
v_fs2_blocks,   
v_fs2_bytes,   
v_fs3_blocks,   
v_fs3_bytes,   
v_fs4_blocks,   
v_fs4_bytes,   
v_full_blocks,   
v_full_bytes);   
  
dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);   
dbms_output.put_line('Blocks with 00-25% free space = '||v_fs1_blocks);   
dbms_output.put_line('Blocks with 26-50% free space = '||v_fs2_blocks);   
dbms_output.put_line('Blocks with 51-75% free space = '||v_fs3_blocks);   
dbms_output.put_line('Blocks with 76-100% free space = '||v_fs4_blocks);   
dbms_output.put_line('Full Blocks = '||v_full_blocks);   
  
end;

Oracle

著作权归作者所有

如果觉得我的文章对您有用，请点赞。您的支持将鼓励我继续创作！

添加新评论0 条评论

Ctrl+Enter 发表

匿名评论

Oracle 个人技巧 -- Oracle HWM

添加新评论0 条评论

作者其他文章

相关文章

相关问题

相关资料