Oracle 个人技巧 -- Oracle HWM

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;